error in code to automatically clear fields between entries (1 Viewer)

[KateM]

Hi All

I'm still learning so please accept my apologies for what is probably a beginner's mistake.

I have made a form with a subform to search for existing records based on inputting a postcode then selecting the relevant address from the subform which then fills the form with details of that chosen record ready to issue a further package linked to that original record.

To prevent errors and confusion by the users I coded the following:
Private Sub CmdFindAddress_Click()
If IsNull(Me.TxtPostcode) Then
MsgBox "You need a Postcode to use this button. Please type one in."
Me.TxtPostcode.SetFocus
Exit Sub
End If
If Len(Me.TxtPostcode) <> 8 Then
MsgBox "The postcode should be 8 characters long. Please type it in again."
Me.TxtPostcode.SetFocus
Exit Sub
End If
If DCount("Property_id", "dbo_Property", "[Postcode] = Forms!frmRetestSearch.TxtPostcode") = 0 Then
MsgBox "Error - there are no records with the postcode you have entered. Please check it and type it in again."
Me.TxtPostcode.SetFocus
Exit Sub
End If
Me.TxtPostcode = UCase(Me.TxtPostcode)
Me.NumOrigCrn = Null

'Where the postcode entered does not exist in records or if there is a mistake typing in the postcode, below will remove residual record details from previous postcode ready for a new postcode search:
For Each ctlCurr In Me.Controls
If ctlCurr.Tag = "clear" Then
ctlCurr = Null
End If
Next ctlCurr

Me.Refresh
End Sub


.........

The problem is that if I test the system by inputting a postcode where I know there are no records, the CtlCurr is highlighted and the error message is:

Compile error: variable not defined
..........

This does not happen if I have already selected a postcode so the record fields are filled and then change the postcode - so the record fields are cleared.

Previously, entering a postcode which did not match existing records generated the "there are no records...." error message instead.

Would you please advise - how do I define CtlCurr?

Thank you !

KateM

 

[JHB]

It can be because ctlCurr isn't declared, (Dim ctlCurr As Control), but also because Null is unknown.
What line exactly is highlighted?

 

[KateM]

Hi JHB

Thanks for your reply.

It was the CtlCurr in the "For Each CtlCurr In Me.Controls" line which was highlighted.

I've tried your suggestion (putting the "Dim CtlCurr as Control" line above the "For Each" line) and that stops the compile error, but it no longer clears the filled fields - so the CtlCurr code doesn't work now.

Do you have any other suggestions, please?
Thanks !

KateM

 

[JHB]

... so the CtlCurr code doesn't work now.

I think the code works ok, it is the criteria in the If statement there doesn't come true. so are you sure you have the text Clear ctlCurr Tag's property, like shown below?

 

[KateM]

Hi JHB

I've checked, all the text boxes I want to clear have a tag "clear".

I adapted the code from code I found online:

Dim ctlCurr As ControlDim booCondition As Boolean
For Each ctlCurr In Me.Controls If ctlCurr.Tag = "Group1" Then
ctlCurr.Visible = booCondition
End If
Next ctlCurr
(this makes the text boxes and labels become invisible....)

.................

As my code works fine if the text boxes are filled, I presumably need to add code to specify that if the text boxes are empty, do not run the code? When I try to add a second "If" statement to the code, it fails. Is there another way to do this?



You said there might be a problem with "Null" - could you suggest what other term I could use?

Thanks

KateM

 

[JHB]

Dim ctlCurr As ControlDim booCondition As Boolean
For Each ctlCurr In Me.Controls If ctlCurr.Tag = "Group1" Then
ctlCurr.Visible = booCondition
End If
Next ctlCurr
(this makes the text boxes and labels become invisible....)

Do you use the above code now, it is not what you first showed!
The code above search for ctlCurr.Tag = "Group1" not Clear.

Post a stripped version of your database with some sample data, (zip it) + information about which form you have problem with.

 

[KateM]

I hope I've successfully uploaded a zipped stripped-out version of of my database and form.

The problem is as follows:

If, for example you type in postcode ABCDEFG1 and click on the "click here to select from current records" button, it will display records of previous tests. You select a record in the subform and the system will fill in the orig_id and the house_type, etc.

If you mistakenly think that your postcode is actually ABCDEFG3, you type that in and press the "click here.." button, but the previous details from the previous test are still showing.. I want the fields to all clear between each postcode search.

If you then realise that your postcode is actually ABCDEFG2 and change the postcode to ABCDEFG2, the clear all details code works at that point.


I hope this illustrates my problem and that you can help!

Thanks again

KateM

 

[Mihail]

Your problem is red:

Private Sub CmdFindAddress_Click()

If IsNull(Me.TxtPostcode) Then
        MsgBox "You need a Postcode to use this button.  Please type one in."
        Me.TxtPostcode.SetFocus
Exit Sub
End If

If Len(Me.TxtPostcode) <> 8 Then
        MsgBox "The postcode should be 8 characters long.  Please type it in again."
        Me.TxtPostcode.SetFocus
Exit Sub
End If

If DCount("Property_id", "Table1", "[Postcode] = Forms!frmSample.TxtPostcode") = 0 Then
MsgBox "Error - there are no records with the postcode you have entered.  Please check it and type it in again."
Me.TxtPostcode.SetFocus

[COLOR=Red][B]Exit Sub[/B][/COLOR]
End If

Me.TxtPostcode = UCase(Me.TxtPostcode)

Me.NumOrigCrn = Null



'Where the postcode entered does not exist in records or if there is a mistake typing in the postcode, below will remove residual record details from previous postcode ready for a new postcode search:
Dim CtlCurr As Control
For Each CtlCurr In Me.Controls
    If CtlCurr.Tag = "clear" Then
        CtlCurr = Null
    End If
Next CtlCurr


Me.Refresh

End Sub

 

[Mihail]

While now should be a breeze for you to continue (I hope), I have a question for you:
Why you don't use a combo box instead that damned text box where you enter the code ?

If will change to combo, manage the row source to show existing codes, and set the Limit To List property to YES.
The user will select from the combo a certain code then use the After Update event for the combo to trigger the rest of code. No more need for click here to select from current records button.
This way you will be sure that the code exist and there are no more reasons for other tests.

 

[KateM]

Hi Mihail

Thanks for your advice - removing the "exit sub" works.

I can't use a combo box for the postcode as the client could enter any of several milllion UK postcodes on the real version of my form....

We have some 8 million existing records and need to link a new request for our service to existing records so we can track individual clients and the work we have done for them.

Seasons Greetings.

Kind regards

KateM