Automatic search for UnitPrice

[erbarmen]

Hi there. I'm new to Access and to this forum.

So here's my problem.

I want to create an order Entry form, where once I select the Product ID on the Product ID field, the UnitPrice field would look for the product's price in a table and fill in the blank automatically.

What is the best way ton do this?

I appreciate it.

 

[rainman89]

what is your table structure looking like? this can be done by creating fields bound to the fields in a query based on your table

 

[MStCyr]

Hi there. I'm new to Access and to this forum.

So here's my problem.

I want to create an order Entry form, where once I select the Product ID on the Product ID field, the UnitPrice field would look for the product's price in a table and fill in the blank automatically.

What is the best way ton do this?

I appreciate it.

Hello

This is simple to accomplish using the DLookup function, however you must have the appropriate table structure

Best regards

Maurice

 

[CEH]

With the correct structure and a combobox on ProductID you shouldn't need any code.

 

[ajetrumpet]

Erbarmen,

Do you know VB at all? If you've seen it, take a look at this FAQ. The order entry form there does the exact same thing. All you have to do is search for the code that performs the task.

 

[erbarmen]

Awesome guys.

So I have a Table 1

Product ID as primary key
UnitPrice

and Table 2

Order ID
Product ID

I wanna create a Form with the Order ID field, where I enter the Product ID and the UnitPrice appears automatically.

Do I have to create a Query based on Table 1?
And then, what should I do?


Thanks again.

 

[ajetrumpet]

Awesome guys.

So I have a Table 1

Product ID as primary key
UnitPrice

and Table 2

Order ID
Product ID

I wanna create a Form with the Order ID field, where I enter the Product ID and the UnitPrice appears automatically.

Here it is, in very simple terms...

ProductID combo box rowsource...

SELECT table1.productID, table1.unitPrice
   FROM table1;

**ProductID combo box column count = 2
**ProductID column widths = 1;0
**UnitPrice should be a textbox.

"AfterUpdate" event of ProductID combo box...

Me.UnitPriceTextBox = Me.ProductIDComboBox.Column(1)

 

[MStCyr]

Awesome guys.

So I have a Table 1

Product ID as primary key
UnitPrice

and Table 2

Order ID
Product ID

I wanna create a Form with the Order ID field, where I enter the Product ID and the UnitPrice appears automatically.

Do I have to create a Query based on Table 1?
And then, what should I do?


Thanks again.

Good morning

I've taken the liberty of attaching a simple example of how this works. If you need additional help, please feel free to post back.

Best regards

Maurice

 

[erbarmen]

Thank you

Thanks a lot. I think all this answers my questions.

 

[erbarmen]

One more doubt...

I succesfully did what I wanted.

Now, how can I make the Control "UnitPrice"containing the value
=[Product ID].column(2)
record this value in the corresponding Table (instead of just showing the value on the form)?

 

[MStCyr]

I succesfully did what I wanted.

Now, how can I make the Control "UnitPrice"containing the value
=[Product ID].column(2)
record this value in the corresponding Table (instead of just showing the value on the form)?

Good morning

Just place the corresponding table field on the main form and HIDE it.
Then in the AfterUpdate event of the combo you just set the value to the Combo value.

If you need additional help, just post back

Maurice

 

[Simon_MT]

You should store the Unit Price in Table 2 as well, as the Unit price on Table 1 is the prevailing Unit Price. This enables you to historically produce reports that are accurate.

The method described by Maurice varies ever so slightly:

AfterUpdate ProductID
UnitPrice = [Product ID].column(2)

I don't enforce Pricing because there are situations whereby special deals have been done so I would allow the UnitPrice to be editable or allow for discounts.

Simon

Simon

 

[Simon_MT]

The UnitPrice Control Source will be Table2 UnitPrice

AfterUpdate ProductID

UnitPrice = [Product ID].column(1)

Try column one a computers start counting from Zero.

Simon

 

[Simon_MT]

The AfterUpdate occurs on the Selection of the Product - ProductID Combi. Having selected your Product you now can ascertain the price from Table1 and transfer this to Table2.

These combiboxes may seen a bit foreign but you're get the hang of them.

Simon

 

[erbarmen]

I attached my file on the previous post.
Could someone tell me why I'm so messy?
And why the UnitPrice = [Product ID].column(2) thing doesn't work?
I spent hours on this, and I can't figure it out.

 

[ajetrumpet]

Erbarmen,

I thought you got this figured out. Are you still having trouble with it? Did you never get it done?