Integers aren't working

M

MF Scheetz

I've got the following code:

Private Sub PO_AfterUpdate()
Dim cn As ADODB.Connection
Dim BArs As ADODB.Recordset
Dim Rqd, Qty, Iss, box As Integer

Set cn = CurrentProject.Connection
Set BArs = New ADODB.Recordset

BArs.Open "SELECT * FROM [Bird Assembly Table] WHERE [Assembly
TravelerID] = " & Me.PO & " AND [Part Number] = '" & Me.Drawing_Number & "'",
cn, adOpenDynamic, adLockOptimistic

If BArs.EOF Then
'condition 1
box = MsgBox("That Traveler ID doesn't exist.")
Me.PO = ""
Exit Sub
Else
BArs.MoveFirst
Rqd = Me.Qty
Qty = BArs.Fields("Quantity Per AV")
Iss = BArs.Fields("Issued")
If Qty = Iss Then
If Qty = 1 Then
'condition 2
box = MsgBox("A " & Me.Drawing_Number & " has already been
issued to " & Me.PO)
Me.Qty = 0
Me.PO = ""
Else
'condition 3
box = MsgBox(Iss & " " & Me.Drawing_Number & " have already
been issued to " & Me.PO)
Me.Qty = 0
Me.PO = ""
End If
ElseIf Qty <> Iss Then
If Iss = 0 Then
If Rqd > Qty Then
'condition 4
box = MsgBox("The quantity requested is more than
required by the traveler.")
Me.Qty = Qty
Me.Qty.SetFocus
ElseIf Rqd < Qty Then
'condition 5
box = MsgBox(Qty - Rqd & " more " & Me.Drawing_Number &
" will still need to be assigned to this traveler.")
End If
ElseIf Iss > 0 Then
If Rqd > Qty Then
'condition 6
box = MsgBox("The quantity requested is more than
required by the traveler.")
Me.Qty = Qty - Iss
Me.Qty.SetFocus
ElseIf Rqd < Qty Then
'condition 7
box = MsgBox(Qty - Rqd - Iss & " more " &
Me.Drawing_Number & " will still need to be assigned to this traveler.")
End If
End If
End If
End If

BArs.Close
cn.Close

Set BArs = Nothing
Set cn = Nothing
End Sub

During testing of the code, I set in the ADO table:
"Quantity Per AV" = 4
"Issued" = 0
In the form I set Qty to 1.

This should result in condition 5, but condition 4 occurs, incorrect, 1 is
not > 4.

If I change the form Qty to 5 then condition 4 occurs, this is correct, 5 > 4

Any value I place in the form's Qty field, brings condition 4.

Any suggestions?

Thanks,

-Matt
 
D

Douglas J. Steele

I don't know whether this is the cause of your problem, but

Dim Rqd, Qty, Iss, box As Integer

doesn't do what you probably think it does.

Access doesn't allow short circuiting in declarations, so of those four
variables, only box is an Integer: the other three are actually Variants.

To make all the variables Integers, you need to use

Dim Rqd As Integer, Qty As Integer, Iss As Integer, box As Integer

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MF Scheetz said:
I've got the following code:

Private Sub PO_AfterUpdate()
Dim cn As ADODB.Connection
Dim BArs As ADODB.Recordset
Dim Rqd, Qty, Iss, box As Integer

Set cn = CurrentProject.Connection
Set BArs = New ADODB.Recordset

BArs.Open "SELECT * FROM [Bird Assembly Table] WHERE [Assembly
TravelerID] = " & Me.PO & " AND [Part Number] = '" & Me.Drawing_Number &
"'",
cn, adOpenDynamic, adLockOptimistic

If BArs.EOF Then
'condition 1
box = MsgBox("That Traveler ID doesn't exist.")
Me.PO = ""
Exit Sub
Else
BArs.MoveFirst
Rqd = Me.Qty
Qty = BArs.Fields("Quantity Per AV")
Iss = BArs.Fields("Issued")
If Qty = Iss Then
If Qty = 1 Then
'condition 2
box = MsgBox("A " & Me.Drawing_Number & " has already been
issued to " & Me.PO)
Me.Qty = 0
Me.PO = ""
Else
'condition 3
box = MsgBox(Iss & " " & Me.Drawing_Number & " have already
been issued to " & Me.PO)
Me.Qty = 0
Me.PO = ""
End If
ElseIf Qty <> Iss Then
If Iss = 0 Then
If Rqd > Qty Then
'condition 4
box = MsgBox("The quantity requested is more than
required by the traveler.")
Me.Qty = Qty
Me.Qty.SetFocus
ElseIf Rqd < Qty Then
'condition 5
box = MsgBox(Qty - Rqd & " more " & Me.Drawing_Number &
" will still need to be assigned to this traveler.")
End If
ElseIf Iss > 0 Then
If Rqd > Qty Then
'condition 6
box = MsgBox("The quantity requested is more than
required by the traveler.")
Me.Qty = Qty - Iss
Me.Qty.SetFocus
ElseIf Rqd < Qty Then
'condition 7
box = MsgBox(Qty - Rqd - Iss & " more " &
Me.Drawing_Number & " will still need to be assigned to this traveler.")
End If
End If
End If
End If

BArs.Close
cn.Close

Set BArs = Nothing
Set cn = Nothing
End Sub

During testing of the code, I set in the ADO table:
"Quantity Per AV" = 4
"Issued" = 0
In the form I set Qty to 1.

This should result in condition 5, but condition 4 occurs, incorrect, 1 is
not > 4.

If I change the form Qty to 5 then condition 4 occurs, this is correct, 5
4
Click to expand...

Any value I place in the form's Qty field, brings condition 4.

Any suggestions?

Thanks,

-Matt
Click to expand...
 
M

MF Scheetz

Thanks Doug.

I always thought you could declare like that. But thanks for clarifying.

-Matt

Douglas J. Steele said:
I don't know whether this is the cause of your problem, but

Dim Rqd, Qty, Iss, box As Integer

doesn't do what you probably think it does.

Access doesn't allow short circuiting in declarations, so of those four
variables, only box is an Integer: the other three are actually Variants.

To make all the variables Integers, you need to use

Dim Rqd As Integer, Qty As Integer, Iss As Integer, box As Integer

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MF Scheetz said:
I've got the following code:

Private Sub PO_AfterUpdate()
Dim cn As ADODB.Connection
Dim BArs As ADODB.Recordset
Dim Rqd, Qty, Iss, box As Integer

Set cn = CurrentProject.Connection
Set BArs = New ADODB.Recordset

BArs.Open "SELECT * FROM [Bird Assembly Table] WHERE [Assembly
TravelerID] = " & Me.PO & " AND [Part Number] = '" & Me.Drawing_Number &
"'",
cn, adOpenDynamic, adLockOptimistic

If BArs.EOF Then
'condition 1
box = MsgBox("That Traveler ID doesn't exist.")
Me.PO = ""
Exit Sub
Else
BArs.MoveFirst
Rqd = Me.Qty
Qty = BArs.Fields("Quantity Per AV")
Iss = BArs.Fields("Issued")
If Qty = Iss Then
If Qty = 1 Then
'condition 2
box = MsgBox("A " & Me.Drawing_Number & " has already been
issued to " & Me.PO)
Me.Qty = 0
Me.PO = ""
Else
'condition 3
box = MsgBox(Iss & " " & Me.Drawing_Number & " have already
been issued to " & Me.PO)
Me.Qty = 0
Me.PO = ""
End If
ElseIf Qty <> Iss Then
If Iss = 0 Then
If Rqd > Qty Then
'condition 4
box = MsgBox("The quantity requested is more than
required by the traveler.")
Me.Qty = Qty
Me.Qty.SetFocus
ElseIf Rqd < Qty Then
'condition 5
box = MsgBox(Qty - Rqd & " more " & Me.Drawing_Number &
" will still need to be assigned to this traveler.")
End If
ElseIf Iss > 0 Then
If Rqd > Qty Then
'condition 6
box = MsgBox("The quantity requested is more than
required by the traveler.")
Me.Qty = Qty - Iss
Me.Qty.SetFocus
ElseIf Rqd < Qty Then
'condition 7
box = MsgBox(Qty - Rqd - Iss & " more " &
Me.Drawing_Number & " will still need to be assigned to this traveler.")
End If
End If
End If
End If

BArs.Close
cn.Close

Set BArs = Nothing
Set cn = Nothing
End Sub

During testing of the code, I set in the ADO table:
"Quantity Per AV" = 4
"Issued" = 0
In the form I set Qty to 1.

This should result in condition 5, but condition 4 occurs, incorrect, 1 is
not > 4.

If I change the form Qty to 5 then condition 4 occurs, this is correct, 5
4
Click to expand...

Any value I place in the form's Qty field, brings condition 4.

Any suggestions?

Thanks,

-Matt
Click to expand...
Click to expand...
 
M

MF Scheetz

Does this rule apply to all declaration statements?

Douglas J. Steele said:
I don't know whether this is the cause of your problem, but

Dim Rqd, Qty, Iss, box As Integer

doesn't do what you probably think it does.

Access doesn't allow short circuiting in declarations, so of those four
variables, only box is an Integer: the other three are actually Variants.

To make all the variables Integers, you need to use

Dim Rqd As Integer, Qty As Integer, Iss As Integer, box As Integer

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MF Scheetz said:
I've got the following code:

Private Sub PO_AfterUpdate()
Dim cn As ADODB.Connection
Dim BArs As ADODB.Recordset
Dim Rqd, Qty, Iss, box As Integer

Set cn = CurrentProject.Connection
Set BArs = New ADODB.Recordset

BArs.Open "SELECT * FROM [Bird Assembly Table] WHERE [Assembly
TravelerID] = " & Me.PO & " AND [Part Number] = '" & Me.Drawing_Number &
"'",
cn, adOpenDynamic, adLockOptimistic

If BArs.EOF Then
'condition 1
box = MsgBox("That Traveler ID doesn't exist.")
Me.PO = ""
Exit Sub
Else
BArs.MoveFirst
Rqd = Me.Qty
Qty = BArs.Fields("Quantity Per AV")
Iss = BArs.Fields("Issued")
If Qty = Iss Then
If Qty = 1 Then
'condition 2
box = MsgBox("A " & Me.Drawing_Number & " has already been
issued to " & Me.PO)
Me.Qty = 0
Me.PO = ""
Else
'condition 3
box = MsgBox(Iss & " " & Me.Drawing_Number & " have already
been issued to " & Me.PO)
Me.Qty = 0
Me.PO = ""
End If
ElseIf Qty <> Iss Then
If Iss = 0 Then
If Rqd > Qty Then
'condition 4
box = MsgBox("The quantity requested is more than
required by the traveler.")
Me.Qty = Qty
Me.Qty.SetFocus
ElseIf Rqd < Qty Then
'condition 5
box = MsgBox(Qty - Rqd & " more " & Me.Drawing_Number &
" will still need to be assigned to this traveler.")
End If
ElseIf Iss > 0 Then
If Rqd > Qty Then
'condition 6
box = MsgBox("The quantity requested is more than
required by the traveler.")
Me.Qty = Qty - Iss
Me.Qty.SetFocus
ElseIf Rqd < Qty Then
'condition 7
box = MsgBox(Qty - Rqd - Iss & " more " &
Me.Drawing_Number & " will still need to be assigned to this traveler.")
End If
End If
End If
End If

BArs.Close
cn.Close

Set BArs = Nothing
Set cn = Nothing
End Sub

During testing of the code, I set in the ADO table:
"Quantity Per AV" = 4
"Issued" = 0
In the form I set Qty to 1.

This should result in condition 5, but condition 4 occurs, incorrect, 1 is
not > 4.

If I change the form Qty to 5 then condition 4 occurs, this is correct, 5
4
Click to expand...

Any value I place in the form's Qty field, brings condition 4.

Any suggestions?

Thanks,

-Matt
Click to expand...
Click to expand...
 
D

Dale Fye

A couple of other points.

1. get rid of the Exit Sub line in condition 1. If this condition occurs,
you leave the sub without closing the recordset or connection. While Access
should clean this up for you, you will drop out of the If statement right
before the close statements if you delete this line.

2. You don't need the BArs.MoveFirst line.

3. What do you want to happen if Rqd and Qty are equal?

Dale
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Working outlook via vba code with xlsx - error 0
VBA works in orignal but fails in copy of database 1
Remove Identical words 0
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Passing Quantities with an array 2
Help with creating a model serial number 2
Assign the records shown in a subform to a recordset 2
Add where clause to querydef? 1

Top