VBA and Form help

[harvy28]

I have a button on a form called redeem which when clicked should only bring up another form (new redemptions) if the member has enough points. It's like having a loyalty scheme where the user can only redeem points for a gift if they have enough points. This is what has been done so far:


Private Sub Redeem_Click()
'Check to see if the user has enough points to redeem anything at all
If Me.PointBalance.Value > "149" Then
Me.PointBalance.Value = Me.PointBalance.Value

On Error GoTo Err_Redeem_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmNewMemberRedemptions"

stLinkCriteria = "[CardNumber]=" & Me![CardNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Redeem_Click:
Exit Sub
Err_Redeem_Click:
MsgBox Err.Description
Resume Exit_Redeem_Click

Else
MsgBox "You do not have enough points to redeem any gift!", vbOKOnly, "Llandaff Hotels"
End If

End Sub

 

[Dennisk]

So, what is the problem?

 

[missinglinq]

Welcome to AWF! When people come this type of site the point is to ask for help with a specific problem/question they have. You've done neither here. At a glance, there are several things wrong. The order of events in the sub should be:

1. The first line below the sub header needs to be the On Error statement.
2. Next comes your Dim statements
3. Your assignment statement (such as stDocName =) can come next, if appropriate
4. Now place the actual code of your sub
5. After the actual code, place the Exit and Err lines

Private Sub Redeem_Click()
On Error GoTo Err_Redeem_Click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNewMemberRedemptions"

'Check to see if the user has enough points to redeem anything at all
If Me.PointBalance.Value > "149" Then
  Me.PointBalance.Value = Me.PointBalance.Value
  stLinkCriteria = "[CardNumber]=" & Me![CardNumber]
  DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
  MsgBox "You do not have enough points to redeem any gift!", vbOKOnly, "Llandaff Hotels"
End If

Exit_Redeem_Click:
Exit Sub

Err_Redeem_Click:
MsgBox Err.Description
Resume Exit_Redeem_Click

End Sub

Now, as to your actual code.

Since PointBalance appears to a numeric value, you need to lose the quotes around 149.

Next, there's no point to this statement:

Me.PointBalance.Value = Me.PointBalance.Value

so we'll drop it.

Lastly, is the question of the datatype of [CardNumber]. Being an identification "number" it should be Text. Only "numbers" that are used for math should be defined as Numeric. There are several reasons for this, which I won't overwhelm you with here. The point is, your line

stLinkCriteria = "[CardNumber]=" & Me![CardNumber]

is the correct syntax if [CardNumber] is Numeric.

If [CardNumber] is Text, then

stLinkCriteria = "[CardNumber] = '" & Me.CardNumber & "'"

would be the syntax.

Taking in mind the above questions about datatypes, the final code should look something like:

Private Sub Redeem_Click()
On Error GoTo Err_Redeem_Click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNewMemberRedemptions"

'Check to see if the user has enough points to redeem anything at all
If Me.PointBalance.Value > 149 Then
  stLinkCriteria = "[CardNumber]=" & Me![CardNumber]
  DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
  MsgBox "You do not have enough points to redeem any gift!", vbOKOnly, "Llandaff Hotels"
End If

Exit_Redeem_Click:
Exit Sub

Err_Redeem_Click:
MsgBox Err.Description
Resume Exit_Redeem_Click

End Sub

Have a go at it now, and come back if you have problems.

Good luck!

Linq