Solved: Change form from read-only to edit for users

[Gingertrees]

Creating a client DB for my co. I have a client table with identifying info (first, last, address, birthdate, etc.) AND more in-depth stuff like their doctors, insurance(s), etc. I want my employees to search for clients before they add or edit existing info. So, I created two forms: frmClientSearchReadOnly, which contains just the identifying info; and frmClientEdit, which is a tabbed form with the whole enchilada.

See the "Edit..." command button on the ClientSearch form? I'd like that to open the ClientEdit form, filtered for the selected person, and close the ClientSearch form. VBA seems to be the way, but I don't know how.

I've attached a picture of my forms to illustrate. Please Help! Thanks!

[CreganTur]

Dude... Batman is OLD! No wonder the Joker keeps getting away from him:rofl:

Welcome Gingertress! Always good to see new people here.

You can accomplish what you want by using the WhereCondition parameter of the OpenForm Function:
OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)
Per VBA Help:


WhereCondition Optional Variant. A string expression that's a valid SQL WHERE clause (mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\OFFICE11\1033\vbaac10. chm::/html/acmthactOpenForm.htm#) without the word WHERE.

So, you capture some criteria, I'm guessing an account number, as a variable, and then build your WhereCondition so that you're opening your edit form for that account number.

HTH :thumb

[Gingertrees]

Hmm...I can see how the Where condition would help some, but not exactly what I want.

Most people are going to be searching by first name, last name, or birthdate. The where condition works fine so long as the criteria is unique. But, in my scenario where I am looking for "Bruce" to find client "Bruce Wayne", I could have other clients named "Bruce." Then the Where condition filters for all people named Bruce, leaving the user free and clear to screw up the records of Bruce Smith, Bruce Black, etc in addition to the only one they need to see, Bruce Wayne.:banghead:

I'd like it to go like this: 1) user clicks "find by first name" on client switchbd, 2)they type in first name "Bruce", 3)this brings up the read-only client-search form with all people named Bruce, 4)user selects one, clicks Edit button, 5)thus closing the client-search form and opening the ClientEdit form filtered for ONLY the one client. I have attached a picture of how I'd like the forms to flow. They ALMOST do this now, except for opening the Edit form for ONLY one client.

Ideas? Thanks again.

[CreganTur]

The where condition works fine so long as the criteria is unique
You can assign multiple criteria to the WhereCondition.
But, the bigger issue is the fact that you should have a Primary Key that you could use for the WhereCondition. If the records in your table don't have a primary key, then you're leaving yourself open for a lot of future headaches.

[Gingertrees]

OK, so the user does his search for first name or last name or whatever, and browses through the results until he finds the record he wants. He then clicks the "Edit" command button, which runs the following code I can't finish:

Private Sub Command32_Click()
'I assume there should be some "Dim ___ as ___ " here, especially since half my forms don't show up in the list of Class objects

DoCmd.OpenForm (frmClientEdit, , , ClientID= _ frmClientSearchReadOnly.ClientID ...)

Am I coming close?

[CreganTur]

That code's really close to what you want! A couple of small changes just need to be made.

This should work:

DoCmd.OpenForm "frmClientEdit", , , "ClientID = " & Me.ClientID

Form Names are considered strings, so you have to wrap them in quotation marks. Also, the WhereCondition must be evaluated as a String as well. You use 'frmClientSearchReadOnly.ClientID' to refer to the ClientID textbox on the current form. This is not incorrect, but it is the long way to do it. You can use 'Me.' instead. This referes to the current, open form- the form that the code is behind.

Try the bit of code I provide above- hopefully it will work for you.

[Gingertrees]

Thank you so much, CreganTur. :thumb It is so nice to have nice people like you out there to redirect confuzled rookies like myself. Have a great day!
~:whistle: Gingertrees