Populate From One Table, Link ControlSource To Another

[MWalker]

I have two tables: public_records and personnel_info.

• public_records contains a full list of users (storing things such as first name, last name, phone number, and more).
• personnel_info will share a few pieces of information with public_records (both will have a few common field names); however, it will also have a few of its own fields. Because public_records is live, or semi-live, I need to store the redundant information in personnel_info as a 'snapshot' of the information at the time of adding the user.

My goal is to have the user be able to search for a first and last name (by typing it in the corresponding search boxes on the web form). Clicking the search button will then populate a listview of any people matching those names, and it will provide unique information about each person so that the user can clearly distinguish between them. Once the user selects a person from the listview, I need some textboxes to be populated with additional fields from that person's entry in public_records. To be clear, the listview shows only a few unique, identifying items, but the textboxes will show lots of information so the user can make small changes as needed. After they are confident that all the information is correct, I want them to be able to click a save button (because the textboxes are on a data entry form) which will save all of the fields represented by the textboxes into personnel_info.

In short, I want some textboxes populated by a query of public_records, but I want the save button to store the data in those textboxes to personnel_info. All of the searching and the populating of the listview works correctly. My issue is only with the input from one table and the output to another. The farthest I got was being able to do one or the other: populate the textboxes but have them linked to the query, or link them to the personnel_info table but have them start empty. Having the listview display all of the information needed to fill the textboxes and having the user just manually copy all of the data down defeats the point of this system and is not acceptable.

Let me know if any additional information is needed.

Thanks!

[rpeare]

What's your link between the two tables other than first name/last name?
Are you using a bound or unbound form?

[MWalker]

There is no actual link between the tables (via a join in a query or similar). I simply need my web form to act as a proxy, taking data from one, displaying it, and then saving it to the other.

Currently the form is bound (to personnel_info) simply because I wanted the textboxes described above to store data to that table. I'm not sure if it matters? The search textboxes are of course unbound. If this needs to be changed I don't think it will affect anything else.

[rpeare]

Is there a primary key on your web table? I would think there is. If so, why don't you just store that PK in your personnel_info table instead of storing a bunch of fields that may or may not be correct?

You're basically duplicating a bunch of fields for no good reason if you can establish a link between the two tables, even though it's manual, then whenever your web table is updated you are 'automatically' updated with the new information instead of having to go back and run some sort of edit routine.

[MWalker]

Is there a primary key on your web table? I would think there is. If so, why don't you just store that PK in your personnel_info table instead of storing a bunch of fields that may or may not be correct?

You're basically duplicating a bunch of fields for no good reason if you can establish a link between the two tables, even though it's manual, then whenever your web table is updated you are 'automatically' updated with the new information instead of having to go back and run some sort of edit routine.

The primary key is the default ID field, the autonumbered, unique values one.


"I need to store the redundant information in personnel_info as a 'snapshot' of the information at the time of adding the user."
The entries in personnel_info NEED to be unedited. It needs to be a complete history; more than one entry of the same person can be added. I can prevent duplicates later; it's not important to this current situation.

[rpeare]

Ok, even if that's the case, if you relate your individuals by establishing the link to your web db you can click set up an event (for instance a button click) that would capture the data as it exists now rather than having to scroll through people every time to identify the correct person.

Let's say you have 20 John Smiths. In your premise you'd have to cycle through your 20 John Smiths in your web database to identify the correct one each time you wanted to create a snapshot. Whereas if you establish a link between your two tables you can pull the information you want, far more reliably, than choosing from a list of 20 people every time.

What I'm proposing would require another table one with that contains the PK of the web database and one that contains the PK of your *individuals* that make up your personnel info table. It's a modestly different setup but it will make your end effect much easier to attain.

[MWalker]

Ok, even if that's the case, if you relate your individuals by establishing the link to your web db you can click set up an event (for instance a button click) that would capture the data as it exists now rather than having to scroll through people every time to identify the correct person.

Let's say you have 20 John Smiths. In your premise you'd have to cycle through your 20 John Smiths in your web database to identify the correct one each time you wanted to create a snapshot. Whereas if you establish a link between your two tables you can pull the information you want, far more reliably, than choosing from a list of 20 people every time.

What I'm proposing would require another table one with that contains the PK of the web database and one that contains the PK of your *individuals* that make up your personnel info table. It's a modestly different setup but it will make your end effect much easier to attain.

I'm not quite sure what you mean. The form to add a user needs to select people from the public_records table. Once the correct record is found and put into the personnel_info, all other operations will reference the personnel_info table. The public_records table will be 'read-only'. I don't want any 'old' information at the time of adding a person to the personnel_info table. However, after that, the information could either be old or current (it wouldn't matter and nobody would know) since it's a snapshot not to be updated.

Does this make more sense or did your answer take this into account?

Thanks!

[rpeare]

Let's assume you have only one entry in your web table

PublicRecords
PersonID  PersonFN  PersonLN  PersonCity  PersonState
1         Smith     John      Yuma        CO

From my understanding when you are doing your data entry you'd want to copy (for example) YUMA, CO to your personnel_info table.

So for instance the first time you copy your information you would have a record in your personnel_info table like this:

Personnel_Info
PersonnelID  PersonFN  PersonLN  PersonCity  PersonState
1            Smith     John      Yuma        CO

notice that the two tables are unrelated, the only thing they have in common is a first/last name

Now let's say that the web database gets updated so that John Smith now lives in Boulder CO your personnel_info table would then look like:

Personnel_Info
PersonnelID  PersonFN  PersonLN  PersonCity  PersonState
1            Smith     John      Yuma        CO
2            Smith     John      Boulder     CO

Am in incorrect in what you are trying to do if I'm not, please explain with an example

[MWalker]

Your example is correct. This database is primarily meant for recording the physical moving of a person from one building to another, for example. So, if the first time we move them they are in Bldg. 10 and are going to Bldg. 20, the next time we add them, public_records would contain 20 instead. public_records will only ever contain one instance of each person with the most recent data, but personnel_info will hold it at the time the person is added to our records.

"Now let's say that the web database gets updated so that John Smith now lives in Boulder CO"
If you are referring to public_records: The automatic updates of public_records should not touch personnel_info without a user of the form explicitly 'copying them over' with the add form for use later. If this is what you meant then we're on the same page.

I simply (or not so simply, haha) need to populate some text boxes with the data from public_records. Then I need to have the save button store them in personnel_info. Basically, I need to change the conrol source and perhaps query from one to the other. The "default values" so to speak should be loaded from a query on public_records, but then the control source needs to be personnel_info so that calling the SaveRecord macro stores all of that data into personnel_info.


Edit: I'm looking into TempVars. Say the last name is the first column in the listbox of results. Setting the default value of the last name textbox to a temporary variable created from the text of that listbox DOES work. However, to complete the rest of the textboxes, I need to create tempvars based on a query. Any good way to do this?

[rpeare]

MWalker.zip

Here's an example of what I'm talking about

There should be a link between your 'local' and 'web' data.

In my example the 'web' data is in table public_records
the 'local' data is in the table personnel_info
the 'link' table is local_crossref

If you open the form select a person that *HAS* a web record you can click the button and it will copy their information to a new record.
Then change their information on your 'web' table (make sure you change the record and close the table or, at the very least move off the row you're modifying) and click the button again and you'll see the new information is copied to your local (history) type table.

[MWalker]

Unfortunately, your form is a client form. In my OP I stated that I was using a web form. I did attempt this project with a client form and I got it working perfectly because I could use VBA code. In web forms, I don't have access to any more than the first column of a listbox, so I must make that a unique value and somehow run another query on it.

[rpeare]

Ok I think I understand you created a web database (in access) where you are severely limited in that you can't use code you can only use queries/macros and not modules?

[MWalker]

Yes. Exactly. With a client form I have made a very nice, advanced version of what I want. However, with this web form, I'm just trying to transfer results from a query to textboxes (right now I'm trying to use temporary variables). The only issue that I have is that I don't know how to set the temporary variables without making additional textboxes that are bound to the query, and then reloading the primary textboxes to read those temporary variables into their defaultvalue properties. So, it's very ugly, but I might have a solution. Any other ideas are welcomed.

[rpeare]

Can you use criteria in queries like [forms]![formname]![fieldname] and have them work on web based applications?

If so you may be able to create an insert query based on the item selected (either through a combo box or list box) and run the query in a macro.

[MWalker]

What I did was create a query with 3 fields (one being unique) and one parameter. Then, I made a blank form and bound it to that query (so when it loads and is given that parameter, it automatically finds the 1 record in the public_records table. Since there are no textboxes, it simply holds this data, but does not display it. Then, in this form's OnLoad event, I add a macro to SetTempVar on every field that I want. I also add BrowseTo to the form with the textboxes that I was trying to set data in. Each textbox on the form has a defaultvalue expression to set its own text to the value of the tempvar which was just loaded. I set both of these forms as SubForms inside my main form (the query form blends in because it's white).

So, the user can type in the search box for last name. Then, the listbox is updated to contain all people with that last name.
The user then clicks on the listbox item that they want and presses a "Insert" button. This button performs a BrowseTo on the query form, using the listbox value as the parameter.
As explained above, this form will load, query the table for 1 entry, set all of the temporary variables, and then browse to the textbox form.
The textbox form will load and each textbox's value will be properly set. The user can then change any of these values and press the "Save" button which will finally add it into the personnel_info table.

Like I said, this method is UGLY! Also, for some reason, my buttons on the page all broke. Clicking them does nothing and no errors are thrown. I double checked to make sure the macros existed and were in the onclick event. Buttons on other pages work fine. Buttons on the client-side 'preview' of the web forms work fine as well. I'm not sure what happened. Any ideas?