Auto-Fill Forms with Data from Multiple Tables

[hitpoint]

So I've been at this for about four weeks straight, 9-5, trying to overhaul a DOS based application to the Microsoft Access environment. Google has been my friend thus far, but I'm going to have to compromise my male-ness and roll down the window to ask for directions.

Here's my issue: I'm trying to make a form auto-retrieve information from a table different from the form's record source and save it in the form's unique record source. I want the information to be retrievable by inputting a number defined by the other form's table and have it retrieve multiple records from said table and automatically display them in the new form, thereby saving it to the new table.

Thus far I've monkeyed with relationships, queries, and macros, none of which want to solve my problem. I'm very open to the possibility that all three of them can fix my problem and that it's simply novice error that's preventing me from solving this.

Any help, links, prayers, and monetary donations are very much appreciated.

 

[G37Sam]

well you can use DAO (Data access objects)
perhaps if you post your database i could give you more detailed help
Samer,

 

[hitpoint]

Many thanks for any assistance you can offer.

I'm looking to have some of the information in the form/table "Load Plan" automatically populate in the form "Create Master" based on the primary key in "Load Plan".

 

[G37Sam]

so whats the scenario? where does the user click and what should happen then?

 

[hitpoint]

On the "Master" form, the user inputs LP#, and it automatically draws from the "LP Table" table multiple fields such as VSL, BKG#, etc and not only reflects them in the "Master" form, but saves the values again to the "Master Table."

 

[G37Sam]

so basically just throw in the following under whatever event that triggers the populating

Now i couldnt your primary key for LP so im just going to give you a generalized solution. You'll need to change whats in the curly brackets to fit your application.

Dim db as database, tb as recordset
set db = currentdb
set tb = db.openrecordset("Select * from Load Plan where {load plan ID} =" & me.{load plan ID}
me!{whatever field you want to copy} = tb!{whatever field is to be copied from}
tb.close
db.close

this should work

cheers,
Samer

 

[hitpoint]

New database uploaded because I renamed a few fields to get rid of the "#" sign, because I think it might have been interfering with operators.

I inputted your code under the On Enter function of LPNUM on Create Master. It's complaining of a bug that I can't figure out.

 

[G37Sam]

the way you named your controls is messed up and hence all the bugs. you also changed the names in the tables but forgot to do so in the forms . Change the name of the controls in the form and put that code on the before update NOT on the on enter.
let me know what happens
cheers,

 

[hitpoint]

You, sir, are fabulous. It works, and I assume that adding more lines to that code, each with every field I want to auto-populate, should work.

Also, assuming I want to preserve the space in between names such as "LP Table" instead of bridging it as "LPTable," what is the character for spaces in Access Code?

 

[G37Sam]

haha i aint no sir mate im only 21.. and yes you can throw in as many lines in there as you want to populate more fields.. this way of doing it is called DAO.. read about it its pretty interesting what it can help you do. more than half my code uses DAO.
Regarding the space in VBA it depends on where its being used. if you're referring to controls in a form you can always use [ ]. as in me![load number id] or underscores i guess work as in me!load_number_id
i save my ass all this headache and not use spaces when naming anything. Just capitalize beginning of each word to make it easier to read.
me!LoadNumberID
also i realised in your database you're using 1color 1note 2color 2note etc all in one table.. why dont you do break it down to two tables with 1-many relation?

 

[hitpoint]

I do hope you're making obscene amounts of money doing what you do. I can imagine your databases are far more complex than the crap I have wasting away in my application. Then again, the only languages I've really gotten into have been HTML and mIRC.

Yeah, my code is very dirty by fault of this being my first database. I got thrown into this project against my will. I suppose if I have time at the end of it, I'll clean it all up to where it can be understood by a successor and have editions made to it.

Explain the two tables aspect? I'm hesitant to branch one form's field out into more than one table for fear of spreading it out too far.

 

[G37Sam]

Well, what i understood from your table is that you have 8 sub-things per major thing haha.. and sometimes the users only uses 1 sub thing, the remaining 7 go into waste therefore waste of space which starts making a difference when you hit thousands of records.. and maybe sometime a user may want more than 8 sub things
so what you do is something called a 1-to-many two table relationship
best example is like in a restaurant. 1 table has more than 1 order.. if you want to do it your way then you'll do it all in 1 orders table and just force the table to have a maximum of like 20 orders
and your table will be huge and a waste of space [tableNo,order1,order2,order3 etc..]
the right thing to do is make two tables the first containing like the table info [tableID, tableName, tableLocation, SmokingOrNon etc..] and the second would contain the order [orderID,tableID(this will be linked to the tableID from tableInfo),order,price etc..]
and to do the linking you'll have to make sure theyre both of the same datatype. Then from orders table you make the tableID's datatype lookupwizard.. choose to look tables up from the [table info] table. Then go to the relationships page, double click on the line linking the tables to orders and choose Enforce Referential Integrity. Now you have yourself a 1-many relationship. Now try applying that to your database. I suggest you keep a back up of your original database too. That was like a very briefed example. You should read more about it from MSDN help there are tens of pages written about relationships and all.
Hope i clarified rather than made complicated.
Cheers,

 

[hitpoint]

I'll definitely be looking into that. This database'll end up having a large number of records. If I could only figure out these problems.

So, back to the original code you gave me, it produces an error when it can't call upon a file number to get extra fields for. Is there any way to null this error? I would like it so that if it can't find a record, that data can be manually inputted anyway.

Also, the database is attached... I'm working with the same code you sent me to do pretty much the same operation, though this time via a combo box. It's in the Create Master form under CONSIGNEEDESIGNATION and CONSIGNED TO. I want the user to select a value from the CONSIGNEEDESIGNATION box and have it auto populate the CONSIGNED TO field via information stored in the Consignee Table.

I'm wondering what's causing it to produce an error...