Automatically input into form from a reference table 1

[demax182]

How do I have a form automatically input data in a field from a reference table based on what the user has inputted for other fields?

For example, I have a reference table that displays the company name, the item, and the item price.

My form (based on another table) requires you to input the company name and the item. Based on what the user has inputted in the company and item fields, I want the cost field of this form automatically populated with the correct cost (from the reference table).

I've spent hours trying to figure this out.

 

[TheAceMan1]

How are ya demax182 . . . . .

Have the code, there's just a little too much to fill in as far as names are concerned. So post the following actual names:

[blue]ReferenceTableName.CompanyFieldName
ReferenceTableName.ItemFieldName
ReferenceTableName.PriceFieldName

FormName.CompanyControlName
FormName.ItemControlName
FormName.CostControlName[/blue]

See Ya! . . . . . .

 

[demax182]

tblCraneOrderRate.Contractor
tblCraneOrderRate.[Vehicle Type]
tblCraneOrderRate.Rate

frmCraneOrderSub.cboContractor
frmCraneOrderSub.cboVehicleType
frmCraneOrderSub.txtRate

Thanks for your help.

 

[TheAceMan1]

OK demax182 . . . . .

In the AfterUpdate event of [blue]cboContractor[/blue] & [blue]cboVehicleType[/blue] add the following code:

[blue]   Call GetCost[/blue]

Finally the main routine. While still in the code module for the form [blue]frmCraneOrderSub[/blue], copy/paste the following (Note: you may have to play a little with the column number of the comboboxes to get the right value. Just remember column count starts at zero on the left and includes any fields with width set to zero):

[blue]Public Sub GetCost(Co As String, Itm As String)
   Dim Msg As String, Style As Integer, Title As String
   Dim Criteria As String, DL As String, hldCost
   Dim Co As String, Itm As String
   
   Co = Me!Me!cboContractor.Column(1) & ""
   Itm = Me!cboVehicleType.Colimn(1) & ""
   DL = vbNewLine & vbNewLine
   
   [green]'Validation. Bypass routine if either value is missing.[/green]
   If (Len(Trim(Co)) & "") > 0 And (Len(Trim(Itm)) & "") > 0 Then

      Criteria = "[Contractor] = '" & Co & "' And " & _
                 "[Vehicle Type] = '" & Itm & "';"
      hldCost = DLookup("[Rate]", "tblCraneOrderRate", Criteria)
      
      [green]'Check if matching record found.[/green]
      If IsNull(hldCost) Then
         Msg = "Couldn't find '" & Co & "' with '" & Itm & "'!" & DL & _
               "Check your spelling or Enter new data and try again . . ."
         Style = vbInformation + vbOKOnly
         Title = "Data Not Found Notice! . . . ."
         MsgBox Msg, Style, Title
      Else
         Me!txtRate = hldCost
      End If
   End If
   
End Sub
[/blue]

Thats it . . . . give it a whirl and let me know . . . .

See Ya! . . . . . .

 

[demax182]

Thanks! Got it working now!