VLOOKUP Functionality in Access

[Waheed2008]

I am new to access and have got a problem. I have a table Table1 in which there are 2 fields. One is Code and other is Budget. e.g.

Code ------- Budget
A12 --------- 20000
A14 --------- 19000
. ------------- .
. ------------- .

Now in a form, I have different objects for entring data into another table Table2. In this form I have added a Combo Box and populate it with Code values from Table1. There is text box in front of it. I want that when I select some Code value in Combo Box, its corresponding Budget value from Table1 is automaticaly filled up in text box. Moreover, when I click Save button, that value should be saved in Table2 along with other fields.
Can anybudy help me !
Thanks

 

[KenHigg]

Do you want the code value or the budget value to be saved in table2?

 

[missinglinq]

I think he wants to select the Code from the cbo, then store the related Budget amount. If so, you need to:

Create a combobx (called YourComboBox in this code) with the wizard, based on Table1. When asked, move over the Code field, then the Budget field to the right hand box, to be included in the cbo.

Make sure that the textbox (called txtBudget in the following code) is bound to the proper field in Table2.

Then enter this code:

Private Sub YourComboBox_AfterUpdate()
  Me.txtBudget = YourComboBox.Column(1)
End Sub

 

[Waheed2008]

Thanks for so quick reply.

Actualy Code is like some ID given to some department, for example, and each department has allocated some Budget. Code is some string of characters while Budget is dollar amount.

ComboBox on form have code values picked up from Table1. When I use wizered to make ComboBox, he asked me whether I want to save the value into some table. I selected the table and in this way I was able to store Code selected in ComboBox into Table2 field.

The problem is with Budget. I want that after ComBox updated, corresponding value of Budget of selected Code from Table1 should be displayed into TextBox. And that text box value should be saved into a field in Table2.

I hope I have cleared my question.
Thanks

 

[Waheed2008]

I write this event procedure but it has errors. Can you see this or tel me some other method.

Private Sub cmbCodes_AfterUpdate()
  
Dim qdf As QueryDef
Dim rst As Recordset
 
Set qdf = CurrentDb.CreateQueryDef("", "SELECT T1.Budget FROM Table1 AS T1 WHERE T1.Codes=[Value];")
 
qdf.Parameters("Value") = CDbl(Me.cmbCodes)
Set rst = qdf.OpenRecordset
If rst.RecordCount > 0 Then Me.txtBudget = rst!Budget
 
rst.Close
qdf.Close
 
End Sub

 

[Waheed2008]

I have tried this one and it works.

Private Sub cmbCodes_AfterUpdate()
 
txtBudget = DLookup("Budget", "Table1", "Codes=" & "cmbCodes")
 
End Sub

Although I have acieved my objective, I am wandring if it can be solved through SQL query!

Thanks