Adding Information on a Form Based on ID

[mxcsquared]

I've got a form that has certain information and I would like to be capable of clicking on a button entitled "add a client". When that button is pressed I would like another form to open and provide a search query asking for the client's name. When I hit okay I want a list of clients to be viewed based on a partial search. Once I have found the one I want I want to be able to click/do something to get me back to the original form. At this time, all the client information all will be put on the form and introduced to that particular table row.

If you have any questions regarding what I just said please ask.

Thanks in advance,

-- Mike

 

[Pat Hartman]

Why are you adding existing clients to another table? I don't understand the purpose or the process.

 

[mxcsquared]

its an accts rec. program & it is based by month... i sometimes will have clients on different months but I don't want to have to type in their information all the time. So to fix this I figured just finding their name and placing information based on it would be great... Understand? Or maybe there's something else I hadn't thought of.

Thx!
--Mike

 

[mxcsquared]

Basically: How to I put information from one table into another? Here are my fields:

tblAddressBook
Client_ID
Client Name
Address
City
State
Zip

tblAcctsReceivable
Month
Year
Client_ID
Info_1
Info_2
Info_3
Price_1
Price_2
Price_3


Now, I want to be able to place the "Client_ID" that I find into that table field. THEN I want to be able to retrieve information of that Client from a form AND a report.

Make more sense?
Thanks!
--Mike

 

[Pat Hartman]

You are making it harder than necessary. Just use a combo box to select the client_id on the form that adds the accounts receivable records. When you want to see the client information on a form or report, just join the two tables on the client_ID and select the required fields from both tables. Use the query as the RecordSource for your form or report. On a form, be sure to set the Locked property to Yes for the fields from the address book to prevent accidental changes.

 

[mxcsquared]

Not quite sure what you're saying... Not to mention I can't just select the Client_ID from a combo box. I need to be able to search records and make sure I select the correct one and then I need that info brought back to the original form to be viewed. Then I need a report to branch off of that so that I may print it.

 

[Pat Hartman]

The point is - you don't insert an empty record. After you find the client, you insert the record. I would do this with a combo. If the criteria is more than just the client name, I would include the fields that comprise the criteria and use them to limit the records retrieved by my combo.

 

[mxcsquared]

So you're saying I should use a combo box on hundreds of clients I will need to sift through?

 

[Pat Hartman]

You don't need to sift through them. If you simply start typing in a combo, it automatically scrolls for you. As soon as you have entered enough characters to identify a specific customer, you can tab out of the field. For example, in a list of states - Con would bring up Connecticut and so that is all you would have to type.

 

[Pat Hartman]

You don't need to sift through them. If you simply start typing in a combo, it automatically scrolls for you. As soon as you have entered enough characters to identify a specific customer, you can tab out of the field. For example, in a list of states - Con would bring up Connecticut and so that is all you would have to type.

 

[mxcsquared]

Oh I see what you're saying! The problem still with that is I need to be able to do something like this:

Search: Something
Find: X_something


Maybe now you understand my dillemma?

 

[Pat Hartman]

To do this you need to use an unbound textbox. Add a button to run a query that uses criteria similar to:

Where SomeField Like "*" & Forms!yourform!SomeField & "*";

 

[mxcsquared]

ok, so assuming i have donre this on form "frmSearchClient" do I have to put an "OK" button there to search for the record. If so, where to I go next, if not... then how can I get the particular record i want? And what if more then one shows up, how can I transfer the information of the one I want?

I'm sorry, I know this is probably all very simple for you and I really am actually gaining a good bit of knowledge. Please help me with this and I'll be gratefull... another helpful possibility is to recommend me a book with scenarios much like this.

Thanks, greatly appreciated!!
--Mike

 

[Pat Hartman]

Create a form to display the identifying information from the records returned by the search. Use the query with the "like" clause as the RecordSource for this query. So, to run the search, open the form. In the form, add code to the double-click event of the id field, to pass that value back to the original form and requery it.

 

[burnout]

I have something like this in my database. I have a main form, they click on a Search button and a pop up comes up where they can search by last name and first name (first few characters of each) and it brings up a listing of records meeting that criteria, then they click on it and it brings them back to the main form.

Search form is based on the table that contains demographic information (ID Number and name, DOB, SSN, ).

I took out all the details and replaced with dummy data, but something like attached maybe?

 

[mxcsquared]

Wow, that's quite a bit of information. Thanks very much for the help! I'm going to take some time and sift through it and if I have more questions I'll let you know.. Thanks to both of you

BTW! I bought an Access Book today! From now on I'll refer to that before bothering ya