Auto-Fill in - Is it possible??

[RHomsey]

I constantly put the same information into a form (ie addresses, company names, store numbers). The first thing i always put in is Company name and then the store number.. I am curious if there is a way for it to populate the address and zip code automaticly for me after i put in the store number?? Is this possible?

 

[Travis]

Make the Store Number Field a Combo Box.

Create A Query off of a Table that has the Store Number, Address, and Name.

Make the Store Number Column the only one Visible (This shows only the Store Number)

Then on the AfterUpdate of the Store Number Field set the Other Text Boxes equal to the Column they are represented by in the Combo Box.

Private Sub StoreNumberCombo_AfterUpdate()

   Me.Address=StoreNumberCombo.Column(1)
   Me.StoreName=StoreNumberCombo.Column(2)

End Sub

 

[RHomsey]

I just went through and tried to make a combo box and it was kind of confusing to me ... THere is a wizard that takes you through it... I see somewhat what i need to do but i still havent got it to work. Is there anyway you can tell me step by step how i get this to work?? All i want to put in is Store Name (ie Waffle House) and Store Number (ie 180).. and want it to fill in the address, zip, zone, state. I see now a combo box will do that... Im just not positive on how to set it up correctly.

 

[Rob.Mills]

If you're form is pulling records from two tables who have a one-to-many relationship, you can use an auto-update query. You can learn about that in 'help'.

 

[Travis]

1. Create a Query with your fields ("Store Number", "Store Name","Address","Zip","Zone","State")
2. Add a Combo Box to your form.
3. Make the ComboBox's RowSource = the Created Query's Name
4. Make the ComboBox's ControlSource = the "Store Number" Field
5. Change the ColumnCount Property to 6
   [/list=1]
   
   Add code like that Above to the AfterUpdate Event of the ComboBox.
   
   Column(0)=Store Number
   Column(1)=Store Name
   Column(2)=Address
   Column(3)=Zip
   Column(4)=Zone
   Column(5)=State
   
   Set the field = to the appropriate column.
   
   You can change the column widths property to change the size or hide columns.

 

[vangogh228]

Try this... and you'll see where it leads.

Take any two tables related by a one-to-many relationship. Create a query that includes ONLY the related field from the "many" table and ALL of the "one" table's fields EXCEPT the related field.

Run the query, and then make a valid entry into the "many" table's related field, and the rest of the fields should fill in.

If it works... and it should... save the query, then create a form using the query plus whatever other fields you need.

Q.E.D.

 

[Oldsoftboss]

I use a Dlookup to autofill

AfterUpdate on the client field I have the following

Me!Address = DLookup("address", "clientdata", "[client] = '" & Forms![QuotesFrm]!QuoteTo & "'") & ", " & DLookup("town", "clientdata", "[client] = '" & Forms![QuotesFrm]!QuoteTo & "'")
Me!Email = DLookup("Email", "clientdata", "[client] = '" & Forms![QuotesFrm]!QuoteTo & "'")
Me!QuotePerson = DLookup("contact", "clientdata", "[client] = '" & Forms![QuotesFrm]!QuoteTo & "'")

Puts there Address, Emial Address and Contact Person into the relevent fields

Save the attachment for further (and future) reference.

Dave

 

[Rich]

Domain aggregate functions are generally slow and in most cases like this unnecessary, have a look at the Orders sample db which ships with access for one example of how to "autofill"

 

[Oldsoftboss]

To vangogh228 and Rich

I have mulled over the Orders sample DB forthe past couple of years and could not work out how the info in the fields just "appears". I would sit down and say "right, today Im going to work this out." But end up banging my head against the wall. Now, after a 3 line explanation I have so many possible applications I can apply this to I dont think I'll get any sleep for the next month.
Thanks
Thanks alot

Dave

 

[vangogh228]

Dave:

I am SO glad to pass that information along. I learned it right here on this forum myself!!!

Good luck to you!

Tom