(Acess 2003)How to auto populate a form using a drop down

[roymcd]

Hey All,

New to access here and I have picked up most of the stuff myself but am having problems with 1 thing for the last 3 days.

I am creating a basic system with 3 tables Customers, Invoices, Payments. I am creating a form that is to take in the details of a new Invoice. I am using the wizard to create the form with all the fields from the invoice table. What I want to happen is when the user uses the drop down to select who the customer is the other customer information gets filled in automatically.

I think the core of the problem is how I have set up my tables. They look like

CUSTOMER
PK – customerID
- Company name
- Contact number
- PhoneNumber


INVOICE
PK – InvoiceId
- invoiceNumber
- CustomerID (lookup of the CustomerID table)
- CompanyName (lookup of the CustomerName table)
- Status
- Date
- Amount

PAYMENT
PK – PaymentID
- CustomerID
- InvoiceID
- PaymountAmount
- Date
- Methiod




Relationships…

Customers.CustomerID -> Payment.CusotmerID
Customers.CustomerID -> Invocie.CustomerID



…So on my form I have created from the Invoices table I change the CustomerID and and Name to drop down boxes. What I want to happen is when the use uses this drop down to select 1 the other one will auto populate.

For example… the user uses the drop down box to select CustomerID = 1. I then want the Company name to be filled in with that is set for CustomerID1.


I know this is a very basic thing that I am missing here but just can’t figure it out.

I also now strongly thing that using the lookup in the table creation could be wrong but have left it there just to show you where I am.

Cheers


Roy

 

[jzwp22]

Welcome to Utter Access!

First, let's address your table structure. There is no need to repeat the customer information in the invoice table; you only need the ID field. Also, I would get rid of the lookup fields at the table level and go with the numeric ID fields; see this link for the problems that lookup fields at the table level can cause. Also, since the invoice is tied to the customer, you do not need to reference the customerID in the payment table. Also the word "date" is a reserved word in Access, so it would be best to use a different name.

CUSTOMER
PK – customerID
- Company name
- Contact number
- PhoneNumber


INVOICE
PK – InvoiceID
- invoiceNumber
- CustomerID (foreign key to cutomer table)
- Status
- dteInvoice
- Amount

PAYMENT
PK – PaymentID
- InvoiceID (foreign key to your invoice table)
- PaymentAmount
- dtePayment
- Methiod

 

[vbaInet]

Welcome to Utter Access!

Maybe not quite!

 

[jzwp22]

OOPS

WELCOME TO AWF!

My apologies

 

[vbaInet]

I was only teasing

 

[roymcd]

Cheers jzwp22,

I have set up the DB as you have suggested but am still having the same problem.

When I create a form for the Invoice table it will now only show the CustomerID, I want the form to show the Customer names and then autopopulate CustomerID so it can be saved as obviously the end users are not goin to know the ID only the name of the companies.

So my idea is when I am using the wizard to create the form I also include some fields from the customer table but am getting now joy there.

I also read that I could create a new combo box and use the properties to set the “row source” to look up all the customer names. That works but problem with that is once the customer name is selected the how to I get the CustomerID text box to be updated also.

I don’t really care how I do it as I know there are always 5 different ways of doing stuff in Access. I just want to create a form that will take in the invoice details and be able to use a drop down box to select the customer name.


Roy

 

[jzwp22]

When I create a form for the Invoice table it will now only show the CustomerID, I want the form to show the Customer names and then autopopulate CustomerID so it can be saved as obviously the end users are not goin to know the ID only the name of the companies.

Not a problem, remove the text box for the customerID and then use the combo box wizard. Base the combo box on your customer table and select the fields you will want. The customer ID field should be the first followed by the customer name and any other fields. When prompted, hide the key field (customerID). You will also need to bind the combo box to the customerID field of the invoice table (the wizard should walk you through that as well). When you bind something to the underlying table, a change to the value will update the field in the underlying table. So if you reselect the customer, the customerID field will be updated in the Invoice table.


Now, you can add a number of textbox controls to your form and set the control source to the other fields brought in by the combo box. The expression would be as follows

=comboboxname.column(x)

where x= the number of the column in the combo box's row source. Access starts counting at zero not one.

So if the row source for the combo box looks like this:

SELECT customerID, customername, customeraddress ...

and you want to display the customername in another control all you have to do is set the control source of a textbox to the following

=comboboxname.column(1)

 

[roymcd]

Thanks, that is just what I was looking for. Big thanks for that. I knew it was going to be easy to do I just needed to know where to begin.



Roy

 

[jzwp22]

Glad to hear that you got it working the way you want. Good luck with your project.

 

[boblarson]

Welcome to Utter Access!

Oh my - I think we'll have to apply sanctions for that mistake

 

[jzwp22]

I had a lapse of memory; it happens more often now that I am closer to geezerhood

 

[roymcd]

Hey Again,

Its a new question but am posting here so people can see the history.

Now I need to create an add Payment form. In this form I have a drop down box for the Invoice numbers but as my invoice table does not have the customer names in it, just the Customer IDs , when I create the combo box and it will only show the Invoice ID and the Customer ID. how do I change the combo box view so that it will show the Invoice Id with the Customer Names?

I was thinking I should be using some sort of query and use that to create the combo box?

that or add Customer ID to the Payments table and not just the Invoice IDs?

 

[jzwp22]

There is no need to add the customer ID to the payment table; in fact, it would violate good database practices. The best approach is to create a query that joins the customer and invoice tables and select the fields you want. Base the combo box on that query. The bound field of the combo box should be the InvoiceID, I assume.

 

[roymcd]

only got around to doing this now. Doing that does seem to give me the results I am looking for but I get an error when I try to save the record.

"You cannot add or change a record because a related record is required in table 'invocie'

but this is strange to me as I am not lookinig to update the invocie table. I am just using a query to match up InvocieID to customer names.

 

[jzwp22]

You'll have to be more specific as to how your forms are set up before we can help you get to the source of the error. Is your form bound to a table or a query? If you want, you can post your database with any sensitive data removed and we can take a look at it.

 

[roymcd]

I have uplodaed db3 to this form. This is a access 2003 version.

If you open the form "PaymentsAdd" and try to add a new payment you will , hopefully, see the error.


Thanks for this
Roy

 

[jzwp22]

Your combo box was not set up correctly. The row source (your customer query) must include the InvoiceID field and that must be bound to the corresponding field in the payment table. I've made the corrections in the attached database

 

[roymcd]

brilliant stuff. I see know where i went wrong.

Thanks again for the help.

 

[jzwp22]

You're welcome!