Creating Macro to autofill in forms

[asp]

Hi,

I am currently working on a client database. This client has been having trouble entering data consistently the same. There is one table that has all of their drawing data and than I created a separate table which has all the building data. The building data is also in the drawing data table (this has to be there since they use viewing software that links to this table to view the drawings). I created a form for the client to enter the drawing data into but I would like them to be able to select the building name and have the other fields autofill. Can anyone tell me how to create a macro that will do this or if it is even possible. Thanks for your help!

 

[pbaldy]

Like this?

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

 

[asp]

Thanks for the link but I'm not sure where to plug the formula into. I've seen references to After Update Event but can't find that any where. Is there any chance you could go into depth a little more. I'm still a newbie at creating drop-downs or anything more advanced in access. I've pretty much just used it for data entry until recently.

 

[pbaldy]

Does this help?

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

 

[asp]

Here is my database with all the entries deleted. Fac_Drwgs is the main table. All the drop down tables are there just to create drop downs for easier data entry. The Building List is the table I want to autofill into the Fac_Drwgs table.

 

[asp]

When I typed in [Event Procedure] I get the expression builder but it does not have these two lines
Private Sub cmdFind_Click()

End Sub

Am I in the right place?

 

[pbaldy]

What does it have? That's just an example. The control name and event would be different in your case.

 

[asp]

All that is in the Expression Builder when it opens is:
(Event Procedure)

 

[pbaldy]

Are you clicking on the ellipsis on that line, as circled in the link?

 

[asp]

Is there any way you could look at the database I posted and see if you could suggest a code?

The fields I want to Autofill off the Building Name are Building Number, Address, Department Name and Total S F.

I'm trying variations of what you posted and what I have found on other sites but nothing seems to be working.

Any help you can provide would be greatly appreciated! Thanks!

 

[asp]

Yes, I am clicking on the ellipses. Once I do that it brings up the Expression Builder which has (Event Procedure) where the text can be entered and than below the text box are 3 columns, one with folders, one with fields and one with commands(maybe?).

 

[asp]

Btw, below is what I entered into the building number field which I'm not even sure was right.
(Event Procedure)
Private Sub cmdFind_Click()
Me.Building Number=Me.Building Name.Column(1)
End Sub

Do I want the code on the field I want to autofill or on the field that I'm filling in to cause the rest to autofill?

 

[pbaldy]

If you type in [Event Procedure] and then the ellipsis, you should be taken into the VBA editor, not the Expression Builder. As noted in the link, you'd want the code in the after update event of the combo. In your case, when it opens the VBA editor it should say:

Private Sub ComboName_AfterUpdate()

End Sub

and your code setting values would go between those two lines.

 

[asp]

Ok, now I believe I'm in the right place but I entered the following:
Private Sub Building_Name_AfterUpdate()
Me.Building Number=Me.Building Name.Column(1)
End Sub

I got an error when I tried to use the form. It highlighted the first line so I guess something is wrong with what I entered there.

 

[pbaldy]

Because of the inadvisable spaces in your names, they have to be bracketed:

Me.[Building Number]=Me.[Building Name].Column(1)

 

[asp]

That worked for filling in one field. How do I get the other field to populate as well?

 

[pbaldy]

Just add another line:

Private Sub Building_Name_AfterUpdate()
  Me.[Building Number]=Me.[Building Name].Column(1) 
  Me.[SomeOtherField]=Me.[Building Name].Column(2) 
End Sub

 

[asp]

Thanks for all your help!!

 

[asp]

Actually, for some reason it's not accepting that code. Here is what I entered:
Private Sub Building_Name_AfterUpdate()
Me.[Building Number] = Me.[Building Name].Column(1)
Me.[Department Name] = Me.[Building Name].Column(2)
Me.[Address] = Me.[Building Name].Column(3)
Me.[Total S F] = Me.[Building Name].Column(4)
End Sub

Did I do something wrong?

 

[asp]

The error I get is:
Microsoft Office Access can't find the object'
.'
If''
'is a new macro or macro group, make sure you have saved it and that you have typed its name correctly.

Does anyone know what would cause that?