OnLoad event criteria

[Jim]

If have the code below in the OnLoad event which is working fine for the most
part. The second line is causing a problem for cases where there is no
matching record. The code is setting the value - I'd like to have it check
the value first, and if it matches the value for the related field in the
current form recordset, set the value, otherwise just open the form without
setting the value.

If CurrentProject.AllForms("AllOpen").IsLoaded Then
Me.Combo1 = Forms!AllOpen!ID
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo1], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Thanks!
Jim

 

[Dirk Goldgar]

If have the code below in the OnLoad event which is working fine for the
most
part. The second line is causing a problem for cases where there is no
matching record. The code is setting the value - I'd like to have it
check
the value first, and if it matches the value for the related field in the
current form recordset, set the value, otherwise just open the form
without
setting the value.

If CurrentProject.AllForms("AllOpen").IsLoaded Then
Me.Combo1 = Forms!AllOpen!ID
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo1], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Other considerations aside, the line that tests whether the FindFirst found
a match is wrong. I know that it's what the help file says and what the
wizards build, but it's still wrong. In a DAO recordset, which is what
you've got unless you're working in an ADP (or explicitly set the form to an
ADO recordset), a failed FindFirst sets the recordset's .NoMatch property,
and doesn't move to EOF.

Anyway, I think from what you say that maybe this is more what you had in
mind:

'------ start of revised code ------
Dim lngID As Long

If CurrentProject.AllForms("AllOpen").IsLoaded Then

lngID = Nz(Forms!AllOpen!ID, 0)

With Me.RecordsetClone
.FindFirst "ID = " & lngID
If Not .NoMatch Then
Me.Combo1 = lngID
Me.Bookmark = .Bookmark
End If
End With

End If
'------ end of revised code ------

 

[Jim]

This helps, but I'm not quite there.

What I'm trying to determine is if the value from Forms!AllOpen!ID is an
item on the combo box list. If it is, then Me.Combo1 = Forms!AllOpen!ID
If it isn't, then just open the form without setting the value.

So somevalue would be me.combo1, but not sure how to reference the list or
recordset?

You could use a nested If:

If CurrentProject.AllForms("AllOpen").IsLoaded Then
If Me.SomeValue <> 0 Then
Me.Combo1 = Forms!AllOpen!ID
End If
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = """ & Str(Nz(Me![Combo1], 0)) & """"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If

You did not specify what you want to check, so I used a generic field
SomeValue with an arbitrary value of 0.

If ID is a text field you can use the Str function, as you have done, to
convert the Combo1 value to a string, but I think you will need extra quote
marks. But if it comes to that, why not just have the Combo1 value be text
rather than number in the first place?

I don't know where you want to close the opening If, so I did so at the end,
but you may want to do it differently.

If have the code below in the OnLoad event which is working fine for the most
part. The second line is causing a problem for cases where there is no
matching record. The code is setting the value - I'd like to have it check
the value first, and if it matches the value for the related field in the
current form recordset, set the value, otherwise just open the form without
setting the value.

If CurrentProject.AllForms("AllOpen").IsLoaded Then
Me.Combo1 = Forms!AllOpen!ID
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo1], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Thanks!
Jim

--
Message posted via AccessMonster.com


.

 

[Jim]

I replaced the code and now the form opens to the first record in the
recordset if there's no match. I've got some fields that have the visible
property set to false until the after update event of the combo1 control.
Those fields are visible with the first record. Can you help?

If have the code below in the OnLoad event which is working fine for the
most
part. The second line is causing a problem for cases where there is no
matching record. The code is setting the value - I'd like to have it
check
the value first, and if it matches the value for the related field in the
current form recordset, set the value, otherwise just open the form
without
setting the value.

If CurrentProject.AllForms("AllOpen").IsLoaded Then
Me.Combo1 = Forms!AllOpen!ID
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo1], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Other considerations aside, the line that tests whether the FindFirst found
a match is wrong. I know that it's what the help file says and what the
wizards build, but it's still wrong. In a DAO recordset, which is what
you've got unless you're working in an ADP (or explicitly set the form to an
ADO recordset), a failed FindFirst sets the recordset's .NoMatch property,
and doesn't move to EOF.

Anyway, I think from what you say that maybe this is more what you had in
mind:

'------ start of revised code ------
Dim lngID As Long

If CurrentProject.AllForms("AllOpen").IsLoaded Then

lngID = Nz(Forms!AllOpen!ID, 0)

With Me.RecordsetClone
.FindFirst "ID = " & lngID
If Not .NoMatch Then
Me.Combo1 = lngID
Me.Bookmark = .Bookmark
End If
End With

End If
'------ end of revised code ------


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

.

 

[Dirk Goldgar]

I replaced the code and now the form opens to the first record in the
recordset if there's no match.

That is what you wanted, right?

I've got some fields that have the visible
property set to false until the after update event of the combo1 control.
Those fields are visible with the first record. Can you help?

Is the combo box bound or unbound? I'm guessing it is unbound, but if it is
bound, you would normally use both its AfterUpdate event and the form's
Current event to run the code that makes those fields visible or invisible.
That way, as you navigate from record to record and the value of the combo
box changes with the navigation, controls are shown or hidden appropriately.

But if the combo box is unbound, it's not going to change as you go from
record to record. In this case, if you want the other controls to start out
invisible, I'm not sure why you don't just set them to always be invisible
when the form is opened, by setting their .Visible properties to No when in
design view). If for some reason you don't want to do that, but want your
code that opens the form to effectively force the combo box's AfterUpdate
event to fire, what you can do is call the event procedure directly, as in
this modification to my original code:

'------ start of code ------

Dim lngID As Long

If CurrentProject.AllForms("AllOpen").IsLoaded Then

lngID = Nz(Forms!AllOpen!ID, 0)

With Me.RecordsetClone
.FindFirst "ID = " & lngID
If Not .NoMatch Then
Me.Combo1 = lngID
Me.Bookmark = .Bookmark
End If
End With

End If

Call Combo1_AfterUpdate

'------ end of revised code ------