Running a form from a query

[huv123]

Okay I set up a form whose record source is a query. When the participant attempts to open the form they are asked to produce their ID. This was done when creating the query. Under the column Participant ID, I put the statement "Please enter the last three digits of your participant ID" under the criteria row. When the form loads, they see their record and participant ID. They cannot close the form until they have answered all the questions and cannot access anyone elses record (I have removed record selectors, close button, navigation buttons etc) or change their ID number (this field is locked)


Right now if I put in a fake number or I dont put in any number at all the form still opens as 000 (the ID is 3 digits). How do I ensure that the participant is forced to put in the number and that the ID is present in the table at least? i.e. that there is a participant with that number in our system?

 

[mp9]

Rather than using a query parameter to gather your ID field, you need to design a simple second form that has a data entry field and OK/Cancel buttons, and use that to capture the ID. Then you can use VBA on the OK button to validate/check the ID entered, before passing it to your main form.

 

[huv123]

Can you give me an example of some code or point to some more info i.e. website where I can find out about this? I am good at altering code to fit my purposes but not great at coming up with from scratch.

 

[huv123]

Is the answer soimething like this?

Normally I open the form from a button on another form. Instead I will direct the button to open frmSearchForm.


Private Sub btnSearch_Click()
Dim ParticipantID As String
srce = Me.Source_Control (<-------What is this)

If Not IsNull(Me.txtSearch) Then

ParticipantID = Me.txtSearch

'Open Athens Questionnaire Form

DoCmd.OpenForm "frmAthensQuestionnaire", , , "[ParticipantID] = '" & ParticipantID & "'"

'Assign Source Control Data (<---- No sure what this does)

Forms![frmSearchForm].[Source_Control] = srce

'Close Search Form

DoCmd.Close acForm, "frmCustomer_Search", acSaveNo

Else if Forms!frmAthensQuestionnaire.Recordset.RecordCount = 0 Then

MsgBox "No records were found"

DoCmd. CloseForm "frmAthensQuestionnaire"

End If

End sub

 

[mp9]

From your code, I'm assuming a few things, but here's what I'd code on frmSearchForm:

Private Sub btnSearch_Click()
Dim ParticipantID As String

If IsNull(Me.txtSearch) Then
    Msgbox "Enter search criteria.",vbExclamation
    txtSearch.setFocus
Else
    ParticipantID = txtSearch
    If DCount("*","NameOfTheTableYourQuestionnaireIsBasedOnHere","ParticipantID = '" & ParticipantID & "'") >0 Then
        'Open Athens Questionnaire Form
        DoCmd.OpenForm  "frmAthensQuestionnaire", , , "[ParticipantID] = '" & ParticipantID & "'"
        DoCmd.Close acForm, "frmCustomer_Search", acSaveNo
    Else
        If MsgBox("No records were found. Try another search?",vbQuestion)=vbYes Then
            txtSearch.SetFocus
        Else
            DoCmd.CloseForm "frmCustomer_Search"
        End IF
    End If
End If

End sub

 

[huv123]

Thank you I will try and work with this