Creating a Search Form with Ability to Add Found Record to Table

[Ospenc1]

Hello,
I am a novice when it comes to VBA and macros. I am needing help (a lot of help) in creating a VBA that will allow me to search for a record in Table A, and once the record is found, allow me the option to add that record to Table B. Is this possible to do if Table A and Table B are not formatted the same (i.e. one has more fields than the other)? In summary, there is one request; help in creating a VBA that will allow me to search in one table and once the correct record is found, allow me the ability to add it to another table.

Thanks so much!!

 

[Cronk]

You need to be more specific.

How do you want to initiate such search/add action? How is the search information inputted? What is the type of data being searched ie date, string, number? What are the field names and types in the Tables.

The tables do not need to have the same structure. It just makes for more work in copying data. Having written that, the obvious rules would apply that you could not say add text to a numeric field in table B and all required fields would have to be provided, whether they come from table A or elsewhere.

 

[TJPoorman]

Check this site for information on how to complete a search form:
http://allenbrowne.com/ser-62.html

Then you could add a command button in the detail line with vba to run a sql statement to insert the contents of that line to table b

 

[Ospenc1]

Thank you for your response.

The search action would include First Name, Last Name, City, State, Zip Code, Phone Number (The search would not require all of this information, just what is known to the user). This information would be found (or pulled) from the data in Table A. The above fields exist in both tables, but Table B would be for (hypothetical situation) contacted customers, and Table A would have customer information.

Please let me know if I need to elaborate further.
Thanks!

 

[Cronk]

Would you say that you want to have a form bound to TableA with unbound text boxes in the header and a Search button which when clicked would filter on the data included in the text boxes?

Then what do you want to do to cause the record to be copied? Double click on the record, click a Copy button, something else?

Is your basic requirement to copy to TableB those records which do not currently exist in that table? It may be a lot easier to use an Append query.

 

[Ospenc1]

Yes, a form as you described would be what I am looking for, and a "Copy" button would be ideal.

I could run a query, but my goal is to create an automated process so that should someone who has very little knowledge of Access need to add records on a day that I am absent, they could do so with ease.

 

[TJPoorman]

Did you try the link to Allen Browne's website that I provided? Could you adapt that to work with your database?

 

[Cronk]

Ospenc1

I don't know if I'd call that automated.

Can you create a database with that search form and the two tables with dummy data and post it.