I need some help please

[viper550]

Hello everyone,

Im new to Microsoft Access and i would like some help.Ok here is my problem.

I have created 2 combo boxes in a form.The first combo box i want it to show a list of clients.Each client have some machines we work on and each time i choose a client from the first combo box i want to be able to see the list of machines that particular client has in the second combo.So when i choose that particular machine i have a button that when i push it i want it to open an excel file i have created.For each client i have created a different excel file i want to open.



If somebody can help me i would be very very grateful.I am trying to find a way to do this for days now with no success.

[dcrake]

Do a search on cascading combos

David

[viper550]

I know i will sound like a noob but the problem is i am really a noob with microsoft access.

I searched for cascading combos and to tell you the truth i didnt understand much about what i read.

I know,i know you have to take me by the hand on this but i hope you show some patience with me .Could you give some info on what i need to make it work,how the whole thing works etc etc?

Thanks in advance

[dcrake]

Ok

Lets say the row source for the first combo box is

Select CustId, CustName From Customers Order By CustName

The first column (column(0)) is the bound column

On the after update of the combo - after the user has picked a customer form the combo.

The row source for the second combo needs changing to limit the contents for all machines for the selected customer.

Sub Combo1 AfterUpdate()

Me.Combo2.RowSource = "Select MachId,MachName,Etc From [<<TableOrQuery>>] Where CustId=" & Me.Combo1

So if you have a query that contains the customers and machines you would refer to this customer in your second combo.

David

[viper550]

Ok lets see.

I have 2 tables (tblClients,tblClients1).tblClients has 2 columns (ClientID,Client).tblClients1 has 3 columns (CompressorID,CompressorType,Client).

I wrote the first line you told me in the row source and i'm getting the data in the first combo box.On the after update of the first combo i wrote the following line.

Private Sub combo1_AfterUpdate()

Me.combo2.RowSource = "Select CompressorID,CompressorType,Client From [<<tblClients1>>] Where ClientID=" & Me.combo1

End Sub

When i try to select from the second combo box im getting the following error.
"Syntax error (missing operator) in query expression 'ClientID=CCHBC Aigio' "

What am i doing wrong?

[dcrake]

When I referrred to your table using the following

[<<tblClients1>>]

The double chevrons were placemarkers and were for brevity only. They are not needed in the sql

Me.combo2.RowSource = "Select CompressorID,CompressorType,Client From tblClients1 Where ClientID=" & Me.combo1

David

[viper550]

ok i did that.

Now what it does is in some names from the first combo box (the ones that starts with CCHBC) i get the same message as before

Syntax error (missing operator) in query expression 'ClientID=CCHBC Aigio'

While in all the other names when i click on the second combo box it opens a new small window that says parameter value at the top and inside the window ClientID.Under that is a space where i can write anything.

Something is not working right

[dcrake]

You need to set the bound column in your first combo to be the client id. At present it thinks that the client name is the bound column.

David

[viper550]

Do you mean to go to the properties of the first combo box and at the Data menu to change the bound column value?Currently it is set at 1.My ClientID column in both my tables is the first one.What value should i write?

[dcrake]

If the rowsource in combo 1 is

Select ClientId, Client From tblClients Order By Client

Then the following

Column Count : 2
Column Widths: 0cm,3,cm
Bound Column : 1

I asume you client id is a number.

David

[viper550]

Yes ClientID is a number.

I did exactly as you told me.Now what happened is that it stopped giving me the error message for the rest of the names.Now for all the names in the first combo box when i open the second combo box it gives me a new small window as i described in my earlier post.

I will try to make a simple design of what i am getting below
=============================================

Parameter Value ? X

ClientID
-------------------
/ I can write here /
-------------------
OK Cancel

==============================================
In the space i can write only numbers and nothing happens.If i try to write a letter and press ok it says "This expression is typed incorectly or is too complex to be evaluated etc etc.''

What next?

[dcrake]

Can you send a small snippet of your mdb that includes the bit you are struggling on.

David

[viper550]

I sent you a pm.

[dcrake]

You can post here as an attachment. If it is too big then zip it up first.

David

[viper550]

Unfortunately its 3.9MB and i cant make it as an attachment.I am currently uploading it somewhere so i can give you the link