Combo Box to Fill in Form Fields

  • Thread starter Thread starter PennyB
  • Start date Start date
P

PennyB

I have a combo box that I used the wizard to create to show multiple columns.
The stored value is going into my contacts table FirmName. Now, since I
used the wizard the code below was written to find the first record it could
find; however that is assuming that my FirmName is unique, which it is not.
I created a query to use for the combo box called qryContactsUnique which are
unique records, but the firm name can show up several times as there are many
contacts at one firm. So the code below works to fill in my form; however,
it finds the first record and not the entire record the user clicks on. Can
you please help me change the code so that the correct contact information is
loaded into the form?

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
 
More info please..... :)

What is the PK of the table "tblContacts"?

Would you post the SQL of the query "qryContactsUnique"?
 
The primary key is two fields - Job Number and Contact Type. The reason for
this is we only want 1-Owner, 1-Applicant, -1GeneralContractor, and 1
Engineer so there is a maximum of four contacts per job. Hope this makes
sense.

The query is:

SELECT DISTINCT tblContacts.FirmName, tblContacts.First, tblContacts.Last,
tblContacts.Address, tblContacts.City, tblContacts.State, tblContacts.Zip,
tblContacts.Phone, tblContacts.Fax, tblContacts.Email,
tblContacts.LicenseType, tblContacts.LicenseNumber,
tblContacts.ExpirationDate, tblContacts.WorkersCompProvider,
tblContacts.WorkersCompPolicy, tblContacts.WCExpirationDate
FROM tblContacts;

Also, the property is set to unique values.

Thanks for the quick reply.


Steve Sanford said:
More info please..... :)

What is the PK of the table "tblContacts"?

Would you post the SQL of the query "qryContactsUnique"?

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


PennyB said:
I have a combo box that I used the wizard to create to show multiple columns.
The stored value is going into my contacts table FirmName. Now, since I
used the wizard the code below was written to find the first record it could
find; however that is assuming that my FirmName is unique, which it is not.
I created a query to use for the combo box called qryContactsUnique which are
unique records, but the firm name can show up several times as there are many
contacts at one firm. So the code below works to fill in my form; however,
it finds the first record and not the entire record the user clicks on. Can
you please help me change the code so that the correct contact information is
loaded into the form?

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]
Click to expand...
Click to expand...
 
How do you know which line to select in the combo box? Are you looking at
just the FirmName, First name and the Last name?

And....

For the combo box, what is:

on the DATA tab
-------------------
Row Source = qryContactsUnique (is this right?)

Bound Column = ?

on the FORMAT tab
----------------------
Column count = ?

Column widths = ?


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


PennyB said:
The primary key is two fields - Job Number and Contact Type. The reason for
this is we only want 1-Owner, 1-Applicant, -1GeneralContractor, and 1
Engineer so there is a maximum of four contacts per job. Hope this makes
sense.

The query is:

SELECT DISTINCT tblContacts.FirmName, tblContacts.First, tblContacts.Last,
tblContacts.Address, tblContacts.City, tblContacts.State, tblContacts.Zip,
tblContacts.Phone, tblContacts.Fax, tblContacts.Email,
tblContacts.LicenseType, tblContacts.LicenseNumber,
tblContacts.ExpirationDate, tblContacts.WorkersCompProvider,
tblContacts.WorkersCompPolicy, tblContacts.WCExpirationDate
FROM tblContacts;

Also, the property is set to unique values.

Thanks for the quick reply.


Steve Sanford said:
More info please..... :)

What is the PK of the table "tblContacts"?

Would you post the SQL of the query "qryContactsUnique"?

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


PennyB said:
I have a combo box that I used the wizard to create to show multiple columns.
The stored value is going into my contacts table FirmName. Now, since I
used the wizard the code below was written to find the first record it could
find; however that is assuming that my FirmName is unique, which it is not.
I created a query to use for the combo box called qryContactsUnique which are
unique records, but the firm name can show up several times as there are many
contacts at one firm. So the code below works to fill in my form; however,
it finds the first record and not the entire record the user clicks on. Can
you please help me change the code so that the correct contact information is
loaded into the form?

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][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
I've been re-reading your posts and I am a little confused.

All of the data you want to display in the form is in the combo box. So try
the following.

* Comment out every line between "Private Sub cboFirmName_AfterUpdate()"
and "End Sub".

* Add a few blank lines, then paste in the following just below "Private Sub
cboFirmName_AfterUpdate()" :


'---------------------------------------------------
Me.[First] = Me.cboFirmName.Column(1)
Me.[Last] = Me.cboFirmName.Column(2)
Me.[Address] = Me.cboFirmName.Column(3)
Me.[City] = Me.cboFirmName.Column(4)
Me.[State] = Me.cboFirmName.Column(5)
Me.[Zip] = Me.cboFirmName.Column(6)
Me.[Phone] = Me.cboFirmName.Column(7)
Me.[Fax] = Me.cboFirmName.Column(8)
Me. = Me.cboFirmName.Column(9)
Me.[LicenseType] = Me.cboFirmName.Column(10)
Me.[LicenseNumber] = Me.cboFirmName.Column(11)
Me.[ExpirationDate] = Me.cboFirmName.Column(12)
Me.[WorkersCompProvider] = Me.cboFirmName.Column(13)
Me.[WorkersCompPolicy] = Me.cboFirmName.Column(14)
Me.[WCExpirationDate] = Me.cboFirmName.Column(15)
'---------------------------------------------------


- That is it! Every time you select a row in the combo box, the text boxes
on the form will be updated.



One thing - in looking at the SQL for the query, it looks to me like the
table "tblContacts" could be broken into at least 4 tables, maybe 5. It takes
me a lot of time with pencil and paper to design my tables (and talking to a
friend that is very good at it) so I have a (mostly) normalized
structure..... but I am getting a little better. Just my opinion, FWIW.


HTH
 
Thanks for the help. It works fine except if the firm name has more than
one. For example when I pick Andy Gump Construction it fills everything in;
however, I have two Owner-Builder's and when I pick that I get everything
filled in (for the correct record), but then the Firm Name goes blank.


Also, I spoke to a sql designer that does web based databases about the
contacts table when I built it and he said to keep it in one, but he doesn't
use Access at all. I was thinking at first to create four tables one for
each type of contact or keeping the licensing information in another table.
Any suggestions would be great.


Hope this makes sense and thanks for the help.
 
Thank you so much. I figured out what was wrong and now everything is
filling in correct.

When I put the code in that you gave me I didn't change my bound columns
back to 1. Anyway now that I did that it works great. Again thank you.
This has been bugging me for a few days now.


PennyB said:
Thanks for the help. It works fine except if the firm name has more than
one. For example when I pick Andy Gump Construction it fills everything in;
however, I have two Owner-Builder's and when I pick that I get everything
filled in (for the correct record), but then the Firm Name goes blank.


Also, I spoke to a sql designer that does web based databases about the
contacts table when I built it and he said to keep it in one, but he doesn't
use Access at all. I was thinking at first to create four tables one for
each type of contact or keeping the licensing information in another table.
Any suggestions would be great.


Hope this makes sense and thanks for the help.



Steve Sanford said:
I've been re-reading your posts and I am a little confused.

All of the data you want to display in the form is in the combo box. So try
the following.

* Comment out every line between "Private Sub cboFirmName_AfterUpdate()"
and "End Sub".

* Add a few blank lines, then paste in the following just below "Private Sub
cboFirmName_AfterUpdate()" :


'---------------------------------------------------
Me.[First] = Me.cboFirmName.Column(1)
Me.[Last] = Me.cboFirmName.Column(2)
Me.[Address] = Me.cboFirmName.Column(3)
Me.[City] = Me.cboFirmName.Column(4)
Me.[State] = Me.cboFirmName.Column(5)
Me.[Zip] = Me.cboFirmName.Column(6)
Me.[Phone] = Me.cboFirmName.Column(7)
Me.[Fax] = Me.cboFirmName.Column(8)
Me. = Me.cboFirmName.Column(9)
Me.[LicenseType] = Me.cboFirmName.Column(10)
Me.[LicenseNumber] = Me.cboFirmName.Column(11)
Me.[ExpirationDate] = Me.cboFirmName.Column(12)
Me.[WorkersCompProvider] = Me.cboFirmName.Column(13)
Me.[WorkersCompPolicy] = Me.cboFirmName.Column(14)
Me.[WCExpirationDate] = Me.cboFirmName.Column(15)
'---------------------------------------------------


- That is it! Every time you select a row in the combo box, the text boxes
on the form will be updated.



One thing - in looking at the SQL for the query, it looks to me like the
table "tblContacts" could be broken into at least 4 tables, maybe 5. It takes
me a lot of time with pencil and paper to design my tables (and talking to a
friend that is very good at it) so I have a (mostly) normalized
structure..... but I am getting a little better. Just my opinion, FWIW.


HTH[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 
You're welcome..
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


PennyB said:
Thank you so much. I figured out what was wrong and now everything is
filling in correct.

When I put the code in that you gave me I didn't change my bound columns
back to 1. Anyway now that I did that it works great. Again thank you.
This has been bugging me for a few days now.


PennyB said:
Thanks for the help. It works fine except if the firm name has more than
one. For example when I pick Andy Gump Construction it fills everything in;
however, I have two Owner-Builder's and when I pick that I get everything
filled in (for the correct record), but then the Firm Name goes blank.


Also, I spoke to a sql designer that does web based databases about the
contacts table when I built it and he said to keep it in one, but he doesn't
use Access at all. I was thinking at first to create four tables one for
each type of contact or keeping the licensing information in another table.
Any suggestions would be great.


Hope this makes sense and thanks for the help.



Steve Sanford said:
I've been re-reading your posts and I am a little confused.

All of the data you want to display in the form is in the combo box. So try
the following.

* Comment out every line between "Private Sub cboFirmName_AfterUpdate()"
and "End Sub".

* Add a few blank lines, then paste in the following just below "Private Sub
cboFirmName_AfterUpdate()" :


'---------------------------------------------------
Me.[First] = Me.cboFirmName.Column(1)
Me.[Last] = Me.cboFirmName.Column(2)
Me.[Address] = Me.cboFirmName.Column(3)
Me.[City] = Me.cboFirmName.Column(4)
Me.[State] = Me.cboFirmName.Column(5)
Me.[Zip] = Me.cboFirmName.Column(6)
Me.[Phone] = Me.cboFirmName.Column(7)
Me.[Fax] = Me.cboFirmName.Column(8)
Me. = Me.cboFirmName.Column(9)
Me.[LicenseType] = Me.cboFirmName.Column(10)
Me.[LicenseNumber] = Me.cboFirmName.Column(11)
Me.[ExpirationDate] = Me.cboFirmName.Column(12)
Me.[WorkersCompProvider] = Me.cboFirmName.Column(13)
Me.[WorkersCompPolicy] = Me.cboFirmName.Column(14)
Me.[WCExpirationDate] = Me.cboFirmName.Column(15)
'---------------------------------------------------


- That is it! Every time you select a row in the combo box, the text boxes
on the form will be updated.



One thing - in looking at the SQL for the query, it looks to me like the
table "tblContacts" could be broken into at least 4 tables, maybe 5. It takes
me a lot of time with pencil and paper to design my tables (and talking to a
friend that is very good at it) so I have a (mostly) normalized
structure..... but I am getting a little better. Just my opinion, FWIW.


HTH[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top