Combo Box

[PennyB]

Any help would be appreciated!

I have a combo box on my contacts form that is titled cboFirmName. The combo
box is based on a query called ContactUnique. Each contact is unique in that
something is different (first name, last name, or address). The firm name
can be duplicated as you can have multiple contacts from the same firm.

I have the following After Update procedure on my combo box; however, it
finds the first record by firm name. When the user clicks on the third
contact for that firm the code below inserts the first record that is found
as that is what is in the code.

How can I get it to utlize the multiple column combo box, but select the
entire record not just the firm name?

Private Sub cboFirmName_AfterUpdate()
Const cQUOTE = """"
Dim strCriteria As String
Dim rstDevs As DAO.Recordset
Set rstDevs = CurrentDb.OpenRecordset("Select * from tblContacts order by
[FirmName] Desc;")
strCriteria = "[FirmName] = " & cQUOTE & cboFirmName & cQUOTE

rstDevs.FindFirst strCriteria

If Not rstDevs.NoMatch Then
With Me
.[First] = rstDevs![First]
.[Last] = rstDevs![Last]
.[Address] = rstDevs![Address]
.[City] = rstDevs![City]
.[State] = rstDevs![State]
.[Zip] = rstDevs![Zip]
.[Phone] = rstDevs![Phone]
.[Fax] = rstDevs![Fax]
. = rstDevs![Email]
.[LicenseType] = rstDevs![LicenseType]
.[LicenseNumber] = rstDevs![LicenseNumber]
.[ExpirationDate] = rstDevs![ExpirationDate]
.[WorkersCompProvider] = rstDevs![WorkersCompProvider]
.[WorkersCompPolicy] = rstDevs![WorkersCompPolicy]
.[WCExpirationDate] = rstDevs![WCExpirationDate]


End With
rstDevs.Close
Set rstDevs = Nothing
End If

End Sub

 

[Steve Sanford]

It's really not necessary to multi-post (add the same post to more than one
discussion group, one discussion group at a time). It is very unlikely that
any post really belongs in more than one or two discussion groups, and it is
easier to track responses to a single post. Take the time to choose the best
single discussion group for your post.

IF you really feel your question belongs in more than one discussion group,
you should cross-post (add a single post to more than one discussion group at
the same time).

But 99.99% of the time, one post to one discussion group will get the best
response....

 

[PennyB]

Very sorry it was an accident. Didn't realize it actually posted as I was
searching in the other group.

It's really not necessary to multi-post (add the same post to more than one
discussion group, one discussion group at a time). It is very unlikely that
any post really belongs in more than one or two discussion groups, and it is
easier to track responses to a single post. Take the time to choose the best
single discussion group for your post.

IF you really feel your question belongs in more than one discussion group,
you should cross-post (add a single post to more than one discussion group at
the same time).

But 99.99% of the time, one post to one discussion group will get the best
response....

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Any help would be appreciated!

I have a combo box on my contacts form that is titled cboFirmName. The combo
box is based on a query called ContactUnique. Each contact is unique in that
something is different (first name, last name, or address). The firm name
can be duplicated as you can have multiple contacts from the same firm.

I have the following After Update procedure on my combo box; however, it
finds the first record by firm name. When the user clicks on the third
contact for that firm the code below inserts the first record that is found
as that is what is in the code.

How can I get it to utlize the multiple column combo box, but select the
entire record not just the firm name?

Private Sub cboFirmName_AfterUpdate()
Const cQUOTE = """"
Dim strCriteria As String
Dim rstDevs As DAO.Recordset
Set rstDevs = CurrentDb.OpenRecordset("Select * from tblContacts order by
[FirmName] Desc;")
strCriteria = "[FirmName] = " & cQUOTE & cboFirmName & cQUOTE

rstDevs.FindFirst strCriteria

If Not rstDevs.NoMatch Then
With Me
.[First] = rstDevs![First]
.[Last] = rstDevs![Last]
.[Address] = rstDevs![Address]
.[City] = rstDevs![City]
.[State] = rstDevs![State]
.[Zip] = rstDevs![Zip]
.[Phone] = rstDevs![Phone]
.[Fax] = rstDevs![Fax]
. = rstDevs![Email]
.[LicenseType] = rstDevs![LicenseType]
.[LicenseNumber] = rstDevs![LicenseNumber]
.[ExpirationDate] = rstDevs![ExpirationDate]
.[WorkersCompProvider] = rstDevs![WorkersCompProvider]
.[WorkersCompPolicy] = rstDevs![WorkersCompPolicy]
.[WCExpirationDate] = rstDevs![WCExpirationDate]


End With
rstDevs.Close
Set rstDevs = Nothing
End If

End Sub[/QUOTE][/QUOTE]