Solved Updating labels based on a table's records (1 Viewer)

[Ryan142]

Hi again, I regret to be back so soon - but this forum treated me well the first time so why not.

I'm making a form that is for payments. There will be text boxes for the user to input the amount of the item, but an admin can change the actual fee of these items. So they need to be dynamic. I've designed the layout of the payment form, just getting into the coding aspect of it now.

So I want to get the prices listed above and process them, then change the labels on my frmPaymentScreen to indicate the prices to the user.
I've got the vague idea of using recordsets and such but not the core undertsanding hence the post.

This was my guess at some code but ultimately not sure

Private Sub Form_Load()

    Set rsFees = CurrentDb.OpenRecordset("tblPaymentPricing", dbOpenSnapshot, dbReadOnly)
    
    If rsFees!ProductID = 1 Then
        'don't know what to put here
    Else If rsFees!ProductID = 2 Then
        'don't knwo what to put here
    Else If rsFees!ProductID = 3 Then
        'don't knwo what to put here
    Else
        'don't knwo what to put here
    End If

End Sub

Any help will of course be greatly appreciated!
Thanks in advance, Ryan

 

[theDBguy]

Hi Ryan. Give DLookup() a try first.

 

[Ryan142]

Hi Ryan. Give DLookup() a try first.

Thanks for the quick response, sorry I'm about 3 days into access could you just help me with the syntax for what you mean

 

[MajP]

Here is a good link to the "domain aggregate" function, of which dlookup is one.

Data retrieval functions for MS Access, DLookup, DCount, DSum, DAvg, DMax, DMin, DVar, DStDev, DLast, DFirst

This page explains the common way to handle data retrieval function using DLookUp as an example. Also the aggregate functions DCount, DSum, DAvg, DMax, DMin DVar, DStDev, DLast, DFirst are discussed briefly.

codevba.com

 

[Ryan142]

Here is a good link to the "domain aggregate" function, of which dlookup is one.

Data retrieval functions for MS Access, DLookup, DCount, DSum, DAvg, DMax, DMin, DVar, DStDev, DLast, DFirst

This page explains the common way to handle data retrieval function using DLookUp as an example. Also the aggregate functions DCount, DSum, DAvg, DMax, DMin DVar, DStDev, DLast, DFirst are discussed briefly.

codevba.com

so where would I put the dLookUp? in the If statement or equate it to a variable like at the top

 

[Ryan142]

Here is a good link to the "domain aggregate" function, of which dlookup is one.

Data retrieval functions for MS Access, DLookup, DCount, DSum, DAvg, DMax, DMin, DVar, DStDev, DLast, DFirst

This page explains the common way to handle data retrieval function using DLookUp as an example. Also the aggregate functions DCount, DSum, DAvg, DMax, DMin DVar, DStDev, DLast, DFirst are discussed briefly.

codevba.com

Does this look good?

DLookup("ProductID", "tblPaymentPricing", "1")

 

[MajP]

me.lblRentalFee.caption = dlookup("Fees","tblPaymentPricing","productID = 1")
me.lblLandingFee.caption = dlookup("Fees","tblPaymentPricing","productID = 2")
me.lblInstructorFee.caption = dlookup("Fees","tblPaymentPricing","productID = 3")

 

[Ryan142]

me.lblRentalFee.caption = dlookup("Fees","tblPaymentPricing","productID = 1")
me.lblLandingFee.caption = dlookup("Fees","tblPaymentPricing","productID = 2")
me.lblInstructorFee.caption = dlookup("Fees","tblPaymentPricing","productID = 3")

Ah I got it, thanks for the help. I'll try it now

 

[MajP]

Here are some more examples.

Microsoft Access tips: Getting a value from a table: DLookup()

How to use the DLookup() function to retrieve a value from a table in a Microsoft Access database.

allenbrowne.com

 

[Ryan142]

Here are some more examples.

Microsoft Access tips: Getting a value from a table: DLookup()

How to use the DLookup() function to retrieve a value from a table in a Microsoft Access database.

allenbrowne.com

I see, I got it working now

 

[theDBguy]

I see, I got it working now

Congratulations! Glad to hear you got it sorted out. Good luck with your project.

@MajP Thanks for the assist!

 

[Ryan142]

Congratulations! Glad to hear you got it sorted out. Good luck with your project.

@MajP Thanks for the assist!

thank you and thank you @MajP