First time

[shadow]

I just started working with Access this week and it's been great but I ran into some trouble. Hopefully, this is the right place for this thread.



I created 4 tables: main table, User's initials, Transaction Codes, and ID1. From my main table I used the "Lookup Wizard" so i can use the drop down boxes in the forms. Moving on to the form, when I test drove the form, everything works great, down to the "Add new record" button. (Silly thing to be proud of, but it's a new toy for me right now) The records populate and so on.

Here is the question, when I use new initials, TCs, and ID1, I want those fields to show up on the other tables. Those fields are on the main table, but I want them automatically populating the other tables. How do I go about doing that?

Any help would be great. Thanks in advance!

[ajetrumpet]

can you give a little more info, pls? hard to tell what you're after, by 'populating'?

From my main table I used the "Lookup Wizard" so i can use the drop down boxes in the forms.

absolutely do NOT do this. the reasons are plentiful, so trust me. there is nothing wrong with the lookup wizard in tables until you start writing code for the same purpose. Just so you believe what I'm talking about, here's an example:

Suppose your lookup field that you've got is using 2 columns, which is common. the first is ID, the second is the value, which comes from a one to many relationship. That's fine, and it's easy. but the problem is that the field is called something like "username", which suggests that those values are the ones associated with that field. Wrong.

Later, if you go to do something like this in code:

myform.control = dlookup("username", "table", "criteria")

the control will give you the ID that the lookup wizard uses for it's purposes. So you'll end up with something like '59' when you really wanted 'User 123'.

That's one problem, which is biggest pain. Drop the wizard and add the combos in the form for display. It doesn't take but a minute to do that leg work, and there are no regrets later if you need to write any code in the app.

there is also a great page on these issues at mvps: http://www.mvps.org/access/lookupfields.htm

It's well known by the forum community at this point.

[shadow]

Sure. Let me clarify it a bit more. I turned my main table into a form. In that main table contains 10 total fields and 3 of those fields: User Initials, Transaction Codes, and GLA, I created 3 new tables to input in all the users who will use it and so on with the other 2 tables. From there, I created a combo box as you suggested and everything still works. What I want is when a new user, TC, or GLA is entered, not only is it stored in the combo box, but it also goes to the 3 tables I created for each of them, since they already show up on my main able. Would I append the tables to each other?

I replaced the "lookup wizard" with the combo boxes as suggested since I don't want to run into problems.

[ajetrumpet]

What I want is when a new user, TC, or GLA is entered, not only is it stored in the combo box, but it also goes to the 3 tables I created for each of them, since they already show up on my main able. Would I append the tables to each other?

I still have no idea why you seem to duplicating data, but it's none of my business.

Append operations are simple, but they can only be done for complete records if the number of fields AND those fields' data types are all compatible between the 2 tables. So, after a record entry in the main table, if you want to append that new record to the other 3 tables, you could run 3 append statements in a code module, which would be the quickest way to do so. If you're only interested in appending partial record data from your main to the other 3, simply make sure that the number of fields between the 2 match and the data types are compatible.

For instance, if you tried to execute this:

INSERT INTO table2 (userid, username) 

SELECT username, userid FROM maintable

Access will say something like "couldn't append 1 record due to data type conversion error".

So make sure to comply with its rules, or it will shout back at you.

[shadow]

Yeah, it does seem like I am duplicating data, but the main table is completely empty and all the current ID's, codes, and GLA's are in those separate 3 tables right now. The main table contains nothing in it.

Alright, I got them to work. Looks like I was just making things more complicated than they need to be. A simple append was all I needed. Thanks again!

[ajetrumpet]

okie doke. marked as solved.