INDIRECT command in excel quivalent in Access

[romes]

Bit uninformed with these kinds of things. I have a huge project with load of combo boxes, but if i can just use a simple example of what I am trying to achieve.

in Access 2007 -Lets say I am trying to do quote. I have two tables; one is called Proofs, the other Quote. In the proofs table, I have proof ID, then Proof type, then price. In my quote I have proof type and then price, and then total.

So in the quote table, in the proof type column, I have a combo box where you can select from the "proofs table" what size you want.

A4
A3

In the proofs table, A4’s price is 0.84 and the A3 is 1.52. What I am trying to do on Form View, when I have created an order form, is tell it to automatically pick up the price of what is selected, like you would do in excel with indirect command. So if I select A4, then in the price column, it will automatically give me the price of 0.84 in the price column.

Does that make sense to anyone? I can do it in excel, but I don’t know how to do it in access. I have tried dlookup, but my knowledge of VB is not too good.

[ChipR]

Assuming your combo box is bound to the column containing ProofType, you could try something like:

Private Sub ProofSizeComboBox_AfterUpdate()
 
PriceTextBox = DLookup("Price", "Proofs", _
 "ProofType = """ & ProofSizeComboBox & """")
 
End Sub

[romes]

Thank you so much. I am going to try it.

Kind regards

[romes]

@ChipR
Hi, me again.

i have two tables:

Table 1 is called Proofs and it has three fields, Proofs (which is the primary), Proof ID, Price ID.

Then in my second table, (which I have created a form for) I have:
Quotes ID, Proof (this is where I have a combo box to select between A4 and A3), and then the next field is Price (where I want to use your formula you gave me) and then Total field. I am not concerned about the Total, I know how to get Price and Total to add up.

So I did this in VB:

Private Sub ProofSizeComboBox_AfterUpdate()

Price = DLookup("Price", "Proofs", "ProofID = """ & ProofIDComboBox & """")

End Sub

But nothing happened. What am I doing wrong?

Kind regards
Romes

[romes]

@ChipR
Hi, is there some way I can upload the file?

[ChipR]

Hi romes. It shouldn't be necessary to upload your database. I would not be able to download it anyway.

I suspect that your ProofID field may be numeric. In that case, the DLookup criteria should be formatted as

..."ProofID = " & ProofIDComboBox)

The extra quotes are only needed for delimiting string fields.

Also, if you created the prodecure manually, you still need to go to the combo box properties and set the AfterUpdate event to use the code.

[romes]

@ChipR
Hi there, you are so clever. So do I changed the combo box properties, that seems correct, may I just ask one more question?

Are you saying I must do the following:

Price = DLookup("ProofID = " & ProofIDComboBox)

I have tried it nothing happens. I suspect I will have to go do a course for this. I have been struggling with this for months now. It is driving me up the wall.

[ChipR]

You can find a lot of what you need on Microsoft's reference websites, in this case: DLookup Function (http://office.microsoft.com/en-us/ac...288251033.aspx)
DLookup(expr, domain [, criteria] )
So I was just being lazy, referring to the criteria and not wanting to retype the rest. This function is particularly useful, so check out that page thoroughly and let us know if you still have problems.

[romes]

@ChipR
Thank you for your help. I really appreciate it.

Kind regards
Romes