Navigation through records 1

[JennieFernandes]

I have a table with a set of records: FiscalYear(format - Number, eg. 2004, 2005), CompanyName(format - text), ItemName(format - text), ItemPrice (format- currency).

I have a form in which I have created four text boxes. First three text boxes are for FiscalYear, CompanyName and ItemName. The user would need to fill in this information.

I want a VB code that automatically finds out the ItemPrice information as entered in the table. I would appreciate if somebody could provide me the code that I should use for this.

Jennie.

 

[TheAceMan1]

How are ya JennieFernandes . . . . .

Try this:
[ol][li]In the [blue]code module[/blue] for the form, copy/paste the following routine:

[blue]Public Sub PostItemPrice()
   Dim Criteria As String, Ans
   
   If Trim(Me!CompanyName & "") <> "" And _
      Trim(Me!ItemName & "") <> "" And _
      IsNumeric(Me!FiscalYear) Then
      
      Criteria = "[FiscalYear]=" & Me!FiscalYear & " AND " & _
                 "[CompanyName]='" & Me!CompanyName & "' AND " & _
                 "[ItemName]='" & Me!ItemName & "'"
      Ans = DLookup("[ItemPrice]", [purple][b]TableName[/b][/purple], Criteria)
      
      If Not IsNull(Ans) Then
         Me!ItemPrice = Ans
      Else
         MsgBox "Item Not Found!"
         Me!ItemPrice = ""
      End If
   Else
      Me!ItemPrice = ""
   End If
   
End Sub[/blue]

[/li][/ol]
Then in the AfterUpdate event of [blue]FiscalYear[/blue], [blue]CompanyName[/blue], and [blue]ItemName[/blue], copy/paste the following:

[blue] Call PostItemPrice[/blue]

[purple]Thats it . . . give it a whirl & let me know . . .[/purple]

See Ya! . . . . . .

 

[JennieFernandes]

Thanks, AceMan

 

[TheAceMan1]

JennieFernandes . . . . .

[blue]   Ans = DLookup("[ItemPrice]", [purple][b]TableName[/b][/purple], Criteria)
[green]Should be:[/green]
   Ans = DLookup("[ItemPrice]", "[purple][b]TableName[/b][/purple]", Criteria)[/blue]

Double quotes are missing around TableName.

See Ya! . . . . . .

 

[JennieFernandes]

Where did you say, you want me to paste the code.....code module or create a macro and run a code.

If I run a macro, I need to use form name instead of "Me" as in the code above. Please confirm.

 

[TheAceMan1]

JennieFernandes . . . . .

[ol][li]In [blue]form design[/blue] view click the code button

. Paste the main routine here below any other code. Then [blue]Alt + Q[/blue] to return to design view.[/li]
[li] In the properties window, events tab, for each of [blue]FiscalYear[/blue], [blue]CompanyName[/blue], and [blue]ItemName[/blue], put the cursor on the line of the [blue]AfterUpdate[/blue] event. Then click the three elipses

just to the right. This is where [blue]Call PostItemPrice[/blue] goes. In between the [blue]Private[/blue] and [blue]End Sub[/blue] lines.[/li][/ol]

[purple]Cheers! . . .[/purple]

See Ya! . . . . . .