Combo Box hell

[Guest]

I am running Access 2007 (beta) and am having trouble displaying data. I
have a database with the following tables:

Product Info (with the following fields):-
ProductID (autonumber)
ProductName (Text) and Primarykey
UnitPrice (currency)

Invoice Details (with the following fields):-
Invoice ID (autonumber)
Invoice Number (text)
Invoice Date (Date/Time)
Job Completion Date (Date/Time)
RE: (Text)
Job Description (memo)

Invoice Product Info(with the following fields):-
InvoiceProductID (autonumber)
Invoice ID (number)
ProductID (number)
ProductName (text)
UnitPrice (currency)
QTY (number)

I have inserted the “Invoice Product Info†as a Subform Datasheet into
“Invoice Detailsâ€. What I am trying to achieve is, when I select
“ProductName†(Combobox) and choose from the list, that the “UnitPrice†is
automatically updated with the corresponding data from “Product Info†table.

I have the following Relationships :-
“ProductID†From “Product Info†and “Invoice Product Infoâ€
“Invoice ID†From “ Invoice Details†and “Invoice Product Infoâ€

I have selected RowSource for “ProductName (Combobox)†as “SELECT [Product
Info].ProductID, [Product Info].ProductName, [Product Info].UnitPrice FROM
[Product Info];â€.

I have set the following Properties for the combobox:-
BoundColumn property to 1.
ColumnWidth to 0;;0

This used to display the middle column of data “ProductNameâ€, but now
doesn’t, its blank.
I also inserted the following code as an event:-
Private Sub ProductName_AfterUpdate()
Me!UnitPrice = ProductName.Column(2)

To display the third column of data (UnitPrice) in the UnitPrice field.
This part has never worked.

What am I doing wrong.

 

[Albert D. Kallal]

Try re-building the combo box with the wizard.

Your format of:

I have set the following Properties for the combobox:-
BoundColumn property to 1.
ColumnWidth to 0;;0

Hum, I I don't see a value for the 3 one?..why? you have 3 collums, you need
3 values?

Private Sub ProductName_AfterUpdate()

Me!UnitPrice = ProductName.Column(2)

end sub

Yes, that looks good. You should only need the ONE line of code above. I
would either fix your columns size, or simply delete the combo box, and use
the wizard to build the combo again.

 

[Guest]

Thanks for that.
I have now got the Combo Box showing the right columns of information, but
the After_Update event still isnt inserting the UnitPrice from these columns
into the UnitPrice field. I have the Private Sub code, but no luck.
Any more suggestions.
cheers

 

[Albert D. Kallal]

Thanks for that.
I have now got the Combo Box showing the right columns of information, but
the After_Update event still isnt inserting the UnitPrice from these
columns
into the UnitPrice field. I have the Private Sub code, but no luck.
Any more suggestions.

Is your after update code running? Put in a msgbox "after update" command to
test this....

It sounds likely your code is not running. test this!!!

Make sure you use the UI to enter the code.... (events tab - click on after
update field, then click on the ellipse -- the [...] button. Then choose
code....

If that property setting is blank, then the code you type in will NOT run...

so, is your after update event running?

(remember, this event will ONLY run when you CHANGE the value of the combo).

 

[Guest]

That didnt seem to work either. I have found a past question which was
answered by Ken Snell which has explained it.
http://www.mvps.org/access/forms/frm0058.htm

Many thanks for your help

Steve

Thanks for that.
I have now got the Combo Box showing the right columns of information, but
the After_Update event still isnt inserting the UnitPrice from these
columns
into the UnitPrice field. I have the Private Sub code, but no luck.
Any more suggestions.

Is your after update code running? Put in a msgbox "after update" command to
test this....

It sounds likely your code is not running. test this!!!

Make sure you use the UI to enter the code.... (events tab - click on after
update field, then click on the ellipse -- the [...] button. Then choose
code....

If that property setting is blank, then the code you type in will NOT run...

so, is your after update event running?

(remember, this event will ONLY run when you CHANGE the value of the combo).