Help with VBA code to validate form controls

[abette]

Hi,
I have a form control that I would like to validate to see if the value entered by the user exists on a SQL table. I tried using a macro but it's not working and I know it's probably better to do using VBA - something I am not good at! I can code basic routines but nothing crazy.

1. I have a Form and need to validate if the Member ID entered by the user exists on a Member table. If it doesn't exist, I would like to display an error message. The table name is dbo_tdMember and resides on a SQL Server

2. Next, If the user enters an Authorization Number I need to validate the following

• Does the Authorization Number belong to the Member ID? (dbo_tdMember table MEMBERID and dbo_Authorization MemberID)
• Does the Authorization Number exist on the Auth Table?
• Does the Authorization Date fall within the Date of Service From and Date of Service Start dates?

The Authorization data is on dbo_Authorization and the fields referenced are AuthNum, authStart and authEnd

I appreciate any help you may provide since I am really an amateur with VBA.
Thank you,
Ann Marie

 

[theDBguy]

Hi Ann Marie,

We could try taking small steps...

In the BeforeUpdate event of the Member ID, you could try something like:

If DCount("*", "dbo_tdMember", "[Member ID]=" & Nz(Me.[Member ID],0))>0 Then
    MsgBox "Member ID exists!"
Else
    MsgBox "New member."
End If

I was assuming the name of the field is [Member ID] (with a space) and that it's of a Number data type.

Hope that helps...

 

[abette]

Hi Ann Marie,

We could try taking small steps...

In the BeforeUpdate event of the Member ID, you could try something like:

If DCount("*", "dbo_tdMember", "[Member ID]=" & Nz(Me.[Member ID],0))>0 Then
    MsgBox "Member ID exists!"
Else
    MsgBox "New member."
End If

I was assuming the name of the field is [Member ID] (with a space) and that it's of a Number data type.

Hope that helps...

Hi there ,
Thank you for your quick reply.
The field is actually spelled MEMBERID on the table and yes, it's a number field.
This could wouldn't go in the AfterUpdate event property?
Also, what is DCount? is it a built in function?
Do I just copy this into the VBA module attached to the property?

 

[abette]

Hi Ann Marie,

We could try taking small steps...

In the BeforeUpdate event of the Member ID, you could try something like:

If DCount("*", "dbo_tdMember", "[Member ID]=" & Nz(Me.[Member ID],0))>0 Then
    MsgBox "Member ID exists!"
Else
    MsgBox "New member."
End If

I was assuming the name of the field is [Member ID] (with a space) and that it's of a Number data type.

Hope that helps...

This is what I put in the BeforeUpdate EventProperty attached to the Member ID field control of the form:

Private Sub MemberID_BeforeUpdate(Cancel As Integer)
If DCount("*", "dbo_tdMember", "[MEMBERID]=" & Nz(Me.[MemberID], 0)) > 0 Then
MsgBox "Member ID exists!"
Else
MsgBox "New member."
End If
End Sub

MEMBERID = table field
MemberID = Form control field

Is this correct?

 

[abette]

This is what I put in the BeforeUpdate EventProperty attached to the Member ID field control of the form:

Private Sub MemberID_BeforeUpdate(Cancel As Integer)
If DCount("*", "dbo_tdMember", "[MEMBERID]=" & Nz(Me.[MemberID], 0)) > 0 Then
MsgBox "Member ID exists!"
Else
MsgBox "New member."
End If
End Sub

MEMBERID = table field
MemberID = Form control field

Is this correct?

I corrected the field and control name references so the code reads like this now:
Private Sub MemberID_BeforeUpdate(Cancel As Integer)
If DCount("*", "dbo_tdMember", "[MemberID]=" & Nz(Me.[MemberID], 0)) > 0 Then
MsgBox "Member ID exists!"
Else
MsgBox "Member ID doesn't exist in CYBER."
End If
End Sub

It seems to be working

 

[theDBguy]

It seems to be working

Great! That's step 1. Let us know what happens when you take step 2 and the rest of the way.

 

[abette]

Great! That's step 1. Let us know what happens when you take step 2 and the rest of the way.

For the Authorization Number validation how do I do it if the data type is short text?

 

[theDBguy]

For the Authorization Number validation how do I do it if the data type is short text?

We add the String delimiters. Like:

DCount("*", "TableName", "TextFieldName='" & Me.TextboxName & "'")

 

[abette]

For the Authorization Number validation how do I do it if the data type is short text?

Private Sub AuthNumber_BeforeUpdate(Cancel As Integer)
If DCount("*", "dbo_tdAuthorization", "[AuthNum]=" & Nz(Me.[AuthNumber], 0)) > 0 Then
MsgBox "Auth Num exists!"
Else
MsgBox "Auth Number doesn't exist in CYBER."
End If
End Sub

This isn't working for the Auth Number because the data type = short text
Is there a way to check for not null?
Also, in place of the ) should it read ""?

 

[theDBguy]

Private Sub AuthNumber_BeforeUpdate(Cancel As Integer)
If DCount("*", "dbo_tdAuthorization", "[AuthNum]=" & Nz(Me.[AuthNumber], 0)) > 0 Then
MsgBox "Auth Num exists!"
Else
MsgBox "Auth Number doesn't exist in CYBER."
End If
End Sub

This isn't working for the Auth Number because the data type = short text
Is there a way to check for not null?
Also, in place of the ) should it read ""?

I guess you missed my response above. Please try that and let us know...

 

[abette]

We add the String delimiters. Like:

DCount("*", "TableName", "TextFieldName='" & Me.TextboxName & "'")

My apologies for not seeing this sooner.
I tried this and it's bombing
Private Sub AuthNumber_BeforeUpdate(Cancel As Integer)
If DCount("*", "dbo_tdAuthorization", "[AuthNum]='" & Me.[AuthNumber] & "'") Then
MsgBox "Auth Num exists!"
Else
MsgBox "Auth Number doesn't exist in CYBER."
End If
End Sub

 

[theDBguy]

My apologies for not seeing this sooner.
I tried this and it's bombing
Private Sub AuthNumber_BeforeUpdate(Cancel As Integer)
If DCount("*", "dbo_tdAuthorization", "[AuthNum]='" & Me.[AuthNumber] & "'") Then
MsgBox "Auth Num exists!"
Else
MsgBox "Auth Number doesn't exist in CYBER."
End If
End Sub

Are you getting an error message? Are you comparing exact strings or only partial?

 

[abette]

My apologies for not seeing this sooner.
I tried this and it's bombing
Private Sub AuthNumber_BeforeUpdate(Cancel As Integer)
If DCount("*", "dbo_tdAuthorization", "[AuthNum]='" & Me.[AuthNumber] & "'") Then
MsgBox "Auth Num exists!"
Else
MsgBox "Auth Number doesn't exist in CYBER."
End If
End Sub

OK So far I have this:
Option Compare Database

Private Sub AuthNumber_BeforeUpdate(Cancel As Integer)
If DCount("*", "dbo_tdAuthorization", "[AuthNum]='" & Me.[AuthNumber] & "'") > 0 Then
MsgBox "Valid Auth Num entered."
Else
MsgBox "Auth Num doesn't exist in CYBER."
End If
End Sub

Private Sub MemberID_BeforeUpdate(Cancel As Integer)
If DCount("*", "dbo_tdMember", "[MEMBERID]=" & Nz(Me.[MemberID], 0)) > 0 Then
MsgBox "Valid Member ID entered."
Else
MsgBox "Member ID doesn't exist in CYBER."
End If
End Sub

Now for the AuthNumber I need to add more logic so it validates that the Auth number belongs to the Member ID (dbo_tdAuthorization) and that the authStart and authEnd dates on dbo_tdAuthorization fall within the Date Of Service From and Date of Service To dates entered on my Form.

 

[conception_native_0123]

Now for the AuthNumber I need to add more logic so it validates that the Auth number belongs to the Member ID (dbo_tdAuthorization) and that the authStart and authEnd dates on dbo_tdAuthorization fall within the Date Of Service From and Date of Service To dates entered on my Form.

abette,

would you be interested in looping the controls to make things easier?

 

[abette]

abette,

would you be interested in looping the controls to make things easier?

Looping the controls? I honestly do not know how to do this, LOL

Private Sub AuthNumber_BeforeUpdate(Cancel As Integer)
If DCount("*", "dbo_tdAuthorization", "[AuthNum]='" & Me.[AuthNumber] & "'") > 0 Then
' MsgBox "Valid Auth Num entered."
If DCount("*", "dbo_tdAuthorization", "[MemberID]=" & Nz(Me.[MemberID], 0)) > 0 Then

Else
MsgBox "Auth Num not associated with Member ID."
End If
Else
MsgBox "Auth Num doesn't exist in CYBER."
End If
End Sub

Private Sub MemberID_BeforeUpdate(Cancel As Integer)
If DCount("*", "dbo_tdMember", "[MEMBERID]=" & Nz(Me.[MemberID], 0)) > 0 Then
MsgBox "Valid Member ID entered."
Else
MsgBox "Member ID doesn't exist in CYBER."
End If
End Sub

Should the Member ID Control be moved before the Auth Number control on the form?

 

[abette]

abette,

would you be interested in looping the controls to make things easier?

I am wondering if this validation should be attached to the Save Button? When you attach to the individual controls, it looks like the order of the controls will impact how things are being validated.

 

[conception_native_0123]

I am wondering if this validation should be attached to the Save Button? When you attach to the individual controls, it looks like the order of the controls will impact how things are being validated.

abette,

what I meant when I said to loop controls is that your table fields have to be patternized somewhat. here is an example of what you could do:

Public Function validate_controls(arrCtrls() As String, fName As String) As Boolean
Dim c As Control
   For Each c In Forms(fName).controls
      If TypeOf c Is TextBox Then
         If c = "" Or IsNull(c) Then
            validate_controls = False
            Exit Function
         End If
      End If
   Next c
validate_controls = True
End Function

that is code that I wrote for another client quite a ways back. but you had mentioned this:

2. Next, If the user enters an Authorization Number I need to validate the following

• Does the Authorization Number belong to the Member ID? (dbo_tdMember table MEMBERID and dbo_Authorization MemberID)
• Does the Authorization Number exist on the Auth Table?
• Does the Authorization Date fall within the Date of Service From and Date of Service Start dates?

so you would probably have to include a CASE statement in the middle of that and change a few lines of code. so, what is the name of all the controls and what are the names of all the fields that are associated with those controls please? but I've kind of dived in here without asking dbGuy so sorry if it made things more complicated.

 

[theDBguy]

Now for the AuthNumber I need to add more logic so it validates that the Auth number belongs to the Member ID

How many AuthNumbers can each MemberID have?

I am wondering if this validation should be attached to the Save Button? When you attach to the individual controls, it looks like the order of the controls will impact how things are being validated.

That would depend on your requirements and the user experience you want to have. You can certainly wait until the user has finished filling out the entire form and then validate each entry all at once, if that's what you prefer. If so, you would move all the code in the form's BeforeUpdate event (or use your Save button, but I prefer the latter).

 

[abette]

How many AuthNumbers can each MemberID have?


That would depend on your requirements and the user experience you want to have. You can certainly wait until the user has finished filling out the entire form and then validate each entry all at once, if that's what you prefer. If so, you would move all the code in the form's BeforeUpdate event (or use your Save button, but I prefer the latter).

Yes, I will probably move validation but I am lost with how to continue the validation of the Auth number as I mentioned in my original post.
Right now my code validates to see if the Member ID and the Auth Number exist on their respective tables.

How to I validate the Member ID entered and the Auth Number entered both exist on the Authorization table in 1 record? So I want to verify the AuthNum belongs to the correct Member ID or the Member ID belongs to the AuthNum

Also, I need to validate the Date of Service From (Form Control) is > AuthStart (dbo_tdAuthorization) and < AuthEnd (dbo_tdAuthorization)
AND
Date of Service To (Form Control) is < AuthStart (dbo_tdAuthorization) and > AuthEnd (dbo_tdAuthorization)

 

[theDBguy]

Yes, I will probably move validation but I am lost with how to continue the validation of the Auth number as I mentioned in my original post.
Right now my code validates to see if the Member ID and the Auth Number exist on their respective tables.

How to I validate the Member ID entered and the Auth Number entered both exist on the Authorization table in 1 record? So I want to verify the AuthNum belongs to the correct Member ID or the Member ID belongs to the AuthNum

Also, I need to validate the Date of Service From (Form Control) is > AuthStart (dbo_tdAuthorization) and < AuthEnd (dbo_tdAuthorization)
AND
Date of Service To (Form Control) is < AuthStart (dbo_tdAuthorization) and > AuthEnd (dbo_tdAuthorization)

Hi. But you didn't answer my question. Can each member id have more than one authorization number?