Comedy of errors - VB code (query based on form controls)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello.
I've been stumbling around the forums and trying a variety of things, but
can't solve this problem. Here's what I'm trying to do:

I have a form control which queries the open quantity for a job. The query
criteria use three other controls on that form to filter the results to a
single record. I want to ensure the user cannot mistype a quantity greater
than the actual quantity open.

I appreciate any assistance!


Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset
Dim QTY As Integer
Dim total As Integer
Dim PullFIELD As Control

Function QTY_PULL()

Set db = CurrentDb()
Set qdf = db.QueryDefs!PullQtyVB
Set rs = qdf.OpenRecordset(dbOpenDynaset)
rs.MoveFirst

Set PullFIELD = Screen.ActiveControl
QTY = PullFIELD.Value
total = rs.Fields("QuantityOpen")

If (QTY) > (total) Then
MsgBox ("QUANTITY ENTERED CANNOT BE MORE THAN PULL QUANTITY!")
PullFIELD.Value = Null
SendKeys "+{TAB}"
Exit Function
Else
PullFIELD.Value = QTY
End If

End Function
 
First, your Dims need to be inside the function. Also, in this case, a
DLookup would be a better solution. Also, Screen.ActiveControl can sometime
be squirlly, so I suggest passing the control as a argument. And, it nees to
return a value so the control will know to cancel the update. This call to
this function should be in each of the 3 controls used to test for
availability.

Cancel = QTY_PULL(Me.MyName)

Function QTY_PULL(ctlCurrent as Control) As Boolean

If ctlCurrent > Nz(DLookup("[QuantityOpen]", "TableNameHere", _
"[JobNumber] = " & Me.txtJobNumber), 0) Then
MsgBox ("QUANTITY ENTERED CANNOT BE MORE THAN PULL QUANTITY!")
QTY_PULL = True
End If

End Function

You will need to replace names for the tablename, jobnumber field, and
txtJobNumber control with those in your application.
 
Thanks for your reply, Klatuu. I'm trying to set that up now, but the
Me.MyName is giving me problems. I either get 'Invalid Outside Procedure' or
'Invalid Use of Me Keyword, depending on where I try to place the statement.
I'm sure that's something easy, but... ?


Klatuu said:
First, your Dims need to be inside the function. Also, in this case, a
DLookup would be a better solution. Also, Screen.ActiveControl can sometime
be squirlly, so I suggest passing the control as a argument. And, it nees to
return a value so the control will know to cancel the update. This call to
this function should be in each of the 3 controls used to test for
availability.

Cancel = QTY_PULL(Me.MyName)

Function QTY_PULL(ctlCurrent as Control) As Boolean

If ctlCurrent > Nz(DLookup("[QuantityOpen]", "TableNameHere", _
"[JobNumber] = " & Me.txtJobNumber), 0) Then
MsgBox ("QUANTITY ENTERED CANNOT BE MORE THAN PULL QUANTITY!")
QTY_PULL = True
End If

End Function

You will need to replace names for the tablename, jobnumber field, and
txtJobNumber control with those in your application.
ormazd said:
Hello.
I've been stumbling around the forums and trying a variety of things, but
can't solve this problem. Here's what I'm trying to do:

I have a form control which queries the open quantity for a job. The query
criteria use three other controls on that form to filter the results to a
single record. I want to ensure the user cannot mistype a quantity greater
than the actual quantity open.

I appreciate any assistance!


Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset
Dim QTY As Integer
Dim total As Integer
Dim PullFIELD As Control

Function QTY_PULL()

Set db = CurrentDb()
Set qdf = db.QueryDefs!PullQtyVB
Set rs = qdf.OpenRecordset(dbOpenDynaset)
rs.MoveFirst

Set PullFIELD = Screen.ActiveControl
QTY = PullFIELD.Value
total = rs.Fields("QuantityOpen")

If (QTY) > (total) Then
MsgBox ("QUANTITY ENTERED CANNOT BE MORE THAN PULL QUANTITY!")
PullFIELD.Value = Null
SendKeys "+{TAB}"
Exit Function
Else
PullFIELD.Value = QTY
End If

End Function
Click to expand...
Click to expand...
 
The code
Cancel = (Me.MyName)
Should go in the before update event of each of the 3 text boxes you want to
test with. Replace MyName with the name of the control. For example, if
your text box is named txtFooBah then it would be
Cancel = (Me.txtFooBah)

ormazd said:
Thanks for your reply, Klatuu. I'm trying to set that up now, but the
Me.MyName is giving me problems. I either get 'Invalid Outside Procedure' or
'Invalid Use of Me Keyword, depending on where I try to place the statement.
I'm sure that's something easy, but... ?


Klatuu said:
First, your Dims need to be inside the function. Also, in this case, a
DLookup would be a better solution. Also, Screen.ActiveControl can sometime
be squirlly, so I suggest passing the control as a argument. And, it nees to
return a value so the control will know to cancel the update. This call to
this function should be in each of the 3 controls used to test for
availability.

Cancel = QTY_PULL(Me.MyName)

Function QTY_PULL(ctlCurrent as Control) As Boolean

If ctlCurrent > Nz(DLookup("[QuantityOpen]", "TableNameHere", _
"[JobNumber] = " & Me.txtJobNumber), 0) Then
MsgBox ("QUANTITY ENTERED CANNOT BE MORE THAN PULL QUANTITY!")
QTY_PULL = True
End If

End Function

You will need to replace names for the tablename, jobnumber field, and
txtJobNumber control with those in your application.
ormazd said:
Hello.
I've been stumbling around the forums and trying a variety of things, but
can't solve this problem. Here's what I'm trying to do:

I have a form control which queries the open quantity for a job. The query
criteria use three other controls on that form to filter the results to a
single record. I want to ensure the user cannot mistype a quantity greater
than the actual quantity open.

I appreciate any assistance!


Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset
Dim QTY As Integer
Dim total As Integer
Dim PullFIELD As Control

Function QTY_PULL()

Set db = CurrentDb()
Set qdf = db.QueryDefs!PullQtyVB
Set rs = qdf.OpenRecordset(dbOpenDynaset)
rs.MoveFirst

Set PullFIELD = Screen.ActiveControl
QTY = PullFIELD.Value
total = rs.Fields("QuantityOpen")

If (QTY) > (total) Then
MsgBox ("QUANTITY ENTERED CANNOT BE MORE THAN PULL QUANTITY!")
PullFIELD.Value = Null
SendKeys "+{TAB}"
Exit Function
Else
PullFIELD.Value = QTY
End If

End Function
Click to expand...
Click to expand...
Click to expand...
 
Back
Top