Auto fill after selecting drop down

[roymcd]

Hey all,

Just wondering how I can have a drop down box and once and option is selected it autofills other fields?

Always best with an example of what I am looking for. . .

I have 3 tables

Customers
Invoice
Payment


I have a payment form and use a drop down to select the Invoice number for the payment(this drop down searches a query to get a list of all invoices numbers) .

What I want to happen is when the user selects the invoice number the next field, Customer Name, is automatically filled. I don’t need to save this data as Customer name is not part of the payment table. I just want to show it to the users.
There is no CustomerID in the Payment table.
Only the Invoice and Customer have the CustomerID field.


I was trying to search for this but cant find the right keywords to search for.

Any ideas?

 

[CarlJohnson]

Does your invoice table have a PaymentID field? If so, then you can link to the customer data using the relationship to the invoice table.

I would use all three tables as the data source for your form (make a query), and then you should be able to make relationships and show the payment info and the customer info for each invoice record.

 

[roymcd]

My Invoice does not have an Payment field. But my payment has an Invocie one.

I have attached my DB as its hard to explain. I am using the customer query "CustomerQuery 2" , that has all the information I want to use in this page.

So.... in the Form "Add Payment" I want the "Customer Name" box to show the customer name when the invoice number is selected from the drop down box. I have managed to get the customer name in that drop down box but cant see how to link that to the new "Customer Name" field.

cheers for the help

 

[Poppa Smurf]

Attached is an update version of your database with the following changes:
1. Changed the name of the Cutsomer Name textbox from text22 to customer_name as customer_name is a more meaningful name that text22.
2. Created an After Update event for your invoice combo box and added the follwoing code
Private Sub InvocieNumber_lab_AfterUpdate()
Me!customer_name = InvocieNumber_lab.Column(2)
End Sub
In your query you have Customer in column 3, but the numbering of the columns for coding purposes starts at 0 so this means column 3 in your query becomes column 2 for coding.

Not the name of your Invoice dropdown is spelt as InvocieNumber_lab should it be InvoiceNumber_lab? If so you will will need to change the above code.

 

[roymcd]

Thats great , thanks. but could i trouble you for one more question on it.

I now also want to add another 2 boxes "LefttoPay" and "total_paid" I will then do a calucation on these to to show the amount owed.

I have a seprate querys that does the calucation for total paid already "Payments Per Invocie" but am wondering how I can link that into my button event so that it will show in my new text box when the invocie is selected.

I am thinking now it might of been easier to create a new query that gets all this info but if there was another way to do it I would be interested in learning.

 

[Poppa Smurf]

I now also want to add another 2 boxes "LefttoPay" and "total_paid" I will then do a calucation on these to to show the amount owed.

Are these two boxes on your Add Payment form c_Payments_Add? Please upload your latest version.

 

[roymcd]

Yes there are just 2 new text boxes that i have created.

There is a query that has the amount paid to date which is called "Payments Per Invoice" How can I get this info to fill a text box when another drop down box selects the invoice number.

I though I had it there working for a while when I edited the query that was population the invoice drop down box to include the "sum of payment" field from the "Payments Per Invoice" query but ran into problems when there were invoices that had no payments on them. They would not show in the query and then not be shown in the drop down for invoice number. I have revert back them changes now.


Cheers
Roy

 

[Poppa Smurf]

I updated my version of your database before you posted yours to the forum. I made changes to the recordsource of your Invoice ID and added the two fields, note this layout is not the same as yours. The query used for the recordsource gives you the total paid to date for the invoice and the invoice amount.

When you select the Invoice ID the following are displayed Customer Name, Invoice Amount, Total paid to date prior to the current payment. After the user enters the payment the Balance to be paid is updated.

 

[roymcd]

Would u believe i am only getting back to this now. Been quite busy in work. Thanks for the updated file but there is now and error when I click on the drop down box for the Invocie number in the Add Payment sheet. It is saying that the record source is not correct.

"the record source '~sq_cc_Payments_Add~sq_cInvoice_lab' specified on this form or report does not exist."

I would think its just a small typo as I spelt Invocie wrong all over the system! Can you look at that app usent me again and see does it work for you. It would be strange that it would not work for me and it would for you.


Cheers

 

[Poppa Smurf]

Query qry_payments was missing from the database. I have attached the latest version. Yes I noticed you had Invoice spelt incorrect.

 

[roymcd]

Cheers man, working the way I wanted to now.

All I need is spellcheck for Access and i will be doing great