Running a form from a query issues... 2

[huv123]

Sorry I posted this in the Access Other forum but should have been posted here.

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?

 

[jrbarnett]

Use an input form to enter the number.
In this form, have some code to check whether this is valid or not.
If valid, open your editing form with this number passed across as a filter or WhereCondition.

In the Form_Open event of the editing form, have a check that if there are no matching records, display a suitable error message "No matching data" or similar and close the form or set Cancel=True to stop the form_open from completing.

John

 

[huv123]

Can someone please help me with the code? I am not familiar with how to do it.

The variable I want to search is called Participant ID. The form is called frmAthensQuestionnaire. If I created the popup form it would be called frmEnterParticipantID and the table that I am searching is called tblParticipantData/

 

[huv123]

..oops and if the participant ID that they are searching is not valid rather than opening the form can it please return an error message?

 

[huv123]

IS it something like this?

================

Private Sub btnSearch_Click()
Dim ParticipantID As String
srce = Me.Source_Control

If Not IsNull(Me.txtSearch) Then

ParticipantID = Me.txtSearch

'Open Athens Questionnaire Form

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

'Assign Source Control Data

Forms![frmSearch_Results].[Source_Control] = srce
'Close Search Form

DoCmd.Close acForm, "frmCustomer_Search", acSaveNo

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

MsgBox "No records were found"

DoCmd. CloseForm "frmAthensQuestionnaire"

End If

End sub

 

[huv123]

oops I think it should read

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

 

[HarleyQuinn]

Hi huv123,

I think you might have misunderstood the If..ElseIf construct slightly, as long as the txtSearch box isn't null then with your current code the will always open regardless of a record existing (did you try the code and see this behaviour?). You could try someothing like this (it's untested and I've just written it on the fly so may require a bit of a re-jig):

Private Sub btnSearch_Click()
Dim ParticipantID As String
srce = Me.Source_Control

If Trim(Me.txtSearch & " ") <> "" Then

	ParticipantID = Me.txtSearch

	'Open Athens Questionnaire Form

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

	'Assign Source Control Data

	Forms![frmSearch_Results].[Source_Control] = srce
	
	If Forms!frmAthensQuestionnaire.Recordset.RecordCount = 0 Then

		MsgBox "No records were found"

		DoCmd. CloseForm "frmAthensQuestionnaire"

	Else
		
		'Close Search Form

		DoCmd.Close acForm, "frmCustomer_Search", acSaveNo
	
	End If
	
Else

	MsgBox "Please enter a valid search number"

End If 

End sub

The construct I've used makes sure users have entered something in the search box and then checks records. As I say it's just quick and untested but should at the very least give you an idea as to where to go, also, indentation is your friend. It makes code much easier to read.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[huv123]

Hi Harley

I get an error message about method or data member not found. I think because in the original code all the text fields, form names etc were getting mixed up.

Here is the details.

My pop-up search form is called frmSearchForm

The text box where I want to enter the Participant ID (which is the primary key of the table underlying the form that I want to present the results in) is called txtSearch


THe table I want it to search is called tblParticipantData

and the form I want it to open the results in is called frmAthensQuestionnaire

The field that I want the txtSearch matched to in frmAthensQuestionnaire is called Participant ID.

 

[HarleyQuinn]

Where does it error? Have you tried modofying the code I gave you to fit with the information you've just given me?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[huv123]

It errors at srce = Me.Source_Control

or

'Assign Source Control Data

Forms![frmSearch_Results].[Source_Control] = srce


I have tried putting my own variables in, but I am uncertain what the above sentance means so I cant fix it Sorry I am so slow.

 

[HarleyQuinn]

Don't apologise, it's quite a steep learning curve at times!

What happens if you comment out the two offending lines you mentioned? Does it work then?

Cheers

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[huv123]

I tried this code

==============
Private Sub btnSearch_Click()
Dim txtSearch As String

If Trim(Me.txtSearch & " ") <> "" Then

ParticipantID = Me.txtSearch

'Open Athens Questionnaire Form

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


If Forms!frmAthensQuestionnaire.Recordset.RecordCount = 0 Then

MsgBox "No records were found"

DoCmd.CloseForm "frmAthensQuestionnaire"

Else

'Close Search Form

DoCmd.Close acForm, "frmCustomer_Search", acSaveNo

End If

Else

MsgBox "Please enter a valid search number"

End If

End Sub

===============


I get a compile error variable not defined at this line:
ParticipantID = Me.txtSearch


I can see what the code is doing but Im not sure of the minute details.

ALso Im not sure if this affects anything however the Participant stored in tblParticipantDetails is stored as a number in a text field not text.

 

[PHV]

Replace this:
Dim txtSearch As String
with this:
Dim ParticipantID As String

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[huv123]

THank. I will try this over the weekend.