One last thing.
You need to use + " " & in this example.
Just using + either side of the " " causes another issue.
Hi All,
WOW! There is a lot for me to understand... I will try to implement this and see if I can get it working.
I appreciate the time you took to answer my question :-)
Hi All,
I was able to use the code provided and get it working; however, I noticed that whenever I open the form containing these cbo the second cbo shows all options vs being shortlisted. The shortlisted version only works when a choose a company from the first cbo. I guess this is because the code is called and short listing of the second cbo is performed. This doesnt happen when you close the form and open it again.
Any suggestions?
On forms Open event, you either read 1st combo entry (a default one) or determine it, and then edit the rowsource of 2nd combo, like in event for 1st combo. (Or you call AfterUpdate event for 1st combo.)
Hi Arvil,
Sorry, but that went completely over my head. What do you mean?
Open your database in Edit mode;
Open your form in edit mode. When at right of Project window Property Sheet is displayed, then go to next, otherwiswe right-click on left-top corner of form, and select Properties from drop-down menu;
In Property Sheet, activate tab Event;
Locate OnOpen event. (When there is not "[Event Procedure]" displayed in 2nd column click on button <v> at right and select "[Event Procedure].) Open procedure, clicking on <...> button at right.
You see a code like
Enter somewhere between top and bottom rows, but above any rows like "SomeName:" when such exists, and outside of any cycles or IF...Endif clauses, rows likeCode:Private Sub Form_Open(Cancel As Integer) ... End Sub
where Select String is composed like you composed it in AfterUpdate event of your cbbYourCombo1;Code:Private Sub Form_Open(Cancel As Integer) ... Me.cbbYourCombo2.RowSource = "SELECT ...;" Me.cbbYourCombo2.Requery ... End Sub
Close VBA editor and save database.
Or Zip your database and put it into your next post as attachment. Probably someone here edits/adds the event and posts database back.
Thank you for the details answer. Here's what the code looks like. Now when the form opens, cbo2 is empty and when clicked to see possible choices, the list too is empty.
Private Sub Form_Open(Cancel As Integer)
Me.CboContact.RowSource = "SELECT [tblContacts].[ID]," & _
" [tblContacts].[CompanyID]," & _
" [tblContacts].[FirstName]," & _
" [tblContacts].[LastName] " & _
"FROM tblContacts " & _
"WHERE [CompanyID] = " & Me.CboCompany.Value
Me.CboContact.RowSource = Contact
Me.CboContact.Requery
End Sub
There's a nice example of cascading combo boxes here which you may find helpful:
http://www.fmsinc.com/microsoftacces...cascading.html
BTW What happened to concatenating the first & last names as in the early posts in this thread
No need for .ValuePrivate Sub Form_Open(Cancel As Integer)
Me.CboContact.RowSource = "SELECT [tblContacts].[ID]," & _
" [tblContacts].[CompanyID]," & _
" [tblContacts].[FirstName]," & _
" [tblContacts].[LastName] " & _
"FROM tblContacts " & _
"WHERE [CompanyID] = " & Me.CboCompany.Value
"WHERE [CompanyID] = " & Me!CboCompany
is enough.
With next row of code you do overwrite the RowSource you just set with some variable??? Contact!
What is the RowSource now?
Did you want to set default value for combo? Then this row must be (when Contact is variable which holds this default value)
Me.CboContact = Contact
Or did you want to set combo to value of table field [Contact]? Then the row must be
Me.CboContact = Contact
or
Me.CboContact = me.Contact
Move your code to the form Load event, the stored cbo values won't be there at form open.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
Hi,
I used the following code and now when I open the form a prompt box opens that is titled "Enter Parameter Value" with an entry box for "tblContacts.ContacName" and OK Cancel button.
I believe i have made an error in the code below, but I'm unsure what...
'To stop all names from appearing in CBO contact when form opens.
Private Sub Form_Load()
On Error Resume Next
Me.CboContact.RowSource = "SELECT [tblContacts].[ID]," & _
"[tblContacts].[ContactName] " & _
"FROM tblContacts " & _
"WHERE [CompanyID] = " & Me.CboCompany
Me.CboContact = Me.ContactID
Me.CboContact.Requery
End Sub
At various points in this thread, you have
1. Wanted to concatenate first and last name
2. Used them separately
3. Used ContactName
The parameter request could be because contact name is a 'derived' field that hasn't previously had a value assigned.
Reminder, you do not need to type .Value as that's the default
Is CompanyID a number field? Is the bound column of cboCompany also a number field?
I hope I have not confused anyone. While trying to resolve the most recent issue, I tried going through this form in detail to see if I could resolve the problem myself. During this review I noticed I had not concatenated the fields, so I did that now.At various points in this thread, you have
1. Wanted to concatenate first and last name
2. Used them separately
3. Used ContactName
The parameter request could be because contact name is a 'derived' field that hasn't previously had a value assigned.
Reminder, you do not need to type .Value as that's the default
Is CompanyID a number field? Is the bound column of cboCompany also a number field?
CompanyID is a number field and bound column of cbo is a number field.