Lost Focus - Requery

A

AFSSkier

How can I do a requery or error message "change to new sceen", when a user
queries one sku number. Then queries another without changing record pages in
a form. For example, if they key in the first sku number 12345 & all of the
fields are populated, then 23456 & the null fields do not requery. Capturing
the data from the previous sku number. We have asked that they change to a
new record screen, but sometimes forget. I have included the LostFocus code
below.

Private Sub Code_LostFocus()
Dim rst As Recordset
Dim db As Database
Dim dblItem As Double

On Error GoTo HandleErrors
If Me.Code.Text > 0 Then

dblItem = Me.Code.Text
Let mstrQuery = "Select * from ItemData where ITEMNO = " & _
"" & dblItem & ""
Set db = CurrentDb()
Set rst = db.OpenRecordset(mstrQuery)
Me.Pack.SetFocus
Me.Pack.Text = rst!Pack
Me.Size.SetFocus
Me.Size.Text = rst!Size
Me.Description.SetFocus
Me.Description = rst!Desc
‘etc., etc.

If IsNull(rst![5200AMT]) Then GoTo CB2:
Me.GMRTL.Text = rst![5200AMT]
Me.GMQTY.SetFocus
Me.GMQTY.Text = rst![5200QTY]
CB2:
Me.GRORTL.SetFocus
If IsNull(rst![0141AMT]) Then GoTo CB3:
Me.GRORTL.Text = rst![0141AMT]
Me.GROQTY.SetFocus
Me.GROQTY.Text = rst![0141QTY]
‘etc., etc.

CB7:
Me.CLPFLAG.SetFocus
If IsNull(rst![CLP-FLAG]) Then GoTo CB8:
Me.CLPFLAG.Text = rst![CLP-FLAG]
CB8:
Me.OI.SetFocus

End If

ExitHere:
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

HandleErrors:
MsgBox Err.Description
Resume ExitHere
End Sub
 
S

Steve Sanford

One way would be to take out (or comment out) the IF() functions.
I don't understand why you check one field and not another.

But one way to ensure that all the controls are updated. Add the following
line to the code:


Private Sub Code_LostFocus()
Dim rst As Recordset
Dim db As Database
Dim dblItem As Double

On Error GoTo HandleErrors

'Add this line
ClearValues '<<<<<<<<

If Me.Code.Text > 0 Then
'----SNIP------

And add this Sub:

Sub ClearValues()
Me.Pack = Null
Me.Size = Null
Me.Description = Null
Me.GMRTL.Text = Null
Me.GMQTY.Text = Null
Me.GRORTL.Text = Null
Me.GROQTY.Text = Null
Me.CLPFLAG.Text = Null
Me.OI = Null
'.
'.
'etc., etc.
End Sub


So you enter a SKU and leave the control, the sub "ClearValues" is called
which clears the fields, then the rest of the code runs.

When you enter another SKU, the controls are cleared, etc.


HTH
 
A

AFSSkier

Steve,

I added your suggestions & received an “Expected End Sub†compile error,
after the "ClearValues". I've included the new code below.

Private Sub Code_LostFocus()
Dim rst As Recordset
Dim db As Database
Dim dblItem As Double

On Error GoTo HandleErrors

'Add this line
ClearValues '<<<<<<<<

‘If Me.Code.Text > 0 Then
'----SNIP------

‘ < = = Expected End Sub here

‘And add this Sub:
Sub ClearValues()
Me.Pack = Null
Me.Size = Null
Me.Description = Null
Me.GMRTL.Text = Null
Me.GMQTY.Text = Null
Me.GRORTL.Text = Null
Me.GROQTY.Text = Null
Me.CLPFLAG.Text = Null
Me.OI = Null
'.
'.
'etc., etc.

dblItem = Me.Code.Text
Let mstrQuery = "Select * from ItemData where ITEMNO = " & _
"" & dblItem & ""
Set db = CurrentDb()
Set rst = db.OpenRecordset(mstrQuery)
Me.Pack.SetFocus
Me.Pack.Text = rst!Pack
Me.Size.SetFocus
Me.Size.Text = rst!Size
Me.Description.SetFocus
Me.Description = rst!Desc
'.
'.
'etc., etc.

If IsNull(rst![5200AMT]) Then GoTo CB2:
Me.GMRTL.Text = rst![5200AMT]
Me.GMQTY.SetFocus
Me.GMQTY.Text = rst![5200QTY]
CB2:
Me.GRORTL.SetFocus
If IsNull(rst![0141AMT]) Then GoTo CB3:
Me.GRORTL.Text = rst![0141AMT]
Me.GROQTY.SetFocus
Me.GROQTY.Text = rst![0141QTY]
‘etc., etc.

CB7:
Me.CLPFLAG.SetFocus
If IsNull(rst![CLP-FLAG]) Then GoTo CB8:
Me.CLPFLAG.Text = rst![CLP-FLAG]
CB8:
Me.OI.SetFocus

End If

ExitHere:
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

HandleErrors:
MsgBox Err.Description
Resume ExitHere
End Sub

--
Thanks, Kevin


Steve Sanford said:
One way would be to take out (or comment out) the IF() functions.
I don't understand why you check one field and not another.

But one way to ensure that all the controls are updated. Add the following
line to the code:


Private Sub Code_LostFocus()
Dim rst As Recordset
Dim db As Database
Dim dblItem As Double

On Error GoTo HandleErrors

'Add this line
ClearValues '<<<<<<<<

If Me.Code.Text > 0 Then
'----SNIP------

And add this Sub:

Sub ClearValues()
Me.Pack = Null
Me.Size = Null
Me.Description = Null
Me.GMRTL.Text = Null
Me.GMQTY.Text = Null
Me.GRORTL.Text = Null
Me.GROQTY.Text = Null
Me.CLPFLAG.Text = Null
Me.OI = Null
'.
'.
'etc., etc.
End Sub


So you enter a SKU and leave the control, the sub "ClearValues" is called
which clears the fields, then the rest of the code runs.

When you enter another SKU, the controls are cleared, etc.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


AFSSkier said:
How can I do a requery or error message "change to new sceen", when a user
queries one sku number. Then queries another without changing record pages in
a form. For example, if they key in the first sku number 12345 & all of the
fields are populated, then 23456 & the null fields do not requery. Capturing
the data from the previous sku number. We have asked that they change to a
new record screen, but sometimes forget. I have included the LostFocus code
below.

Private Sub Code_LostFocus()
Dim rst As Recordset
Dim db As Database
Dim dblItem As Double

On Error GoTo HandleErrors
If Me.Code.Text > 0 Then

dblItem = Me.Code.Text
Let mstrQuery = "Select * from ItemData where ITEMNO = " & _
"" & dblItem & ""
Set db = CurrentDb()
Set rst = db.OpenRecordset(mstrQuery)
Me.Pack.SetFocus
Me.Pack.Text = rst!Pack
Me.Size.SetFocus
Me.Size.Text = rst!Size
Me.Description.SetFocus
Me.Description = rst!Desc
‘etc., etc.

If IsNull(rst![5200AMT]) Then GoTo CB2:
Me.GMRTL.Text = rst![5200AMT]
Me.GMQTY.SetFocus
Me.GMQTY.Text = rst![5200QTY]
CB2:
Me.GRORTL.SetFocus
If IsNull(rst![0141AMT]) Then GoTo CB3:
Me.GRORTL.Text = rst![0141AMT]
Me.GROQTY.SetFocus
Me.GROQTY.Text = rst![0141QTY]
‘etc., etc.

CB7:
Me.CLPFLAG.SetFocus
If IsNull(rst![CLP-FLAG]) Then GoTo CB8:
Me.CLPFLAG.Text = rst![CLP-FLAG]
CB8:
Me.OI.SetFocus

End If

ExitHere:
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

HandleErrors:
MsgBox Err.Description
Resume ExitHere
End Sub
Click to expand...
Click to expand...
 
S

Steve Sanford

OK, here it is again.....

Add one line to your code:

'-----------------------------------------------
Private Sub Code_LostFocus()
Dim rst As Recordset
Dim db As Database
Dim dblItem As Double

On Error GoTo HandleErrors

'Add this line
ClearValues '<<<<<<<< <<<<<<<<<<

If Me.Code.Text > 0 Then

dblItem = Me.Code.Text
Let mstrQuery = "Select * from ItemData where ITEMNO = " & _
"" & dblItem & ""
Set db = CurrentDb()
Set rst = db.OpenRecordset(mstrQuery)
Me.Pack.SetFocus
Me.Pack.Text = rst!Pack
Me.Size.SetFocus
Me.Size.Text = rst!Size
Me.Description.SetFocus
Me.Description = rst!Desc
‘etc., etc.

If IsNull(rst![5200AMT]) Then GoTo CB2:
Me.GMRTL.Text = rst![5200AMT]
Me.GMQTY.SetFocus
Me.GMQTY.Text = rst![5200QTY]
CB2:
Me.GRORTL.SetFocus
If IsNull(rst![0141AMT]) Then GoTo CB3:
Me.GRORTL.Text = rst![0141AMT]
Me.GROQTY.SetFocus
Me.GROQTY.Text = rst![0141QTY]
‘etc., etc.

CB7:
Me.CLPFLAG.SetFocus
If IsNull(rst![CLP-FLAG]) Then GoTo CB8:
Me.CLPFLAG.Text = rst![CLP-FLAG]
CB8:
Me.OI.SetFocus

End If

ExitHere:
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

HandleErrors:
MsgBox Err.Description
Resume ExitHere
End Sub
'-----------------------------------------------



This NEW subroutine clears the fields and is called by Sub Code_LostFocus():

'---------------------------
Sub ClearValues()
Me.Pack = Null
Me.Size = Null
Me.Description = Null
Me.GMRTL.Text = Null
Me.GMQTY.Text = Null
Me.GRORTL.Text = Null
Me.GROQTY.Text = Null
Me.CLPFLAG.Text = Null
Me.OI = Null
'.
'.
'etc., etc.
End Sub
'---------------------------



--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


AFSSkier said:
Steve,

I added your suggestions & received an “Expected End Sub†compile error,
after the "ClearValues". I've included the new code below.

Private Sub Code_LostFocus()
Dim rst As Recordset
Dim db As Database
Dim dblItem As Double

On Error GoTo HandleErrors

'Add this line
ClearValues '<<<<<<<<

‘If Me.Code.Text > 0 Then
'----SNIP------

‘ < = = Expected End Sub here

‘And add this Sub:
Sub ClearValues()
Me.Pack = Null
Me.Size = Null
Me.Description = Null
Me.GMRTL.Text = Null
Me.GMQTY.Text = Null
Me.GRORTL.Text = Null
Me.GROQTY.Text = Null
Me.CLPFLAG.Text = Null
Me.OI = Null
'.
'.
'etc., etc.

dblItem = Me.Code.Text
Let mstrQuery = "Select * from ItemData where ITEMNO = " & _
"" & dblItem & ""
Set db = CurrentDb()
Set rst = db.OpenRecordset(mstrQuery)
Me.Pack.SetFocus
Me.Pack.Text = rst!Pack
Me.Size.SetFocus
Me.Size.Text = rst!Size
Me.Description.SetFocus
Me.Description = rst!Desc
'.
'.
'etc., etc.

If IsNull(rst![5200AMT]) Then GoTo CB2:
Me.GMRTL.Text = rst![5200AMT]
Me.GMQTY.SetFocus
Me.GMQTY.Text = rst![5200QTY]
CB2:
Me.GRORTL.SetFocus
If IsNull(rst![0141AMT]) Then GoTo CB3:
Me.GRORTL.Text = rst![0141AMT]
Me.GROQTY.SetFocus
Me.GROQTY.Text = rst![0141QTY]
‘etc., etc.

CB7:
Me.CLPFLAG.SetFocus
If IsNull(rst![CLP-FLAG]) Then GoTo CB8:
Me.CLPFLAG.Text = rst![CLP-FLAG]
CB8:
Me.OI.SetFocus

End If

ExitHere:
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

HandleErrors:
MsgBox Err.Description
Resume ExitHere
End Sub

--
Thanks, Kevin


Steve Sanford said:
One way would be to take out (or comment out) the IF() functions.
I don't understand why you check one field and not another.

But one way to ensure that all the controls are updated. Add the following
line to the code:


Private Sub Code_LostFocus()
Dim rst As Recordset
Dim db As Database
Dim dblItem As Double

On Error GoTo HandleErrors

'Add this line
ClearValues '<<<<<<<<

If Me.Code.Text > 0 Then
'----SNIP------

And add this Sub:

Sub ClearValues()
Me.Pack = Null
Me.Size = Null
Me.Description = Null
Me.GMRTL.Text = Null
Me.GMQTY.Text = Null
Me.GRORTL.Text = Null
Me.GROQTY.Text = Null
Me.CLPFLAG.Text = Null
Me.OI = Null
'.
'.
'etc., etc.
End Sub


So you enter a SKU and leave the control, the sub "ClearValues" is called
which clears the fields, then the rest of the code runs.

When you enter another SKU, the controls are cleared, etc.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


AFSSkier said:
How can I do a requery or error message "change to new sceen", when a user
queries one sku number. Then queries another without changing record pages in
a form. For example, if they key in the first sku number 12345 & all of the
fields are populated, then 23456 & the null fields do not requery. Capturing
the data from the previous sku number. We have asked that they change to a
new record screen, but sometimes forget. I have included the LostFocus code
below.

Private Sub Code_LostFocus()
Dim rst As Recordset
Dim db As Database
Dim dblItem As Double

On Error GoTo HandleErrors
If Me.Code.Text > 0 Then

dblItem = Me.Code.Text
Let mstrQuery = "Select * from ItemData where ITEMNO = " & _
"" & dblItem & ""
Set db = CurrentDb()
Set rst = db.OpenRecordset(mstrQuery)
Me.Pack.SetFocus
Me.Pack.Text = rst!Pack
Me.Size.SetFocus
Me.Size.Text = rst!Size
Me.Description.SetFocus
Me.Description = rst!Desc
‘etc., etc.

If IsNull(rst![5200AMT]) Then GoTo CB2:
Me.GMRTL.Text = rst![5200AMT]
Me.GMQTY.SetFocus
Me.GMQTY.Text = rst![5200QTY]
CB2:
Me.GRORTL.SetFocus
If IsNull(rst![0141AMT]) Then GoTo CB3:
Me.GRORTL.Text = rst![0141AMT]
Me.GROQTY.SetFocus
Me.GROQTY.Text = rst![0141QTY]
‘etc., etc.

CB7:
Me.CLPFLAG.SetFocus
If IsNull(rst![CLP-FLAG]) Then GoTo CB8:
Me.CLPFLAG.Text = rst![CLP-FLAG]
CB8:
Me.OI.SetFocus

End If

ExitHere:
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

HandleErrors:
MsgBox Err.Description
Resume ExitHere
End Sub
Click to expand...
Click to expand...
Click to expand...
 
A

AFSSkier

Steve,

This time, I only added the “ClearValue†line. Now I get “Sub or Function
not defined†compile error. I've included the new code below.

‘_________________________
Private Sub Code_LostFocus()
Dim rst As Recordset
Dim db As Database
Dim dblItem As Double

On Error GoTo HandleErrors

‘_________________________
'Added only this line
ClearValues ‘<<<< Compile Error - Sub or Function not defined
‘_________________________

If Me.Code.Text > 0 Then
dblItem = Me.Code.Text
Let mstrQuery = "Select * from ItemData where ITEMNO = " & _
"" & dblItem & ""
Set db = CurrentDb()
Set rst = db.OpenRecordset(mstrQuery)
Me.Pack.SetFocus
Me.Pack.Text = rst!Pack
Me.Size.SetFocus
Me.Size.Text = rst!Size
Me.Description.SetFocus
Me.Description = rst!Desc
'.
'.
'etc., etc.

If IsNull(rst![5200AMT]) Then GoTo CB2:
Me.GMRTL.Text = rst![5200AMT]
Me.GMQTY.SetFocus
Me.GMQTY.Text = rst![5200QTY]
CB2:
Me.GRORTL.SetFocus
If IsNull(rst![0141AMT]) Then GoTo CB3:
Me.GRORTL.Text = rst![0141AMT]
Me.GROQTY.SetFocus
Me.GROQTY.Text = rst![0141QTY]
‘etc., etc.

CB7:
Me.CLPFLAG.SetFocus
If IsNull(rst![CLP-FLAG]) Then GoTo CB8:
Me.CLPFLAG.Text = rst![CLP-FLAG]
CB8:
Me.OI.SetFocus

End If

ExitHere:
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

HandleErrors:
MsgBox Err.Description
Resume ExitHere
End Sub
‘_________________________

--
Thanks, Kevin


Steve Sanford said:
OK, here it is again.....

Add one line to your code:

'-----------------------------------------------
Private Sub Code_LostFocus()
Dim rst As Recordset
Dim db As Database
Dim dblItem As Double

On Error GoTo HandleErrors

'Add this line
ClearValues '<<<<<<<< <<<<<<<<<<

If Me.Code.Text > 0 Then

dblItem = Me.Code.Text
Let mstrQuery = "Select * from ItemData where ITEMNO = " & _
"" & dblItem & ""
Set db = CurrentDb()
Set rst = db.OpenRecordset(mstrQuery)
Me.Pack.SetFocus
Me.Pack.Text = rst!Pack
Me.Size.SetFocus
Me.Size.Text = rst!Size
Me.Description.SetFocus
Me.Description = rst!Desc
‘etc., etc.

If IsNull(rst![5200AMT]) Then GoTo CB2:
Me.GMRTL.Text = rst![5200AMT]
Me.GMQTY.SetFocus
Me.GMQTY.Text = rst![5200QTY]
CB2:
Me.GRORTL.SetFocus
If IsNull(rst![0141AMT]) Then GoTo CB3:
Me.GRORTL.Text = rst![0141AMT]
Me.GROQTY.SetFocus
Me.GROQTY.Text = rst![0141QTY]
‘etc., etc.

CB7:
Me.CLPFLAG.SetFocus
If IsNull(rst![CLP-FLAG]) Then GoTo CB8:
Me.CLPFLAG.Text = rst![CLP-FLAG]
CB8:
Me.OI.SetFocus

End If

ExitHere:
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

HandleErrors:
MsgBox Err.Description
Resume ExitHere
End Sub
'-----------------------------------------------



This NEW subroutine clears the fields and is called by Sub Code_LostFocus():

'---------------------------
Sub ClearValues()
Me.Pack = Null
Me.Size = Null
Me.Description = Null
Me.GMRTL.Text = Null
Me.GMQTY.Text = Null
Me.GRORTL.Text = Null
Me.GROQTY.Text = Null
Me.CLPFLAG.Text = Null
Me.OI = Null
'.
'.
'etc., etc.
End Sub
'---------------------------



--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


AFSSkier said:
Steve,

I added your suggestions & received an “Expected End Sub†compile error,
after the "ClearValues". I've included the new code below.

Private Sub Code_LostFocus()
Dim rst As Recordset
Dim db As Database
Dim dblItem As Double

On Error GoTo HandleErrors

'Add this line
ClearValues '<<<<<<<<

‘If Me.Code.Text > 0 Then
'----SNIP------

‘ < = = Expected End Sub here

‘And add this Sub:
Sub ClearValues()
Me.Pack = Null
Me.Size = Null
Me.Description = Null
Me.GMRTL.Text = Null
Me.GMQTY.Text = Null
Me.GRORTL.Text = Null
Me.GROQTY.Text = Null
Me.CLPFLAG.Text = Null
Me.OI = Null
'.
'.
'etc., etc.

dblItem = Me.Code.Text
Let mstrQuery = "Select * from ItemData where ITEMNO = " & _
"" & dblItem & ""
Set db = CurrentDb()
Set rst = db.OpenRecordset(mstrQuery)
Me.Pack.SetFocus
Me.Pack.Text = rst!Pack
Me.Size.SetFocus
Me.Size.Text = rst!Size
Me.Description.SetFocus
Me.Description = rst!Desc
'.
'.
'etc., etc.

If IsNull(rst![5200AMT]) Then GoTo CB2:
Me.GMRTL.Text = rst![5200AMT]
Me.GMQTY.SetFocus
Me.GMQTY.Text = rst![5200QTY]
CB2:
Me.GRORTL.SetFocus
If IsNull(rst![0141AMT]) Then GoTo CB3:
Me.GRORTL.Text = rst![0141AMT]
Me.GROQTY.SetFocus
Me.GROQTY.Text = rst![0141QTY]
‘etc., etc.

CB7:
Me.CLPFLAG.SetFocus
If IsNull(rst![CLP-FLAG]) Then GoTo CB8:
Me.CLPFLAG.Text = rst![CLP-FLAG]
CB8:
Me.OI.SetFocus

End If

ExitHere:
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

HandleErrors:
MsgBox Err.Description
Resume ExitHere
End Sub

--
Thanks, Kevin


Steve Sanford said:
One way would be to take out (or comment out) the IF() functions.
I don't understand why you check one field and not another.

But one way to ensure that all the controls are updated. Add the following
line to the code:


Private Sub Code_LostFocus()
Dim rst As Recordset
Dim db As Database
Dim dblItem As Double

On Error GoTo HandleErrors

'Add this line
ClearValues '<<<<<<<<

If Me.Code.Text > 0 Then
'----SNIP------

And add this Sub:

Sub ClearValues()
Me.Pack = Null
Me.Size = Null
Me.Description = Null
Me.GMRTL.Text = Null
Me.GMQTY.Text = Null
Me.GRORTL.Text = Null
Me.GROQTY.Text = Null
Me.CLPFLAG.Text = Null
Me.OI = Null
'.
'.
'etc., etc.
End Sub


So you enter a SKU and leave the control, the sub "ClearValues" is called
which clears the fields, then the rest of the code runs.

When you enter another SKU, the controls are cleared, etc.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

How can I do a requery or error message "change to new sceen", when a user
queries one sku number. Then queries another without changing record pages in
a form. For example, if they key in the first sku number 12345 & all of the
fields are populated, then 23456 & the null fields do not requery. Capturing
the data from the previous sku number. We have asked that they change to a
new record screen, but sometimes forget. I have included the LostFocus code
below.

Private Sub Code_LostFocus()
Dim rst As Recordset
Dim db As Database
Dim dblItem As Double

On Error GoTo HandleErrors
If Me.Code.Text > 0 Then

dblItem = Me.Code.Text
Let mstrQuery = "Select * from ItemData where ITEMNO = " & _
"" & dblItem & ""
Set db = CurrentDb()
Set rst = db.OpenRecordset(mstrQuery)
Me.Pack.SetFocus
Me.Pack.Text = rst!Pack
Me.Size.SetFocus
Me.Size.Text = rst!Size
Me.Description.SetFocus
Me.Description = rst!Desc
‘etc., etc.

If IsNull(rst![5200AMT]) Then GoTo CB2:
Me.GMRTL.Text = rst![5200AMT]
Me.GMQTY.SetFocus
Me.GMQTY.Text = rst![5200QTY]
CB2:
Me.GRORTL.SetFocus
If IsNull(rst![0141AMT]) Then GoTo CB3:
Me.GRORTL.Text = rst![0141AMT]
Me.GROQTY.SetFocus
Me.GROQTY.Text = rst![0141QTY]
‘etc., etc.

CB7:
Me.CLPFLAG.SetFocus
If IsNull(rst![CLP-FLAG]) Then GoTo CB8:
Me.CLPFLAG.Text = rst![CLP-FLAG]
CB8:
Me.OI.SetFocus

End If

ExitHere:
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

HandleErrors:
MsgBox Err.Description
Resume ExitHere
End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

Steve,

This time, I only added the “ClearValue” line. Now I get “Sub or Function
not defined” compile error. I've included the new code below.
Click to expand...

Did you add the ClearValues routine? Evidently not. Steve's code included:

This NEW subroutine clears the fields and is called by Sub Code_LostFocus():

'---------------------------
Sub ClearValues()
Me.Pack = Null
Me.Size = Null
Me.Description = Null
Me.GMRTL.Text = Null
Me.GMQTY.Text = Null
Me.GRORTL.Text = Null
Me.GROQTY.Text = Null
Me.CLPFLAG.Text = Null
Me.OI = Null
'.
'.
'etc., etc.
End Sub


If you don't have the subroutine in your code, you can't call it!

John W. Vinson [MVP]
 
S

Steve Sanford

Kevin,

Problem: When you enter a new SKU, old data is left in the text boxes.

Solution: Clear the textboxes on the form before updating with new data.

How to do it:

Add the *NEW* subroutine, I named it "Sub ClearValues()",
below the "Sub Code_LostFocus()" code.

***WARNING: You did not provide *ALL* of the code for "Sub
Code_LostFocus()", you had ETC. in the code, so *YOU* need to add the rest
of the controls you want cleared to the subroutine "Sub ClearValues()". ***


Adding the line " ClearValues" as the first line after " On Error GoTo
HandleErrors" calls the new subroutine, which clears the old data. Then the
rest of the code in "Sub Code_LostFocus()" runs, which fills in the new data
for the SKU (if any is found).



( Thanks, John :)
 
A

AFSSkier

I didn't understand at first. Now that I understand it as a seperate sub.
What throw me was the ClearValue. It works now, exactly what I wanted.

You & John were a big help.
 
A

AFSSkier

I didn't understand at first. Now that I understand it as a seperate sub.
What throw me was the ClearValue. It works now, exactly what I wanted.

You & Steve were a big help.
 

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

Append loop working but causing duplicates - I'm lost 3
3622: You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that 5
Send Email - Addresses, subject text and body text in query... 0

Top