Selecting a specific record in a subform

[Guest]

I have a main form for people that has a subform to load their addresses
which are multiple. How do I get the form to open to a specific address
(i.e. a default address). I want to avoid opening to the first address in
the recordset. The table for the addresses distinguishes them by an
"AddressType".
Here is the code I have developed so far but it gives me an error #3070
indicating that it does not recognize the field "Home". This is attached to
the main form's OnOpen event.

Dim sFrm As SubForm, recSet As DAO.Recordset
Set sFrm = Me!subfrmPeopleAndAddresses
Set recSet = sFrm.Form.RecordsetClone
recSet.FindFirst "[AddressTypeName] = Home"
If recSet.NoMatch Then
MsgBox "For some reason there is no Home Address"
Else
sFrm.Form.Bookmark = recSet.Bookmark
End If
Set recSet = Nothing

Thank you! Loren

 

[Dirk Goldgar]

I have a main form for people that has a subform to load their
addresses which are multiple. How do I get the form to open to a
specific address (i.e. a default address). I want to avoid opening
to the first address in the recordset. The table for the addresses
distinguishes them by an "AddressType".
Here is the code I have developed so far but it gives me an error
#3070 indicating that it does not recognize the field "Home". This
is attached to the main form's OnOpen event.

Dim sFrm As SubForm, recSet As DAO.Recordset
Set sFrm = Me!subfrmPeopleAndAddresses
Set recSet = sFrm.Form.RecordsetClone
recSet.FindFirst "[AddressTypeName] = Home"
If recSet.NoMatch Then
MsgBox "For some reason there is no Home Address"
Else
sFrm.Form.Bookmark = recSet.Bookmark
End If
Set recSet = Nothing

Thank you! Loren

If "Home" is the value you are looking for in the field AddressTypeName,
at the very least you need to enclose it in quotes when you go to look
for it:

recSet.FindFirst "[AddressTypeName] = 'Home'"

That said, I think you probably need to do this in the Current event of
the main form, not the Open event. Assuming this is a bound form, it's
not even loaded with data yet in the Open event, and the subform will be
filtered again in the main form's Current event, to match the current
Link Master field(s).

If the overall setup is as I imagine, I'd do something like this:

'----- start of suggested code -----
Private Sub Form_Current()

Dim frm As Form

Set frm = Me!subfrmPeopleAndAddresses.Form

With frm.RecordsetClone
.FindFirst "[AddressTypeName] = 'Home'"
If .NoMatch Then
MsgBox "For some reason there is no Home Address"
Else
frm.Bookmark = .Bookmark
End If
End With

Set frm = Nothing

End Sub
'----- end of suggested code -----

 

[Guest]

Thank you Dirk,
That was very helpful. I have incorporated it into a function:

Function SetAddressTypeListSQL(sfm As SubForm)
On Error GoTo Err_SetAddressTypeListSQL
'SETS THE DEFAULT ADDRESS AS THE CURRENT ADDRESS IN THE FORM'S MAIN RECORDSET
Dim rs As DAO.Recordset
Set rs = sfm.Form.RecordsetClone
With rs
.FindFirst "[AddressTypeName] = " & Chr(34) & conDefaultAddressType
& Chr(34)
If .NoMatch Then
MsgBox "For some reason there is no Home Address"
Else
sfm.Form.Bookmark = .Bookmark
'CREATES THE SQL SOURCE FOR THE SUBFORM'S ADDRESS TYPE LIST
RECORDSET
'THEN SELECTS THE DEFAULT ADDRESS - USUALLY THE HOME ADDRESS
strTypeSQL = "SELECT tblPeopleAndAddresses.AddressTypeName" &
vbNewLine _
& "FROM tblPeopleAndAddresses" & vbNewLine _
& "WHERE (((tblPeopleAndAddresses.PeoplePersonalID)=" &
strPeopleNumber & "));"
sfm.Form![TypeList].RowSource = strTypeSQL
sfm.Form![TypeList].Requery
sfm.Form![TypeList].Value = conDefaultAddressType
End If
End With
'CLEAR THE VARIABLES
Set rs = Nothing
Set sfm = Nothing
Exit_SetAddressTypeListSQL:
Exit Function
Err_SetAddressTypeListSQL:
MsgBox "Error #" & Err.Number & " - generated in Function
SetAddressTypeListSQL" & vbNewLine & Err.Description
Resume Exit_SetAddressTypeListSQL
End Function

Then on the main form OnOpen event and a search button OnClick event I
called the function like this:
Call SetAddressTypeListSQL(Me!subfrmPeopleAndAddresses)

That seems to be working well. Again, many thanks!
Loren

--
Thanks

I have a main form for people that has a subform to load their
addresses which are multiple. How do I get the form to open to a
specific address (i.e. a default address). I want to avoid opening
to the first address in the recordset. The table for the addresses
distinguishes them by an "AddressType".
Here is the code I have developed so far but it gives me an error
#3070 indicating that it does not recognize the field "Home". This
is attached to the main form's OnOpen event.

Dim sFrm As SubForm, recSet As DAO.Recordset
Set sFrm = Me!subfrmPeopleAndAddresses
Set recSet = sFrm.Form.RecordsetClone
recSet.FindFirst "[AddressTypeName] = Home"
If recSet.NoMatch Then
MsgBox "For some reason there is no Home Address"
Else
sFrm.Form.Bookmark = recSet.Bookmark
End If
Set recSet = Nothing

Thank you! Loren

If "Home" is the value you are looking for in the field AddressTypeName,
at the very least you need to enclose it in quotes when you go to look
for it:

recSet.FindFirst "[AddressTypeName] = 'Home'"

That said, I think you probably need to do this in the Current event of
the main form, not the Open event. Assuming this is a bound form, it's
not even loaded with data yet in the Open event, and the subform will be
filtered again in the main form's Current event, to match the current
Link Master field(s).

If the overall setup is as I imagine, I'd do something like this:

'----- start of suggested code -----
Private Sub Form_Current()

Dim frm As Form

Set frm = Me!subfrmPeopleAndAddresses.Form

With frm.RecordsetClone
.FindFirst "[AddressTypeName] = 'Home'"
If .NoMatch Then
MsgBox "For some reason there is no Home Address"
Else
frm.Bookmark = .Bookmark
End If
End With

Set frm = Nothing

End Sub
'----- end of suggested code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)