Problem with an IF Then Else in VBA

[Eric the Viking]

I have some code for a button on click event to open a form and select the record which corresponds to a value in an unbound text box. The code is:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmOpenPatientRecord"

If Len(Me![Text27] = 10) Then
stLinkCriteria = "[NHSnumber]=" & "'" & Me![Text27] & "'"

ElseIf Len(Me![Text27] < 10) Then
stLinkCriteria = "[PatientNumber]=" & "'" & Me![Text27] & "'"

End If
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

This works fine when I put in a 10 digit NHS number but opens a blank record when I enter a four digit or six character/digit PatientNumber. Both patient number and NHS number are text fields in the underlying table.

I would really appreciate advice on where I am going wrong.

Many thanks in advance.

Eric

 

[CJ_London]

There doesn't on the face of it appear to be anything wrong with your code so this implies there is something wrong with your data.

1 - does the patientNumber actually exist in your table - i.e. the form is not finding a match
2 - are you using lookups in your patient table - so you are searching for a text match when it may well be a numeric value

 

[namliam]

Yes, mostlikely a data problem
Are you working with preceding leading zero's in your table?
Can your table have spaces in the "number"?

 

[Eric the Viking]

Hi CJ thanks for your reply.
The numbers are there in the table:

PatientNumber
4380
4994
5004
506782
974633
AT4437
AW1907
AX1983
GK4088

the variants in length and construction (ie numeric/alphanumeric) being outside my control. The Table field is a text field of standard 50 character length with no input masks or such like. I dont use lookups in tables so this should not be a source of the problem.

Its all a bit perplexing......

 

[Eric the Viking]

it is possible for there to be a preceding zero in 6 digit numbers but all that is there in the test version is what Ive just posted in reply to CJ. Spaces in the numbers are not allowed

 

[jdraw]

Just to confirm, PatientNumber and NHSnumber are actually text fields --not numeric, right?

If these are text strings, then the leading 0 should occupy a position.

 

[Eric the Viking]

Yes both are text fields PatientNumber in a six digit format could have 0 as its first digit but all other formats will not start with 0 and in my test version no numbers start with 0.

NHSnumber is also a text field and will never start with 0 but will always be 10 digits long. Its set as text because of the way the number is validated elsewhere.

 

[jdraw]

For testing/debugging I would add a line

Debug.Print "Text27 is " & Me!Text27 & " and the length of text27 is " & Len(Me!Text27)
immediately after

stDocName = "frmOpenPatientRecord"

to see the actual values Access/vba code is using.

too see

 

[Eric the Viking]

That returned:

Text27 isaw1907and the length of text 27 is6

in the immediate pane which seems correct although the correct record doesnt open just a blank record...

 

[jdraw]

Can you post a zipped copy of your database -- remove anything confidential first?

 

[Eric the Viking]

Front and back end attached

Cheers

 

[pr2-eugin]

See if this provides any insight.. Why does my form go completely blank?

Try some debugging, DCount/ DLookUp from the immediate window for the condition.. The problem might be as simple as the (text) ID is not exactly the same.

Or as JD suggests, upload a Stripped DB..

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[Eric the Viking]

the form with the issue is frmPatientNumberDialogBox - enter NHS number ie 10 digits and it works fine but not for the PatientNumber

 

[pr2-eugin]

Be careful with your parentheses.. You had your condition check as, Len(Me![Text27] = 10) which will be true irrespective of the length..

It should have read, Len(Me.Text27) = 10

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmOpenPatientRecord"
    Debug.Print "Text27 is " & Me.Text27 & " and the length of text 27 is " & Len(Me.Text27)
    
    If [COLOR=Red][B]Len(Me.Text27) = 10[/B][/COLOR] Then
        stLinkCriteria = "NHSnumber='" & Me.Text27 & "'"
    Else[COLOR=Red][/COLOR]
        stLinkCriteria = "PatientNumber='" & Me.Text27 & "'"
    End If
    
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "frmPatientNumberDialogBox", acSaveNo
    
Exit_Command25_Click:
        Exit Sub
Err_Command25_Click:
        MsgBox Err.Description
        Resume Exit_Command25_Click
End Sub

 

[Eric the Viking]

Thank you all so much for your attention and help with this - as is so often the case the longer you sit staring at something the less likely you are to spot the error.
Once again many thanks rest assured your help will help us, a group of busy gyn oncologists, in the UK make sure nothing slips through the net.

 

[pr2-eugin]

You are welcome, glad we could be of help Good luck.

 

[jdraw]

The syntax of the if was incorrect (bracket in wrong place).

Corrected code

If Len(Me![Text27][COLOR="Red"][B]) [/B][/COLOR]= 10 Then
    stLinkCriteria = "[NHSnumber]=" & "'" & Me![Text27] & "'"

ElseIf Len(Me![Text27][COLOR="Red"][B])[/B][/COLOR] < 10 Then
    stLinkCriteria = "[PatientNumber]=" & "'" & Me![Text27] & "'"

End If

But I'm not sure why your forms are opening and closing (or perhaps not opening)

Perhaps you can describe the logic of the forms once you select say AW4000


OOOOps: just saw recent posts after posting.. I was having lunch and just got back to it.