HELP! How to auto-populate multiple fields using a single combo-box

[June7]

That should have triggered an error message. Names with spaces, special characters, punctuation (underscore is exception), or are reserved words need to be enclosed with []. Try:
Me![site type] = Me.combo466.Column(2)



Did you review my last post?

[tobydobo]

Thanks I will try that June7. And yes I did review your last post and I replied that I needed a little more assistance with that part. What are your thoughts on this method?

[tobydobo]

Ok entered exactly like this:

!Me.[Site Type]<space>=<space>Me.Combo466.Column(2) and got the attached error when I selected a site from the drop down.

[pbaldy]

The code needs to go in the VBA editor, not directly in the property. I think I posted a link about how to do that earlier in post 4?

[June7]

Look at the last post (# 15) on page 1 of this thread. That is my reply to provide additional info for possible alternative.

[tobydobo]

Pbaldy...I was using the Expression builder, that may be the issue then. June7 I will try your suggestion as well, but Im a little unclear as to the steps involved with that. Can I use the expression builder for that?

[tobydobo]

That did it...I used the code builder instead. I think we got a solve here! Let me try adding all my other fields and I will let you know the results. Thanks for sticking with me guys...GREAT help.

[tobydobo]

ok all is working....one last tweak though. It seems to be saving immediately to my table when I select the option from the drop down which creates a new record immediately. I would like to instead, have it auto-populate, but then only save after I finish entering the rest of the unique data on the form, when i click save record.

[June7]

My suggestion does not save data and no expression is constructed and no code involved. It is simply joining tables to show related data. This avoids replication of data, if that is what you are actually doing.

[tobydobo]

well this db does require replication of data as each individual record will sometimes share data among the other records.

[June7]

Don't understand that response. The main principle of relational database is to NOT replicate data. But if you are satisfied with your construction, then please continue.

[tobydobo]

Well my db keeps track of technicians, but it also contains all the info for the sites they support so that you can see everything that relates to that tech at a glance. We currently use multiple worksheets for all this data which makes it hard to go by. My thoughts were to use 1 source for all this data...so if there are say 3 techs that support the same site, all 3 techs will have the same managers and support contact info related to that specific site....hence a duplication of data is required if the db is primarily used to track by tech. I can find no other way around that.

[June7]

Then I suggest that your data structure might not be optimized. Each site can have more than one tech, okay get that. Also sounds like each tech can have more than one site. This is many-to-many relationship. Requires a third table to record associations.

tblTechSite
SiteID (foreign key)
TechID (foreign key)

If each tech can support only one site, then have field in tech table for the siteID.

Either way, replication of data is eliminated.

[tobydobo]

there are a couple of tech's that work at multiple sites, but when that happens, I just have the city selection in the drop down as city\city\city. In these rare cases, they still have the same managers for those multi-sites, I just added additional fields for the additional addresses. But I like your idea of eliminating that...but it sounds a bit complicated for my level of experience. You're welcome to look at my data to analyze though....what would you need to see?

[June7]

You could attach the project to post. Copy & remove confidential data if needed, leave in enough records just for test purposes, run Compact & Repair, zip if still large.

Actually, a properly structured database should be simpler for a new user. Less possibility of having to resort to code and funky queries to get what you want.