prefill a field with parameter


When I open the form, it prompts for ss#.  If the ss# exists in database, the form fields will display the record.  If the ss# is not in the database, I want the ss# field to be prefilled with the parameter value so that it doesn't need to be retyped.

Any ideas?
Who is Participating?
eddiejConnect With a Mentor Commented:
It seems as we might be changing our stragey again.
Since there is only 1 sql now you could set that to the recordsource without the where part.
this would open the form to show all records, if you let it. But then you could set the filter to find the record you want.
useing the example from above it would be something like this

Dim strSSNumber As string

strSSNumber = InputBox("What is the SS number?", "Input")
strSSNumber = Nz(Format(intSSNumber, "000-00-0000"), "")
me.filter = (((Format([nh.nhSS]," & Chr(34) & "000-00-0000" & Chr(34) & _
                    "))=" & strSSNumber & ")));

I should have asked before Do you want to store the ss# in the db formated or unformatted?
It looks like unformatted so you really don't need to format it in you filter

Dim strSSNumber As string

strSSNumber = InputBox("What is the SS number?", "Input")
strSSNumber = Nz(intSSNumber)
me.filter = "((nh.nhSS)= " & strSSNumber & "))"

I haven't used the format function much, so what is above I just copied from you.

Also you might not need the 2 buttons now unless you are going to change things on the form based on if it is phone or fax.
Using the open args is something I am also finding more and more useful as I progress.


One thing you could try is the .newrecord property of the form

I have something similar on one of my forms, it shows the current record if search text matches or a prefills info in the new record.

I put this in the oncurrent event of the form.

if me.newrecord = true _
       = txtSearchSS#
end if

onestopfinancialAuthor Commented:
what is txtSearchSS# ??
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

You said the form prompts for the ss# how does it do that?

Are you using a text box somewhere, an input prompt or something else?

txtSearchSS# was my guess at the name of your search text box


onestopfinancialAuthor Commented:
the form is based off a parameter query (ss# is the parameter)...i wanted to reference the parameter entry somehow so that the user doesn't need to type the ss# again if the parameter doesn't return a record.

if the ss# doesn't exist, the form opens with all fields blank....if ss# does exist, the form opens up with the corresponding record.  so....if the ss# entered in the parameter prompt does not exist in the database (meaning the fields will all be blank), i want the ss# field to be prefilled with what was entered in the parameter prompt so that my users don't need to write down or re-ask for the ss#.  understand??

i apologize about the lack of pertinent background info in my question.
No problem sometimes it takes me asking a few questions before I can answer yours.

I don't think that you can do that with a parameter query, but you can do it.

instead of having the forms record source that query make it unbound.

then in the onload or onopen events ask for the value with an input box.
Then put it back together with your query sql and set the recordsource. If it is a new record you can set the value of the text box that you want.

If you post the sql of the query that you are using I can write the code for this. If you don't know how to see the sql open the query go to view then sql.

I'll check back after work.
onestopfinancialAuthor Commented:
i actually use 2 different queries depending on how i interact with the employee (if i speak to them on the phone, or if i receive a fax) the difference is that the phone query joins an additional table "query" otherwise its the same query (i'm using one form for both purposes, but thats another story)

phone query SQL:
SELECT nh.nhID, nh.nhLocID, nh.nhFName, nh.nhLName, nh.nhSS, nh.nhDOB, nh.nhDOH, nh.nhDOF, nh.nhIsFaxOrPhoneReceived, nh.nhAddress1, nh.nhAddress2, nh.nhCity, nh.nhState, nh.nhZIP, nh.nhCounty, nh.nhJobCat, nh.nhWage, nh.nhAvgHrsWk, nh.nhWStatus, nh.nhOStatus, nh.nhEStatus, nh.nhTG, query.query1, query.query1CaseMgrName, query.query1CaseMgrPhone, query.query1CaseMgrAddress, query.query1CaseMgrCity, query.query1CaseMgrState, query.query1RefLtr, query.query1RefLtrOnPerson, query.query2, query.query2DocOnPerson, query.query3, query.query3DocOnPerson, query.query4, query.queryPrimRecName, query.queryPrimRecCity, query.queryPrimRecState, query.query5, query.query5DocOnPerson, query.query6, query.query6DocOnPerson, query.query7, query.query7ParoleOfficer, query.query7ParoleOfficerPhone, query.query7County, query.query7DateOfConv, query.query7DateOfRelease, query.query7ApproxIncome, query.query7DocOnPerson, query.query8, query.query8DocOnPerson, loc.erLocFacilityLegalName, loc.erLocTradeName, loc.erLocFID, loc.erLocAddress1, loc.erLocCity, loc.erLocState, loc.erLocZip, loc.erLocCounty
FROM (loc INNER JOIN nh ON loc.erLocID = nh.nhLocID) INNER JOIN query ON nh.nhID = query.queryNHID
WHERE (((Format([nh.nhSS],"000-00-0000"))=Format([Enter SS#],"000-00-0000")));

fax query SQL:
SELECT loc.erLocFacilityLegalName, loc.erLocFID, nh.nhLocID, loc.erLocAddress1, loc.erLocCity, loc.erLocState, loc.erLocZip, nh.nhID, nh.nhSS, nh.nhFName, nh.nhLName, nh.nhDOH, nh.nhDOF, nh.nhDOB, nh.nhWage, nh.nhJobCat, nh.nhDolSubmissionDate, nh.nhWStatus, nh.nhOStatus, nh.nhEStatus, nh.nhTG, nh.nhAddress1, nh.nhAddress2, nh.nhCity, nh.nhState, nh.nhZIP, nh.nhCounty, nh.nhAvgHrsWk
FROM loc INNER JOIN nh ON loc.erLocID = nh.nhLocID
WHERE (((Format([nh.nhSS],"000-00-0000"))=Format([Enter SS#],"000-00-0000")));

i understand the concept of your suggestion, but im just getting into the coding so any sample code you can give would be GREAT that way i can start to learn how to code SQL in VB
wow that is some sql string.

but now you make me ask another question.
How do you currently decide which sql to use phone or fax?
do you have different command buttons, and then set the recordsource for the form accordingly?
or something else.

I use as many of my forms as i can for multiple purposes, it makes things cleaner and there is less maintance.

And just a thought about your sql. It doesn't really matter, but if need all of the fields from a table or in this case a sub query you can click on the * in the query builder. This will shorten your sql a bit.

Going to dinner, I check back later.

onestopfinancialAuthor Commented:
true, i forgot about the * ..... however i have a table named query (not to confuse you but because it stores results of a questionnaire).   can i do a * for just the query table because i don't want to * the nh table?

phone and fax each have their own query and form.  each form is bound by its corresponding query.  if i receive a phone call i use the phone form/query; if i receive a fax i use the fax form/query.  this is in limbo now because im thinking of a way to use only one form for maintenance purposes as you suggest, so let's assume that there is only one form (the phone form).  

if i knew how to prompt for choices, i guess i could ask if this request is for a phone or fax, then i could enable/visble appropriate fields depending on the answer (using one query-based form)...for example if its a fax request i would want to store Date Of Fax, or vice-versa with Date Of Phone Call, the differences in fields is very minimal.

back to the original most cases i receive a fax and a phone call on the same person because they faxed over the info but wasnt able to get through to me on the phone then 2 days later they phone me again and get through (in essence i really only need a fax or phone call but both are in effect for control purposes)  also, i might get a phone call, then a fax 2 days later :)  therefore i need to determine if the employee is in the database yet to avoid duplicates, hence the SS# parameter query (easiest solution i could come up with at the time).  so, if the employee is NOT in the database yet, my users (me mainly) wouldnt mind not having to retype the SS# if the form comes up blank (meaning employee not in database) but if the employee IS in the database, the fields populate with the employee record so that i can fill in the missing blanks.

eddiej, i appreciate your patience and helpfullness !  im gonna up the points because this question got bigger.
A table named query nice. I like it.

below is some code to place in the on open part of the form
I changed the sql to include query.*

Dim intSSNumber As Integer
Dim strRecordSource As String

intSSNumber = InputBox("What is the SS number?", "Input")
intSSNumber = Nz(Format(intSSNumber, "000-00-0000"), "")
If Not IsNull(Me.OpenArgs) And intSSNumber <> "" _
        If Me.OpenArgs = "fax" _
                'The fax sql string
                strRecordSource = "SELECT loc.erLocFacilityLegalName, " & _
                    "loc.erLocFID, nh.nhLocID, loc.erLocAddress1, " & _
                    "loc.erLocCity, loc.erLocState, loc.erLocZip, " & _
                    "nh.nhID, nh.nhSS, nh.nhFName, nh.nhLName, nh.nhDOH, " & _
                    "nh.nhDOF, nh.nhDOB, nh.nhWage, nh.nhJobCat, " & _
                    "nh.nhDolSubmissionDate, nh.nhWStatus, nh.nhOStatus, " & _
                    "nh.nhEStatus, nh.nhTG, nh.nhAddress1, nh.nhAddress2, " & _
                    "nh.nhCity, nh.nhState, nh.nhZIP, nh.nhCounty, nh.nhAvgHrsWk " & _
                    "FROM loc INNER JOIN nh ON loc.erLocID = nh.nhLocID " & _
                    "WHERE (((Format([nh.nhSS]," & Chr(34) & "000-00-0000" & Chr(34) & _
                    "))=" & intSSNumber & ")));"
            ElseIf Me.OpenArgs = "phone" _
                'the call sql string
                strRecordSource = "SELECT nh.nhID, nh.nhLocID, nh.nhFName, nh.nhLName, nh.nhSS, " & _
                    "nh.nhDOB, nh.nhDOH, nh.nhDOF, nh.nhIsFaxOrPhoneReceived, " & _
                    "nh.nhAddress1, nh.nhAddress2, nh.nhCity, nh.nhState, nh.nhZIP, " & _
                    "nh.nhCounty, nh.nhJobCat, nh.nhWage, nh.nhAvgHrsWk, nh.nhWStatus, " & _
                    "nh.nhOStatus, nh.nhEStatus, nh.nhTG, query.*, loc.erLocFacilityLegalName, " & _
                    "Loc.erLocTradeName , Loc.erLocFID, Loc.erLo " & _
                    "cAddress1 , Loc.erLocCity, Loc.erLocState, Loc.erLocZip, Loc.erLocCounty " & _
                    "FROM (loc INNER JOIN nh ON loc.erLocID = nh.nhLocID) " & _
                    "INNER JOIN query ON nh.nhID = query.queryNHID " & _
                    "WHERE (((Format([nh.nhSS]," & Chr(34) & "000-00-0000" & Chr(34) & _
                    "))=" & intSSNumber & ")));"
                'nothing here now
                'if you get to this line the form was open with open args other than
                'fax or phone maybe an error msg box or something would work
        End If
        'if you are here there where no open args or no ss number was entered
        'maybe set record source to open all records
End If

If Me.NewRecord _
        'you are in a new record set the ss field to the text entered in the input box
        'you could also preset anything else that need for new records
        'including making fields visible, or enabled
        Me.txtSSNumber = intSSNumber
End If

to open the form you will use one of these in the onclick of a button or something
the fax, phone are the openargs for the form which you then check to see which sql to use to load the form.

DoCmd.OpenForm "frmYourFormName", , , , , , "FAX"
DoCmd.OpenForm "frmYourFormName", , , , , , "phone"

let me know if something doesn't make sense, or doesn't work

good luck

onestopfinancialAuthor Commented:

i created a form with 2 buttons (btnOpenFax and btnOpenPhone) and placed the docmd statements in the onclick events.

i have my SS# field as a Text data type, i noticed you were using Integer, so i changed your Dim statement to strSSNumber As String and changed the other intSSNumber references

when i click the fax button, the main form opens but all the text boxes have #Name? in them; for a SS# i know is in the database, and for a SS# that isnt in the database.

onestopfinancialAuthor Commented:

ive decided to take the low maintenance route and use only one form for fax and phone as you suggested....

i apologize if you spent alot of time on your last response

now that i dont need to determine which SQL to use, could i still use the Input() structure to prefill the SS# ?
onestopfinancialAuthor Commented:
do i need to remove the control source of the controls on my form since the form is unbound? im confused
Sorry, apparently I'm not much of an expert.

Don't delete the control sources for controls.

add this before the if new record section of the above code. and the form is now bound.
me.recordsource= strrecordsource

"now that i dont need to determine which SQL to use" ? is it now the same sql for both phone and fax?
or is the code above going to decide which sql to use.

onestopfinancialAuthor Commented:
aha, i had a feeling it had somethin to do with a missing record source.  

ok, now it will be the same SQL, so the code doesn't need to determine which SQL statement to use...i want to use the phone SQL statement from above...

note that my SS# field in my nh table is a Text shouldnt it be strSSNumber As String?

also, what is that Char(34) and _ underscores after each line in the SQL statement you wrote?

i really appreciate your assistance eddiej

chr(34) is means " when you are putting together a string and it needs to contain a string it can be confusing and a pain to use "'" blah blah "'"  quote, single quote, quote I find it much easier to use chr(34)

When assembling a long string like that above it is just messy to let it run on and on off the screen, and hard to read/troubleshoot " & _     quote ends the string, & Concantinates it with the next string, _ tell access to continue to the next line. Usually a good idea to put a space before the " in sql strings otherwise you will get errors.

the other way to do it is
strSql = "blah blah blah"
strsql = strsql & "more blah blah"
strsql = strsql & "even more blah"

both methods are simply putting the string together. I prefer the first it just looks cleaner to me

I have found that as I get more experiance with databases I use sql more and more in my code rather than in a saved query, so it is always good to know how to manipulate it.

strSSNumber is the way to go

glad to help, it is just hard to find time as I work a lot.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.