Autofill form

[ukmale65000]

I have created a form for a car collection/delivery service. The form has a field for the customers address.
Also in the form i have 3 items which i would like to automate ?? if possible.
Firstly there is a dealer id box which when filled in i would like to autofill a box which is the dealer address box to save the dealers having to type in their address on every visit.
Secondly there are two tick boxes, which are labelled either collect from home dealership or deliver to home dealership, what i would like to do is if either of these tick boxes are ticked, then the collect from or deliver to address boxes on the form would also autofill.
Hope this is clear enough for anyone to help me.
Thanks in advance

 

[Alansidman]

Look at this site:

http://www.baldyweb.com/Autofill.htm

 

[ukmale65000]

Thanks Alan but that website seems to only talk about filling in combo boxes, as a rank amateur at databases im not sure if it does what i want to do.:banghead:

 

[Alansidman]

If you would like someone to look at you set up, suggest you upload a sanitized (no confidential information) of your data base. Run a compact and repair before uploading and make sure to have enough sample records in the data base to make an educated understanding.

 

[ukmale65000]

Thanks, i have enclosed the database for anyone who can help me, you will see on the form that there should be several options which would auto populate the form if i knew how to do it i.e. entering a dealer number should populate the dealer address.
Ticking the tick boxes should auto populate the relevant boxes, hopefully its self explanatory.

 

[Alansidman]

I'm working on my desktop that still has Access 2002 installed so I cannot look at your file. I would guess that you probably need to do a Dlookup for the fields you wish to populate.

Here is a tutorial

http://www.techonthenet.com/access/functions/domain/dlookup.php

Alan

 

[ukmale65000]

Thanks so if i ave a dealer number say 1001 and they enter that in the dealer number box on the form, where do i put the dlookup code, if i want to populate the dealer name and dealer address boxes (two seperate boxes on the form) ?
Is it in the dealer number box as an event "on enter" or somewhere else?.
Sorry but im what my kids call a noob

 

[Alansidman]

Put it right in the text boxes you want populated.

ie. For Dealer Name Text Box =dlookup("DealerNameField","TableWhereDealerInfoResides","DealerNumberField = x")

If Dealer number is not an integer, look at the reference I gave you on how to format the last criteria.

 

[ukmale65000]

I entered the following DLookUp("Dealer name","Table1","dealerid = 5") and get the result #Name? and if i enter =DLookUp("Dealer name","Table1","dealerid = 5") i get the result #error, im sure you guys can spot the simple mistake im making

 

[ukmale65000]

i have also tried the following code in the after updatefunction of the dealer id box to no avail, no off the form boxes are being filled in at all.

Private Sub Dealer_id_number_AfterUpdate()
Dealername = DLookup("Dealername", "Table1", "dealerid=" & [dealerid])
Dealeraddress = DLookup("Dealeraddress", "Table1", "dealerid=" & [dealerid])
End Sub

 

[Alansidman]

Suggest you upload your db for review. Be sure and make any confidential material dummied up. Run a compact and repair before uploading. We only need a few records so you need not upload the entire data set.

 

[ukmale65000]

Thanks Alan have uploaded the database again, feel free to play with it and delete all the rubbish that's in there and not necessary, i have been playing with macros, queries etc whilst waiting for an answer so its probably all rubbish.
Ideally three problems can be solved
1. Dealer enters his unique id and the dealers name and address boxes in the form auto populate.
2. Dealers tick either of the tick boxes and the relevent fileds on the form auto populate and
3. The button at the end when clicked, will save the form as a new record, clear the form and if possible send me an email of the form.
I am not asking for anyone to do the work for me as i would love to learn, just a few pointers in the right direction would be great.

 

[Alansidman]

ok. I finally got onto my AC2010 machine and looked at both versions of your db. I took the liberty of making some changes that I hope you will find acceptable.
1. I took your information from your table and built a second table that you could use as a lookup table associated with the dealer number. This is linked to a combo box on your Form. Using that and the concepts I suggested earlier at the site BaldyWeb, I then set the dealer name and address to auto poplulate.
2. Based upon the checkboxes, I created an after update event for both that said if they are true (checked), then complete the appropriate information from the look up table (tblDealer). If they box is unchecked then the control is left null.
3. I deleted your macro. I don't use macros (really don't understand them), and created a new command button. Because, when you have a form bound to a table, there is no need to have a save command, as Access does this automatically when you change records, I did not add that feature. Instead, I added code to the button to open a new record and set the focus on the Dealer ID Number control.
4. Because I don't know which email system you are using, I did nothing to add a email of the form information.
5. Look at this link regarding emailing from Access. http://www.access-programmers.co.uk/forums/showthread.php?t=237841 This may be very beneficial to your program.

Please look over all the code and get comfortable with it.

Please look at this web site on data normalization and building out relational data bases. I think it will help you to understand why I created the second table to house the information on the dealer.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Good luck with this. Access has a very steep learning curve. The rewards of learning it are endless.

Alan

 

[ukmale65000]

Thanks Alan, i will look at the coding you have done plus the links you have suggested, you have been so helpful. My next task is to add to the form if the dealer is a new dealer who dosent have an allocated number.
Im sure i will be back on the forums soon lol .
Thanks again

 

[ukmale65000]

OOPs one more question, when im adding new dealers manually to the db, do i add the details to my original table1 or the one you created tbldealer ?

 

[Alansidman]

Add to the tblDealer -- the one I created. You may want to do that with a new form that is bound to that table. Perhaps put a button on the original form to open the new form.

 

[ukmale65000]

The database is now working fine with just one thing i cannot for the life of me work out how to do.
I now have a lookup table which has 5 fields, one is the dealerid number, two is the dealer name, three is the dealer address, four is the collection address and five is the delivery address.
Fields four and five are used to populate a form if a tick box is ticked.
Fields one to three are populated when a user fills in a "New Dealer" form, what i could do with is fields four and five both being filled with the same info as field three, at the moment i am having to manually copy and paste the info, but it would be handy if these fields could be auto filled. Is it possible?

 

[Alansidman]

I am confused. You are trying to populate your table that is used as the record source for your combo box with duplicate information that is already in the table.? This does not make sense.

 

[ukmale65000]

Im getting confused myself now, when you created the second table for me, it is populated when a dealer fills out the new dealer form i have. The form (new dealer) asks for four pieces of information. The Dealers Name, The dealers address, and two new pieces of information i have added to the Table & Form which is Email address and contact number. These form fields populate the table you built ( well there is one small problem they only populate the first row, and do not create a new record each time) but the information does get to the table.
In that table you have two other columns, collectionaddress and deliveryaddress which remain blank when the form is closed, however the collectionaddress and deliveryaddress information is used in the other form i have to autopopulate from the drop down list.
That is why i was asking if the two blank fileds from the table you compiled for me could be automatically filled in.
Have enclosed the database again so hopefully you will see the problem.
Also when i create a record to populate the main table from the collect form, the record is not being saved as a new record, it just overwrites record 1 (same problem for both tables).
Thanks

 

[Alansidman]

Will look at it this weekend and see if I can figure something out for you.

Alan