Select when update (1 Viewer)

[Gismo]

Hi all,

I have a 2 combo boxes where the user can select either the part number or the description
After the part number has been selected, the description must be updated
If the description has been selected, then the part number must be updated
But in the case where i do not have a part number nor description, it can be entered manually
When it was entered manually, I dont want any of the update queries to run

How would I accomplish this?

 

[June7]

Should not save both part number and description into record, just part number or the autonumber ID from Parts table.

If part number or description is not listed in combobox, then don't you want this added to the Parts table? Use combobox NotInList event to add new record to lookup table "on-the-fly" during data entry.

 

[Gismo]

So I have discovered that I was missing a link here
In the description dropdown displays the description and the part number, to be able to select the correct part
The update query then looks at the description then takes the first part in the material table then updates that part number instead of the one selected
How would I code the update query to look at the dropdown box selection for the description and the part number selected?

I dont have a reference to the part number selected
How do I look at the second field in the combo box to also take this in to account in the query?

 

[June7]

I don't understand what you are trying to accomplish. Why are you doing UPDATE query?

 

[Gismo]

I don't understand what you are trying to accomplish. Why are you doing UPDATE query?

After I have selected a part number, the description is updated in the temp file
or
After the description selection, the part number is updated

When I complete the transaction, the master file is updated without description

I only update the temp file with the description because I want to have to option to be able to select from description if they dont know the part number

I tried filtering by using below

 

[Gasman]

So I have discovered that I was missing a link here
In the description dropdown displays the description and the part number, to be able to select the correct part
The update query then looks at the description then takes the first part in the material table then updates that part number instead of the one selected
How would I code the update query to look at the dropdown box selection for the description and the part number selected?

I dont have a reference to the part number selected
How do I look at the second field in the combo box to also take this in to account in the query?

View attachment 100815

Use the Column() property.

 

[Gismo]

Use the Column() property.

I used the Column() property
Getting a Undefined function error

Forms![DAW Sheet - Main]!Material.Form!Description.Column(1)

 

[June7]

Can have two comboboxes bound to same field. So only save PartNo - or whatever field is primary key for Parts table - with either combobox.

Side note: Description is a reserved word. Should avoid reserved words as names.

 

[Gismo]

Can have two comboboxes bound to same field. So only save PartNo - or whatever field is primary key for Parts table - with either combobox.

Side note: Description is a reserved word. Should avoid reserved words as names.

Ok, I will avaid using descripyion in the future

The query worked before but now after using Column I get the Undefined function
Any work araound?

Forms![DAW Sheet - Main]!Material.Form!Description.Column(1)

 

[arnelgp]

You cant use Column method of the combobox.
Add an unbound textbox (Visible = No) to your form. Add code to Description combo AfterUpdate event to set the value of the Unbound textbox:

Private sub description_afterupdate()
Me!txtUnbound=Me!description.column(1)
End sub

Now use the Unbound textbox to your Update query.

 

[Gasman]

You cant use Column method of the combobox.
Add an unbound textbox (Visible = No) to your form. Add code to Description combo AfterUpdate event to set the value of the Unbound textbox:

Private sub description_afterupdate()
Me!txtUnbound=Me!description.column(1)
End sub

Now use the Unbound textbox to your Update query.

Oops, sorry Gismo

 

[Gismo]

Oops, sorry Gismo

 

[Gismo]

You cant use Column method of the combobox.
Add an unbound textbox (Visible = No) to your form. Add code to Description combo AfterUpdate event to set the value of the Unbound textbox:

Private sub description_afterupdate()
Me!txtUnbound=Me!description.column(1)
End sub

Now use the Unbound textbox to your Update query.

Works like a charm
Thank you