I think you are confusing "Combo boxes" with "Lookup Fields".
You said:
2nd table is tblCompanyClientContact: (fields are)
- autCompanyClientContactID (Primary Key and Auto Number)
- numCompanyClientID (Lookup column from ABOVE)
and
Now i have a form called tblBooking and the fields include:
(I think you meant table instead of form
- autBookingID (Primary Key and AutoNumber)
- dateDateOfBooking
- numCompanyClient (Lookup from 1st table ABOVE)
In both tables you have "Lookup" refering to a field.
Read these two links, then come back:
http://www.mvps.org/access/lookupfields.htm
(this should be one line)
http://www.smartcomputing.com/editorial/article.asp?article=articles/2006/s1706/47s06/47s06.asp
To recap:
* A "Combo box" is a control on a form.
* A "Lookup Field" is a table design option that looks like a combo box, but
hide the true data in the field. "Lookup Field" = Vile
* A "Lookup Field" is NOT the same as a "Lookup TABLE". "Lookup TABLE" = GOOD
Any table could be a "Lookup TABLE".
----
So you 're working on a COPY of your MDB, right???
Open each table in design view and get rid of any "Lookup FIELDS". When you
click on the Lookup tab you should NOT see "Combo box" - you should see "Text
Box".
In table "tblBooking", you can (should) remove the field "txtContactNumber".
It should be in the table "tblCompanyClientContact". Ask yourself: Does a
"booking" have a contact number or does a client have a contact number?
Now we are ready to (manually) create cascading combo boxes.
* Create a new form.
* Add a combo box control. If the wizard starts, cancel it. Open the
properties dialog box. Click on the "OTHER" tab. Change the "NAME" property
to "cboCompany". Change the label also, so you know which combo box you are
looking at. Click on the DATA tab. In the "Row Source" property, paste the
following:
SELECT tblCompanyClient.autCompanyClientID, tblCompanyClient.txtCompanyName
FROM tblCompanyClient ORDER BY tblCompanyClient.txtCompanyName;
Set the "Limit to list" property to "YES".
Click on the "FORMAT" tab. Set these properties:
Column Count = 2
Column widths = 0; 2
Click on the "EVENT" tab. Click in the "AFTER UPDATE", then click on the
down arrow and select "[Event Procedure]". then click on the elipis (...) and
paste these two lines on the procedure:
Me.cboContact = Null
Me.cboContact.Requery
OK, that does it for 1 combo box. Save the form as "frmBookTRY".
Now for the second combo box. I'll skip explaining some things...
* Add a combo box control. If the wizard starts, cancel it.
Open the properties dialog box.
"OTHER" tab: Set the "NAME" property to "cboClient". (Don't forget to change
the label.)
"DATA" tab : Set the "Row Source" property:
SELECT tblCompanyClientContact.autCompanyClientContactID, [txtFirstName] & "
" & [txtSurname] AS FullName, tblCompanyClientContact.numCompanyClientID FROM
tblCompanyClientContact WHERE
(((tblCompanyClientContact.numCompanyClientID)=[forms]![frmBookingTRY].[cbocompany])) ORDER BY [txtFirstName] & " " & [txtSurname];
"Limit to list" property: "YES".
"FORMAT" tab: Set these properties:
Column Count = 3
Column widths = 0";2";0"
Close the form, saving it. Open the form.
Select a company, then select a client.
Now select a different company. Then client combo box should go blank and
the list of clients should be different.
----
That is the basics on creating cascading combo boxes. To use them, the form
would need to be bound to a table (or updatable query) and the combo boxes
bound to the respective fields.
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
ant1983 said:
No hell im sooo close to giving up.... It jsut aint working!!
Lemme give you my actual fields coz maybe im missing something when im
copying your example...
First table is tblCompanyClient: (fields are)
- autCompanyClientID (Primary Key and Auto Number)
- txtCompanyName
2nd table is tblCompanyClientContact: (fields are)
- autCompanyClientContactID (Primary Key and Auto Number)
- numCompanyClientID (Lookup column from ABOVE)
- txtFirstName
- txtSurname
Then i have a query called qryCompanyClientContact where i added the
tblCompanyClientContact-table (my 2nd table above). I then added all those
fields (the 4 fields) and then added a 5th field called "FullName" (
FullName: Trim([txtFirstName] & " " & [txtSurname]) )
Right...
Now i have a form called tblBooking and the fields include:
- autBookingID (Primary Key and AutoNumber)
- dateDateOfBooking
- numCompanyClient (Lookup from 1st table ABOVE)
- txtBookedBy (Lookup from the query (qryCompanyClientContact) ABOVE as i
need the full name)
- txtContactNumber
Right...
Now i have a form called frmBooking and this is where the trouble starts...
Its a straight forward from that runs off the tblBooking table...
So, what do i do now?
(I'm teeeeeeeerribly sorry for not understanding - Honestly stayed up all
night tryna implement your solution but to no avail...
Klatuu said:
What you want to do is filter the list in Combo2 based on the value in Combo1.
To do that, you filter the query that is the row source for Combo2 using the
value in Combo1. So you would define the query such as:
Select ? From SomeWhere WHERE [SomeField] = Me.Combo1
So when you requery Combo2 in the After Update event of Combo1, it will show
only those items that match the value in Combo1.
--
Dave Hargis, Microsoft Access MVP
ant1983 said:
Hey!!!
Thanks for your response!! I think i get it except for the very first part:
You do that by filtering the Client combo row source on the value in the >Company Combo. You will want the bound column of the Company Combo to be the CompanyID.
:O Please can you explain that in english?
Cheers mate!
:
The first thing you asked is called Cascading combos. You do that by
filtering the Client combo row source on the value in the Company Combo. You
will want the bound column of the Company Combo to be the CompanyID. Then in
the After Update event of the Company Combo, you requery the Client Combo:
Private Sub cboCompany_AfterUpdate()
Me.cboClient.Requery
End Sub
Then to populate the controls on the form with the client info, you use the
After Update event of the Client combo. To have the values available,
include all the fields from tblClient you want to use in the combo's row
source. You can set the column widths property so that only the Client Name
shows in the list. The row source would be like:
SELECT ClientID, ClientName, ClientEmailAddress, ClientContractNumber FROM
tblClient WHERE autCompanyID = Me.cboCompany;
Private Sub cboClient_AfterUpdate()
With Me
.txtClientID = .cboClient.Column(0)
.txtClientName = .cboClient.Column(1)
.txtClientEMail = .cboClient.Column(2)
.txtClientContact = .cboClient.Column(3)
End With
--
Dave Hargis, Microsoft Access MVP
:
I have the following tables with the following (simplified) structures:
tblCompany
- autCompanyID
- txtCompanyName
- txtCompanyCountry
tblClient
- autCompanyID (linked to above table)
- autClientID
- txtClientName
- txtClientEmailAddress
- txtClientContactNumber
What I want to achieve is to have a form where, when I select the Company
Name from a drop down box another drop down box, txtClientName, should be
populated with only the clients within that company.
That’s the first thing I want. The 2nd is to, when a Client Name is
selected this should fill out the remainder of the fields. i.e. if John
Smith was selected then John Smith’s Email Addy and Contact number should be
filled out.
I don’t even know where to start!!! Pls help!! Much appreciated!!!!
