Using a Query or a table

[kplatt]

Hello, this may be a complicated question but I will try to make it as clear as possible. Currenly I have a form with [Generator Name] field which auto populates the [Generator EPA ID Number] Field through the use of an after update event procedure as below. These two fields are currently in the [Generator table].

Me.Generator_EPA_ID_Number = Me![Generator Name].Column(2)

The problem is that i also need to autopopulate the [Generator Site Address] Field string which is not in a table but i have it in a seperate query. That code is below.

Expr1: Trim([Generator’s Site Address]) & *Chr(13) & Chr(10)* & Trim([City]) & ", " & Trim([St]) & " " & Trim([Zip Code])

So my problem is the fact that the [Generator Name] field is already directed toward the table and i have no way of using the address string query code. Should I recreate the query with all of the Generator Table information including the Expression above? If so how do i reference the query as opposed to the table in the VBA text below. For exampl if the query name was [Query1] and the string field would be called [Expr1]

Private Sub Generator_Name_AfterUpdate()
Me.Generator_EPA_ID_Number = Me![Generator Name].Column(2)

Thanks

 

[boblarson]

You should not be storing redundant data. You should only store the ID and the rest should only be DISPLAYED. You pull the rest of it together with a query when you need the rest later (for reports, etc.).

 

[kplatt]

So you are saying to create a query and only show the affilliated table id on the query along with any expressions that I would have. (ie do not repeat all of the fields in the query that are needed on the form. If this is correct how do i reference the query in VBA. Thanks

 

[kplatt]

OK I think I know what you are saying. You dont think i should have redundant data shown on the form. I understand this but this form will also be an interface for individuals to use and it would be handy for them to see/display the address and other relative information related to the Generator Name on the form. How can i have it display on the form but not be stored in the table and with this said should all the data come from a query vs. Generator Table?

 

[boblarson]

OK I think I know what you are saying. You dont think i should have redundant data shown on the form. I understand this but this form will also be an interface for individuals to use and it would be handy for them to see/display the address and other relative information related to the Generator Name on the form. How can i have it display on the form but not be stored in the table and with this said should all the data come from a query vs. Generator Table?

It's okay to display it on the form, just don't store it in the second table. See my example here on how you can include extra field info (displayed) while only storing the ID.

 

[kplatt]

Bob, can you tell me how to link it to a query.
Currently I have the following. Text13 being the address string expression field in [Address Query][Generator Name]. The only thing showing is the word NAME in the address text box in the form. Text13 is an unbound text box

Private Sub Generator_Name_AfterUpdate()
Me.Text13 = Me![AddressQuery]![Generator Name].Column(3)
End Sub

 

[boblarson]

If you are trying to get it in a query you

1. need to get rid of your lookups at the table level.

2. Include both tables in the query and linked on the appropriate key/foreign key fields

3. You include the name field in there.

Perhaps this will show you the query side of things better.

 

[kplatt]

Hi Bob, Thanks, I got the query after updates to work. But now when i close the database the information is not stored in the unbound text box fields. So i would have to update each combo box again for the data to show even when the combo box alredy shows the proper value. How can I make it so that the data in the unbound fields stay all the time unless changed. ( ie when I close and open the database all these fields would remain with content)

 

[boblarson]

Run the combo's After Update event from the Form's On Current event:

Private Sub Form_Current()
   Generator_Name_AfterUpdate
End Sub

 

[kplatt]

Got it and it worked. until next time.... (or next issue) thanks