Full Version: Populate From One Table, Insert Into Another
UtterAccess Forums > Microsoft® Access > Access Forms
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 (they store common information, but are not officially linked in any way).

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.
On 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!
theDBguy
Hi,
welcome2UA.gif
You mentioned a "web form." Is this a web database?
MWalker
Yes, it is. I failed to correctly identify it in the title description. Both tables are web tables and the form is a web form. I got it working correctly with a client form because I used VBA and it was quite easy. These web items are ridiculously limited.
Edit: I got it working correctly with a client form but I need to replicate it somehow in a web form. A client form is a very, very last resort. Saying that I got it working correctly with a client form does not mean my problem is solved. Sorry about the confusion.
theDBguy
Hi,
Glad to hear you got it sorted out. Unfortunately, web forms are limited due to the different environment they have to run in.
Good luck with your project.
MWalker
Sorry, perhaps I wasn't clear enough. I DID get it working with a client form, but now I'm trying to do it with a web form because a client form isn't really acceptable. The only reason I mentioned the client form is because I wanted to state that what I am trying to do does in fact work correctly if enough functionality is provided.
So, my question still stands.
theDBguy
Oops, sorry for the confusion. To emulate the functionality that you have in the client form, I think you'll have to set multiple variables to hold the values in each field in memory so that you can use them to assign the data to a new record.
Just my 2 cents... 2cents.gif
MWalker
Thanks. Here's my progress:
The only data you can read from a listbox on a web form is the first column of the selected item. Say this is a unique value. Setting a TempVar for this value in the listbox and using the textbox's defaultvalue property to read that temporary variable WORKS.
However, this is just one of multiple fields, so I need to set temp vars based on a query of that unique value. How can I set temp vars from a query which I KNOW will only return one result?
theDBguy
Hi,
Sorry if I am not being very helpful at all. It's hard to suggest something on web databases because I can't test them. Here's what I am thinking right now. If you can't find a way to populate the textboxes with the data from a query, then maybe the next best thing is to just open up a new form based on that query.
Just my 2 cents... 2cents.gif
MWalker
If I open a new form based on a query of the unique item, then the textboxes will be bound to the query's fields. I'd need some way to keep the text but switch the bounded source to my other table.
The only alternative I can think of would be to do that (open a form with the query and have the text in textboxes) and then open the form linked to personnel_info with the default values of each textbox set to the values of the query form. I am really looking for a better solution, but I have previously decided that if nothing else works, I guess I'll have to create this gross proxy form.
Thanks for your help. You definitely have been helpful!
theDBguy
Hi,
See if what Albert had to say here could be adapted to what you're trying to do.
Just my 2 cents... 2cents.gif
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.
HAs 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?
theDBguy
Hi,
Glad to hear you are making "some" progress. Unfortunately, I have no idea why the buttons would break or how to fix them. How much trouble would it be to replace them?
Just my 2 cents... 2cents.gif
MWalker
Replacing them doesn't work. I'm not really sure what is going on. There are no errors when syncing to Sharepoint and everything works fine on my local computer. I think it might have to do with macros not running or the query string being problematic.
dit: On second thought, I don't think it's the query string because if I type something in the search textbox while the listbox is still loading, the listbox filters correctly. It just must be something with the button.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.