Set a control value On Opening the Form

[Claudette Hennessy]

I have a continuous form based on an outer join. The pertinent fields in
the query are

SELECT tblDealer.ShopID, tblDealer.ShopName,
IIf(IsNull(tblContract2008Temp.ShopName),tblDealer.ShopName,tblContract2008Temp.ShopName)
AS FindShopName, FROM tblDealer LEFT JOIN tblContract2008Temp ON
tblDealer.ShopID = tblContract2008Temp.ShopID
ORDER BY tblDealer.ShopName;

I have a hidden control set to the FindShopName value.
When the form opens, if any txtShopName value is null, I want it set to the
FindShopName value. What happens is the form opens with the control still
blank, but it fills in if I click the control or the record selector.

What am I missing. I also tried the on Current, and on load events.

Private Sub Form_Open(Cancel As Integer)
With Me
If IsNull(.[txtShopName]) Then
..[txtShopName] = .[txttblDealerShopName]
End If
End With
End Sub

Thank you,
Claudette

 

[Dennis]

Try the On Current event.

 

[Claudette Hennessy]

Hi Dennis, I did try the On Current event, as well as On Load and On Open.
When the form opens, the control is blank where the value is null; If I
click the record locator in the form, the control fills in the value; I
thought that when a form opened, it calculated fields for all records in the
dynaset. Do I need to loop the recordset to fill in the null values?
Claudette

Try the On Current event.

I have a continuous form based on an outer join. The pertinent fields
in
the query are

SELECT tblDealer.ShopID, tblDealer.ShopName,
IIf(IsNull(tblContract2008Temp.ShopName),tblDealer.ShopName,tblContract2008Temp.ShopName)
AS FindShopName, FROM tblDealer LEFT JOIN tblContract2008Temp ON
tblDealer.ShopID = tblContract2008Temp.ShopID
ORDER BY tblDealer.ShopName;

I have a hidden control set to the FindShopName value.
When the form opens, if any txtShopName value is null, I want it set to
the
FindShopName value. What happens is the form opens with the control
still
blank, but it fills in if I click the control or the record selector.

What am I missing. I also tried the on Current, and on load events.

Private Sub Form_Open(Cancel As Integer)
With Me
If IsNull(.[txtShopName]) Then
..[txtShopName] = .[txttblDealerShopName]
End If
End With
End Sub

Thank you,
Claudette

 

[Dennis]

Well, I'm stumped. I've not had a subform act like that. Your code should IMO
take care of that. Personally, I'd use a simpler syntax. In the On Current
event, I'd do:

If isnull(me.myField) then me.myfield = someValue

Single line of code in the On Current event for the subform.

Dennis

Hi Dennis, I did try the On Current event, as well as On Load and On Open.
When the form opens, the control is blank where the value is null; If I
click the record locator in the form, the control fills in the value; I
thought that when a form opened, it calculated fields for all records in the
dynaset. Do I need to loop the recordset to fill in the null values?
Claudette

Try the On Current event.

I have a continuous form based on an outer join. The pertinent fields
in
the query are

SELECT tblDealer.ShopID, tblDealer.ShopName,
IIf(IsNull(tblContract2008Temp.ShopName),tblDealer.ShopName,tblContract2008Temp.ShopName)
AS FindShopName, FROM tblDealer LEFT JOIN tblContract2008Temp ON
tblDealer.ShopID = tblContract2008Temp.ShopID
ORDER BY tblDealer.ShopName;

I have a hidden control set to the FindShopName value.
When the form opens, if any txtShopName value is null, I want it set to
the
FindShopName value. What happens is the form opens with the control
still
blank, but it fills in if I click the control or the record selector.

What am I missing. I also tried the on Current, and on load events.

Private Sub Form_Open(Cancel As Integer)
With Me
If IsNull(.[txtShopName]) Then
..[txtShopName] = .[txttblDealerShopName]
End If
End With
End Sub

Thank you,
Claudette

 

[J_Goddard via AccessMonster.com]

Hi -

Even though you see several records on the screen with a continuous form, the
On Current event will not fire until the cursor actually enters that record,
which happens when you click the field or record selector.

Did you try using a saved query which will do the iif calculations , and then
basing the form on the saved query?

John

Well, I'm stumped. I've not had a subform act like that. Your code should IMO
take care of that. Personally, I'd use a simpler syntax. In the On Current
event, I'd do:

If isnull(me.myField) then me.myfield = someValue

Single line of code in the On Current event for the subform.

Dennis

Hi Dennis, I did try the On Current event, as well as On Load and On Open.
When the form opens, the control is blank where the value is null; If I

[quoted text clipped - 33 lines]

 

[Claudette Hennessy]

Hello John, you gave me the clue I needed. The form is based on a left
join query, which leaves some fields null. After inputting other info, the
records are saved to a table; however I didn't want the user to see blank
fields when the form is opened. The following procedure fixes this:


Private Sub Form_Load()
Dim db As Database
Dim rec As Recordset
Set db = CurrentDb()
Set rec = db.OpenRecordset("qryContract2008Temp")
'rec(1) is ID, rec(2) is source name, rec(3) is if target name is blank, use
source name,rec (4) is target name
Do While Not rec.EOF
'Locate blank fields and edit them
If IsNull(rec(4)) Then
rec.Edit
rec(4) = rec(2)
rec.Update
End If
rec.MoveNext
Loop
rec.Close
'After updating the query, requery
Me.Requery
End Sub

No doubt there is a more elegant way to do this...

Thank everybody who responded, this newsgroup is a godsend.
Claudette

Hi -

Even though you see several records on the screen with a continuous form,
the
On Current event will not fire until the cursor actually enters that
record,
which happens when you click the field or record selector.

Did you try using a saved query which will do the iif calculations , and
then
basing the form on the saved query?

John

Well, I'm stumped. I've not had a subform act like that. Your code should
IMO
take care of that. Personally, I'd use a simpler syntax. In the On Current
event, I'd do:

If isnull(me.myField) then me.myfield = someValue

Single line of code in the On Current event for the subform.

Dennis

Hi Dennis, I did try the On Current event, as well as On Load and On
Open.
When the form opens, the control is blank where the value is null; If I

[quoted text clipped - 33 lines]

Thank you,
Claudette

 

[Claudette Hennessy]

Hi John, that is the clue I needed. The following code does what I want.

Private Sub Form_Load()

Dim db As Database
Dim rec As Recordset
Set db = CurrentDb()
Set rec = db.OpenRecordset("qryContract2008Temp")
Do While Not rec.EOF
If IsNull(rec(3)) Then
Debug.Print rec(1), rec(2), rec(3)
rec.Edit
rec(3) = rec(2)
Debug.Print rec(1), rec(2), rec(3)
rec.Update
End If
rec.MoveNext
Loop
rec.Close
Me.Requery
End Sub

I t populates the blank field in the continuous form when the form is
opened.
Thank you

Claudette

Hi -

Even though you see several records on the screen with a continuous form,
the
On Current event will not fire until the cursor actually enters that
record,
which happens when you click the field or record selector.

Did you try using a saved query which will do the iif calculations , and
then
basing the form on the saved query?

John

Well, I'm stumped. I've not had a subform act like that. Your code should
IMO
take care of that. Personally, I'd use a simpler syntax. In the On Current
event, I'd do:

If isnull(me.myField) then me.myfield = someValue

Single line of code in the On Current event for the subform.

Dennis

Hi Dennis, I did try the On Current event, as well as On Load and On
Open.
When the form opens, the control is blank where the value is null; If I

[quoted text clipped - 33 lines]

Thank you,
Claudette