Populate a table with info from a form

[JudyK]

I am a complete novice at Access - only know some basics.



I have a form called Employees. On that form I have a combo box that lists our Building Locations [BldgLoc]. When a building location is selected, it populates the corresponding Street, City, info in the highlighted green box area. If a person is "Offsite" from one of the locations, I select "Offsite" from the BldgLoc combo box -- problem is, even though it gives me the right information, I need the information to fill in my Employe table field for "Street", etc.

Thanks.

Have tried many things, just don't get it.

[pbaldy]

If you want to save the data, you need the field as the control source, not a formula. See here:

BaldyWeb - Autofill

[JudyK]

IIf([cboBldgLoc]="Offsite",[Home Street],[cboBldgLoc].[column](1))

So where in the formula above would i put my Control Source field called [Street]? The formula states that if the Blg Loc selected is "Offsite", then it should list the employees home street address. I just do not know where to make it fill in the control source of the table field called [Street].

Thanks for the info from your web. I did try several of those options, but just stuck.

[pbaldy]

Since you want to save the value in the table, you want the textbox control source to be just the field name, and incorporate your formula in this example from the link:

Me.TextboxName = Me.ComboName.Column(2)

[JudyK]

Can this be done without using VBA?

[pbaldy]

You could use a macro with SetValue I suppose. Because you want to save the value, you need some way to place the value into the bound textbox. The two methods are VBA and macros. The VBA is pretty simple.

[JudyK]

Sorry, but I'm lost. Will have to look at this tomorrow to figure out how to get this done. Exhausted looking at it.

Thanks.

[pbaldy]

No problem, post back if you're still stuck. Welcome to the site by the way.

[mejia.j88]

hi pbaldy

i have a question along these lines.

i want a cbo to autopopulate about 7 different boxes. the info is being pulled from a query. do i need to make columns for this cbo box in order to code the auto-population?

to clarify my question: is the only way to code the population given by
TxtField1.text= txtCbo.column1 etc.

i want something along the lines of:

txtField1.text = Query1.FIELD NAME. something?
I'm not too sure on how to code this.

[pbaldy]

I would include the columns in the row source. To do it like your example would require DLookup():

txtField1= Dlookup("FIELD NAME", "Query1", "OptionalCriteria")

That would be much less efficient. You'd basically be running the query 7 times.

[JudyK]

Ok, I'm back. Gave up on the macro part. Totally not sure what to do with the VB code. This is what I have:

Private Sub Street_BeforeUpdate()
Me.Street = IIf([cboBldgLoc] = "Offsite", [txtHome Street], cboBldgLoc.Column(1))

End Sub

My field is called [Street]
It is populated from Combo Box cboBldgLoc
If cboBldgLoc has "Offsite" selected in it's box, then I want the field [Street] to use the information entered in field [Home Street]

Both fields [Street] & [Home Street] are in the same table

Just very frustrated.

Thanks for your help. Appreciate your patience.

[pbaldy]

You have that in the before update event of Street. It would typically be in the after update event of cboBldgLoc.

[JudyK]

You are AWESOME! I finally got some quiet time to focus on this and followed what you were trying to tell me. Everything is working perfect thanks to you.

Thank you for all your help and for being so patient with me.

Judy

[pbaldy]

No problem Judy, and welcome to the site by the way!