autofill

[allenarissa]

Hi Guys,
I am trying to populate some fields from another table and I searched for a solution and don't seem to find it. I have a few tables but basically I want to populate some fields after I select a customer name from a dropdown. I have the two tables linked by the name but if I tried to copy from the second table (client), I get a type mismatched. I want to do it on after update. Any suggestions?

 

[theDBguy]

Hi. Welcome to AWF!

Storing redundant data in multiple tables is against normalization rules and therefore not recommended. The usual approach is to use a query or a combobox to display the related data. Is there any particular reason why you want to duplicate the data in multiple tables.?

 

[allenarissa]

Thank you theDBguy,
what I want to do is a simple package for my brother-in-law for him to be able to generate an invoice. I had setup the tables and what I want to do now, is have a form (if that is the best way) for him to create an invoice and print it. The form will have the invoice number (autogenerated) and a RNC number he needs also autogenerated based on the invoice number but be able for him to change if require. When he selects the customer name from a dropdown, I wanted to populate the other fields with the customer information from the other table. I have attached what I have done so far so you can get an idea (don't mind the spanish field names)

 

[theDBguy]

Thank you theDBguy,
what I want to do is a simple package for my brother-in-law for him to be able to generate an invoice. I had setup the tables and what I want to do now, is have a form (if that is the best way) for him to create an invoice and print it. The form will have the invoice number (autogenerated) and a RNC number he needs also autogenerated based on the invoice number but be able for him to change if require. When he selects the customer name from a dropdown, I wanted to populate the other fields with the customer information from the other table. I have attached what I have done so far so you can get an idea (don't mind the spanish field names)

Here's a screenshot of your table relationships. I highlighted a few things.

1. You don't need Cliente in Factura, because ClienteID is all you need.
2. I'm not sure why you have Cliente_1 to link ClienteID twice to the same table.
3. The link between FacturaDetalles and Productos should be on Producto_ID and not Producto_Desc.

Also, you are using lookup fields at the table level. This is not recommended. Take a look at this article.

The Access Web - The Evils of Lookup Fields in Tables

 

[allenarissa]

I am not sure where cliente_1 is coming from. I keep deleting that relationship and it keeps popping up. I have included a small video so you can see what I want to do. Basically as soon as I select the client name with the dropbox and press tab, I want to populate the RNC, Calle, and the rest of the fields I need that I have inserted yet until I figured the RNC part. I was trying to do an event procedure at the "after update: of the cliente name to populate but that's where I get "type mismatched"

 

[theDBguy]

I am not sure where cliente_1 is coming from. I keep deleting that relationship and it keeps popping up. I have included a small video so you can see what I want to do. Basically as soon as I select the client name with the dropbox and press tab, I want to populate the RNC, Calle, and the rest of the fields I need that I have inserted yet until I figured the RNC part. I was trying to do an event procedure at the "after update: of the cliente name to populate but that's where I get "type mismatched"

But, what I am saying is you shouldn't need to duplicate the information in another table. What happens if the data gets updated? You'll have to make sure all the copies of that data also get updated. That's considered bad practice.

Take a look at the attached and open the Factura form and try selecting a Cliente from the dropdown.

 

[allenarissa]

That is exactly what I was trying to do! What I was thinking was create a "factura" (invoice) with all the fields that needs to be printed and hopefully just do a report based on that table to print. Or you think that will have too much redundant information?

 

[theDBguy]

That is exactly what I was trying to do! What I was thinking was create a "factura" (invoice) with all the fields that needs to be printed and hopefully just do a report based on that table to print. Or you think that will have too much redundant information?

What you'll need for your report is a query. To print all the information you need for the invoice, you will include all the fields from all the source tables into that query.

 

[allenarissa]

ok, thank you. i will look into that. How did you get RNC number to update when I switched clients. I tried to do the same thing for the next field "calle" and selected column(3) but it doesn't populate, If I change it to column(1) or column(2), it populates. I am sorry for all these questions. I really appreciate your help

 

[theDBguy]

ok, thank you. i will look into that. How did you get RNC number to update when I switched clients. I tried to do the same thing for the next field "calle" and selected column(3) but it doesn't populate, If I change it to column(1) or column(2), it populates. I am sorry for all these questions. I really appreciate your help
View attachment 86816

You have to add Calle in the dropdown first. I added RNC but hid it.

 

[allenarissa]

oh, got it. thank you so much

 

[theDBguy]

oh, got it. thank you so much

You're welcome. Good luck with your project.