Results 1 to 8 of 8
  1. #1
    nixonshaun is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2020
    Posts
    4

    parameter query validation of field

    I have a database where a client can fill-out a form entering their username and date and record their visit to our career center.


    Demographic information is stored in a separate table, is has FirstName, LastName, DOB, Last4 of Social, email, Address, and other contact info.

    There is a parameter query setup, it has the following fields from two tables.
    from table titled VisitDates it has UserName
    From table titled ClientInformation it has First Name, Last Name, Birthdate
    The criteria for LastName is [Enter Last Name] and the criteria for Birthdate is [Enter Date of Birth]
    A form has been generated based on this query.
    on opening the form, Access will prompt you to enter your Last Name and Date of Birth, if you information is found in the table the form will show Username, First, Last and DOB. I use this for people who have forgotten their username (last name + last 4 of social security number)

    The problem is if i enter a last name and date of birth that is not found in the database, the system opens a form and rightfully displays no information. IS there a way to add some validation to the criteria so that if the last name and date of birth is not found in the table "ClientInformation" then it would msgbox an error message.

    Not sure if this needs to be VB on the form fields or if this can be done in the query criteria.
    any help would be appreicated.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Needs to be in VBA in the form, you can use a dCount to do it:

    Code:
    if dCount("*","[ClientInformation]","[Last Name]='" & Me.txtLastName & "' AND [Birthdate] = #" & Me.txtBirthDate & "#")>0 then
        'open your form here
    Else
        Msgbox "No entries found"......
    End If
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    nixonshaun is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2020
    Posts
    4
    Thanks Vlad,
    I tried this. I created a form with two fields from existing tables: LastName and BirthDate and added a button
    I used this code on the button / onClick

    Private Sub Command2_Click()
    If DCount("*", "[ClientInformation]", "[LastName]='" & Me.txtLastName & "' AND [BirthDate] = #" & Me.txtBirthDate & "#") > 0 Then
    DoCmd.OpenForm "UserNameLookup"
    Else
    MsgBox "Your Name is Not Found in the Database"
    End If
    End Sub

    In the form i enter the last name and date of birth and click the button, it should open the second form with the UserName field that corresponds
    It comes up with a compile error on Me.txtLastName method or data method not found and goes back to the VB screen.
    when I exit this screen, there is a pop up box by the form stating i need to enter a UserName. UserName exists as a relationship between various tables.
    not sure if this impacts anything. or if i need to set this up a different way.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You need to replace txtLastName and txtBirthDate with the actual names of the two controls (text boxes) you have on your form - they might be LastName and Birthdate, you need the control name not the field (control source) name.
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    nixonshaun is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2020
    Posts
    4
    Quote Originally Posted by Gicu View Post
    You need to replace txtLastName and txtBirthDate with the actual names of the two controls (text boxes) you have on your form - they might be LastName and Birthdate, you need the control name not the field (control source) name.
    Vlad, If i was using one form, made from the parameter query. Would i add this as on load event, so it would run the Dcount on form opening?
    I did try this by removing txt from the VB i posted above so it was just the control name. It does seem to work with Lastname and DOB's that are in the database, but if i enter a name that I know is not in the database, it gives me a runtime 2427 error you entered an expression that has no value.
    any thoughts on this.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can I suggest you create a small popup form with the two text boxes and a command button where you put the VBA code. Then remove the parameters from the query and replace them with references to your popup form. Have a look at the attached sample.
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    nixonshaun is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2020
    Posts
    4
    Thank you Vlad, your attached tutorial file was excellent.
    I did as you said, pop up form with 2 unbound fields and a command button with the VB code, and then updated the query with the criteria as follows "Forms!NameOfMenuForm!NameOfUnboundField" and everything works great.
    Thanks for being patient and helping me figure this out.
    i guess we can mark this as solved.
    -shaun

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Glad to hear, good luck with your project!

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-11-2018, 07:19 AM
  2. Concatenate field turning into a parameter query
    By Access_Novice in forum Queries
    Replies: 1
    Last Post: 09-12-2014, 08:32 PM
  3. use parameter in field name of select query
    By focosi in forum Queries
    Replies: 2
    Last Post: 02-11-2012, 10:34 AM
  4. Parameter query on memo field?
    By UnfinishedStory in forum Queries
    Replies: 1
    Last Post: 10-27-2011, 11:05 AM
  5. Parameter Query on a calculated field
    By l3111 in forum Queries
    Replies: 5
    Last Post: 10-12-2011, 02:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums