Need Help With Access Form

[vkumar]

I have a Data entry Form to enter the payment info. I have Invoice ID and Invoice amount etc... in Invoice Table. I want the Invoice amount to appear automatically in the Payment form based on the Invoice Number I enter. I have Invoice number in both tables, Invoice Table(Primary Key) and Payment Table (Foreign Key).


Thank you for your help.

[pbaldy]

Simplest would be this:

BaldyWeb - Autofill

You can also use DLookup or open a recordset to get the value.

[vkumar]

I am sorry, I don't know how to use either one of them. I have attached a small sample DB. Could you please tell me how to do it on it. Mine is almost similar to it, just with more fields Thank you.

[vkumar]

I forgot to create the form to enter payment in the sample DB. The revised DB is attached. Thank you.

[alansidman]

Why do you have the payments in a separate table. I don't see any logical reason when this could easily be in the same table as the invoice. Add a field for pmtDate, pmtAmount.

Alan

Edit: Cross Posted at: http://www.access-programmers.co.uk/...d.php?t=240413

Please read this: http://www.excelguru.ca/content.php?184

[vkumar]

Like I said the DB is much bigger. The customer wants seperate tables and to run some reports, I need to have seperate tables.

[vkumar]

I used Dlookup, it didn't work. I changed my field names as oneword using camel case format; InvoiceNumber, InvoiceAmount etc...
Here is the code I used in my payment form:
Private Sub InvoiceNumber_AfterUpdate()
InvoiceAmount = Dlookup ("InvoiceAmount", "InvoiceT", "InvoiceNumber=", & InvoiceNumber)
End Sub

[Missinglinq]

Your syntax is not correct.

If InvoiceNumber is defined as a Number:

Me.InvoiceAmount = Dlookup ("InvoiceAmount", "InvoiceT", "InvoiceNumber=" & Me.InvoiceNumber)

If it's defined as Text:

Me.InvoiceAmount = Dlookup ("InvoiceAmount", "InvoiceT", "InvoiceNumber='" & Me.InvoiceNumber & "'")

Linq ;0)>

[vkumar]

I set the InvoiceNumber as a number field and this is the code I have now.

Private Sub Invoice_Number_AfterUpdate()
Me.InvoiceAmount = DLookup("InvoiceAmount", "InvoiceT", "InvoiceNumber=" & Me.InvoiceNumber)
End Sub

It's still not working.

Your syntax is not correct.

If InvoiceNumber is defined as a Number:

Me.InvoiceAmount = Dlookup ("InvoiceAmount", "InvoiceT", "InvoiceNumber=" & Me.InvoiceNumber)

If it's defined as Text:

Me.InvoiceAmount = Dlookup ("InvoiceAmount", "InvoiceT", "InvoiceNumber='" & Me.InvoiceNumber & "'")

Linq ;0)>

[eki einstein]

I have a Data entry Form to enter the payment info. I have Invoice ID and Invoice amount etc... in Invoice Table. I want the Invoice amount to appear automatically in the Payment form based on the Invoice Number I enter. I have Invoice number in both tables, Invoice Table(Primary Key) and Payment Table (Foreign Key).
Thank you for your help.

are you gona make a partial payment form..? if yes your doing wrong in your table structure... i have revised your database
sampleok.zip
Hope those help...