Forms Help

[ZX188]

I have a form and I need to have 2 or 3 fields that are updated with data when I put data in another field (combo box) on that form
e.g Part number (combo box) updates part price and and description
I know how to do this normally but the problem is that the table I get this data from is not the forms main data source
I cant create a query based on the two tables because I cant link the primary key
I am sure this is easy but I cant figure it out
Thanks
Kevin

 

[gwinn7]

In the 'AfterUpdate' event of the combo box, change the rowsource of the subform to match what the user selected in the combo list box. Something like this...

Private Sub Combo_AfterUpdate()

Subform.Rowsource = &quot;SELECT * FROM <yourtable> WHERE <mykey> = &quot; & Me.Combo & &quot;;&quot;

End Sub

That's one way to do it.

Gary
gwinn7

 

[sdk]

Hi,

I don't know if Gary's trick worked, however for simliar problems I have included the fields in the combo box list, which can then be used to set the values for the fields in your form.

Each column in a combobox can be referenced eg:
Forms.[theFormName].[cmbControlName].Column(0)
references the first column (nb: the first column is 0)

On the OnChange or AfterUpdate event of the combo, you can set the values of the fields you want, eg
Me.UpdateField1= Me.[cmbControlName].Column(1)
Me.UpdateField2= Me.[cmbControlName].Column(2)

This means new records will be created with the latest info, and existing records will retain the original data (as opposed to query always returning the latest info).

Hope this helps

Cheers

 

[SimonDavis]

I believe, if all else fails, that a dlookup would do the trick, provided that there exists in the record you are 'pulling' the data from a field that matches the combobox value.

You would add the following code to the afterupdate event of the combobox:

field1.Value = DLookup(&quot;fieldname&quot;, &quot;tablename&quot;, &quot;fieldintable = combobox&quot

Where;

field1 is the empty field on your form where you want to place the data.

Fieldname is the field in the 'remote' table or filter that you want the data from.

Tablename is the name of the remote table (you can also specify a filter name).

Fieldintable is the field in the remote table/filter that matches the chosen value on your form (i.e. the combobox).

Combobox is the value you chose on your form.

You can add as many of these as you like, to fill several fields on your form. It is not always the best way to solve the problem, as it is quite hard work for the application.

 

[ZX188]

The Me.UpdateField1= Me.[cmbControlName].Column(1)worked fine
Thanks
I tried the DLookup but it would only put the first option from my combobox to the field I wanted updated