?
Solved

prefill a field with parameter

Posted on 2005-04-29
19
Medium Priority
?
339 Views
Last Modified: 2010-10-28
Hello,

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?
0
Comment
Question by:onestopfinancial
  • 8
  • 8
16 Comments
 
LVL 3

Expert Comment

by:eddiej
ID: 13898010
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 _
         then
                me.ss# = txtSearchSS#
               
end if



0
 

Author Comment

by:onestopfinancial
ID: 13908537
what is txtSearchSS# ??
0
 
LVL 3

Expert Comment

by:eddiej
ID: 13913240
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

edidej

0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Author Comment

by:onestopfinancial
ID: 13913426
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.
0
 
LVL 3

Expert Comment

by:eddiej
ID: 13916782
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.
eddiej
0
 

Author Comment

by:onestopfinancial
ID: 13919294
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
0
 
LVL 3

Expert Comment

by:eddiej
ID: 13921951
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.
eddiej

0
 

Author Comment

by:onestopfinancial
ID: 13922154
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 question...in 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.
0
 
LVL 3

Expert Comment

by:eddiej
ID: 13926593
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 <> "" _
    Then
        If Me.OpenArgs = "fax" _
            Then
                '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" _
                Then
                '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 & ")));"
            Else
                '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
    Else
        '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 _
    Then
        '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
eddiej




0
 

Author Comment

by:onestopfinancial
ID: 13936368
eddiej....

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.

0
 

Author Comment

by:onestopfinancial
ID: 13938462
eddiej....

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# ?
0
 

Author Comment

by:onestopfinancial
ID: 13940239
do i need to remove the control source of the controls on my form since the form is unbound? im confused
0
 
LVL 3

Expert Comment

by:eddiej
ID: 13940483
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.

eddiej
0
 

Author Comment

by:onestopfinancial
ID: 13945221
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 field....so 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
0
 
LVL 3

Expert Comment

by:eddiej
ID: 13948358

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.
0
 
LVL 3

Accepted Solution

by:
eddiej earned 2000 total points
ID: 13948411
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 & ")));
docmd.applyfilter

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 & "))"
docmd.applyfilter

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.

eddiej

0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question