Combo box problem

[Guest]

Hi all,

I have 2 tables:

1 TblStores
Fields ID Store Name(txt) Store Division(txt)
1 Safeway WOW
2 Woolworths WOW
3 Grocery Holdings GHPL
4 Coles GHPL
5 IGA Ind DC

There are 70 stores and 22 store divisions

2 TblTrans
Many fields but the 2 I need help with are:
Store Name(txt) Store Division(txt)

Store Name is a combobox with the source from TblStore
Store Division is a text box

On my form I would like the user to input the store and the Division text
box automatically populates with the store division.

Does the store division need to have a text box?

In the properties of the Store name:
After Update:
me!Store Division=Me!cboStore Name.column(2)

Bound column 2
Column Count 3
Widths 0";1";0"

When I open the form and select from the Store Name, I get error:

can't find the macro: me!Store Division=Me!cboStore Name

Help please

Harry

 

[Niklas Östergren]

Hi Harry!

Since I don´t know which recordsource you use for your form I have to asume
that it´s either TblStores or a query where TblStores is included and that
you int that case have some relation set up for other tables in that query.

In this case I suggest you try with this:

Use the wizard and use the first option (Lookup value). If you follow the
wizard you will end up with code something like this in After_Update event
for the newly created combobox:

=================================================
' Seek record matching the control
Me.RecordsetClone.FindFirst "[StoreName] = " & Me![NameOnTheNewComboBox]
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.txtModell.SetFocus
==================================================

Then set controlsource of your textbox txtStoreDivision to field
[StoreDivision] in the form´s underlying recordset by selecting the field
ControlSource) in the controls property form.

In my exampel I have got rid of the spaces in your fieldnames since that can
cause you som problem. My suggestion is that you never use spaces in
fieldnames and controlnames.

You also have to change "NameOnTheNewComboBox" to the name of your combobox.

This is quit straight forward actually so I think you are having some
problem with your recordsource for your form OR you are using an unbound
form, which does work as well but requires much more programming and is in
many cases unnessesary (well sometimes anyway).

If you still doesn´t get this to work please copy the SQL-string for the
form´s recordsource and I´ll se what I can do.

// Niklas