Combo box help

[maniaouri]

Here it comes:

I have three tables material category / subcategory / data and I am trying to make a form in order to add new data to these one to many related tables.
So I am thinking to have 2 combo boxes one for choosing material caterogy, the other to choose material subcategory and then in a subform to enter the data for the third table.If there the category or subcategory are not in list to be able to enter the new value.
Do you think I should use unbound comboboxes? Do you have any suggestion or an example because I tried sth I found and didn't worked.

Also if there is a book I can buy with many examples of sql programming or the code in visual basic?but I want to be sth practical.

thanks in advance.

 

[namliam]

Yes unbound combo's

Have a search for "cascading combobox" and you should find some good examples.

 

[maniaouri]

but

I did it with the 2 comboboxes but it doesn't work with the subform can you please take a look

 

[namliam]

You want to change stuff in your subform, yet... in your code you are using this form

    Set rs = [COLOR="Red"]Me[/COLOR].Recordset.Clone
    
    rs.FindFirst "[COLOR="Orange"]me![Material Data subform]![Material SubCategoryId][/COLOR] = " & Str(Nz(Me![Combo16], 0))
    If Not rs.EOF Then [COLOR="red"]Me.[/COLOR]Bookmark = rs.Bookmark

Also you dont want to search on the Orange field... This is a reference to a form, but the rs is a table like thing so you want to refer to the column inside the table.

Change those 2 things and you should be good to go.

 

[maniaouri]

I don't want to get you tired but I have 2 questions in order to see if I understood what you said:

1. rs = Me.Recordset.Clone should be changed to the name of the subform? or I have to put the table.I tried me![material data subform] and didn't work.

2.me![Material Data subform]![Material SubCategoryId] should i change it with the name of the table or sth like that
[Forms]![mainfrm]![Subfrm].[Form]![Category]

thanks for your help

 

[namliam]

1. Yes, you are refering to the Me.Recordsetclone which should be Me.Subform.form.recordsetclone
In both the Set line as well as the Bookmark one.

2. No... You just refer to a column like it is in the table.
ie. rs.FindFirst "[ColumnName] = " & Me.SomeCombobox

 

[maniaouri]

Dim rs As Object

Set rs = Me.[Material Data Subform].Form.RecordsetClone
rs.FindFirst "[Material SubCategoryId] = " & Str(Nz(Me![Combo16], 0))
If Not rs.EOF Then Me.[Material Data Subform].Form.Bookmark = rs.Bookmark

i changed them all but now it doesn't do anything.No error but it doesn't get me the result. Can you figure out what happened?

 

[namliam]

Your main form is connected/linked to your subform via the link
Material SubcategoryID = SubCategoryID

Your mainform is not displaying anything, so why
1) Has it got a rowsource at all?
2) Is it linked to your subform

*Note*
You shouldnt use spaces in your table/column names... Some day you will run into problems if you do.

*Note 2*
If you publish some DB on an international forum, please make the content readable?? All these russian/Romenian/whatever characters are "french" to me...
(Which roughly translates as: I dont understand anything about it)

 

[maniaouri]

I did it.
I tried a small trick and it worked.
thanks for your time

 

[maniaouri]

I did it.
I tried a small trick and it worked.
thanks for your time