Can anyone help with this?

A

Anthonydb

Registered User.
Local time
Today, 12:09
Joined
Apr 25, 2003
Messages
19
Please bear with me, the length of the code is long. I'm trying to get this search form search again after the first search is completed. I can type in a barcode # and perform a search but when I try another search, I get a yellow highlight pointing to the following code:

myrec.Open stringQry, CurrentProject.Connection, , adLockOptimistic

I get this whether I completely enter a new number or if I just overwrite part of the number. He is the code. Any help is appreciated.

Private Sub Command4_Click()
stringChk = Text00.Value
If Me.Text00.Value = "0" Then
GoTo EndSeq
End If
Me.Text01.Value = Null
Me.Text02.Value = Null
Me.Text03.Value = Null
Me.Text04.Value = Null
Me.Text05.Value = Null
Me.Text06.Value = Null
Me.Text07.Value = Null
Me.Text08.Value = Null
Me.Text09.Value = Null
Me.Text10.Value = Null
Me.Text11.Value = Null
Me.Text12.Value = Null
Me.Text13.Value = Null
Me.Check01.Value = False

stringQry = "Select * from [Tape DC] where "
stringQry = stringQry & "Barcode = " & cquot & stringChk & cquot
myrec.Open stringQry, CurrentProject.Connection, , adLockOptimistic

If myrec.EOF Then
myrec.Close
GoTo TapeDescriptionChk
End If
GoTo Tape_DC

TapeDescriptionChk:
stringQry = "Select * from [Tape DC] where "
stringQry = stringQry & "TapeDescription = " & cquot & stringChk & cquot
myrec.Open stringQry, CurrentProject.Connection, , adLockOptimistic

If myrec.EOF Then
myrec.Close
GoTo BackupDateChk
End If
GoTo Tape_DC

BackupDateChk:
stringQry = "Select * from [Tape DC] where "
stringQry = stringQry & "BackupDate = " & cquot & stringChk & cquot
myrec.Open stringQry, CurrentProject.Connection, , adLockOptimistic

If myrec.EOF Then
myrec.Close
GoTo SeqNumberofTapesChk
End If
GoTo Tape_DC

SeqNumberofTapesChk:
stringQry = "Select * from [Tape DC] where "
stringQry = stringQry & "SeqNumberofTapes = " & cquot & stringChk & cquot
myrec.Open stringQry, CurrentProject.Connection, , adLockOptimistic

If myrec.EOF Then
myrec.Close
GoTo JobIDChk
End If

GoTo Tape_DC

JobIDChk:
stringQry = "Select * from [Tape_DC] where "
stringQry = stringQry & "JobID = " & cquot & stringChk & cquot
myrec.Open stringQry, CurrentProject.Connection, , adLockOptimistic

If myrec.EOF Then
myrec.Close
GoTo SerialNumberChk
End If

GoTo Tape_DC

SerialNumberChk:
stringQry = "Select * from [Tape_DC] where "
stringQry = stringQry & "SerialNumber = " & cquot & stringChk & cquot
myrec.Open stringQry, CurrentProject.Connection, , adLockOptimistic

If myrec.EOF Then
myrec.Close
GoTo TapeCategoryChk
End If

TapeCategoryChk:
stringQry = "Select * from [Tape_DC] where "
stringQry = stringQry & "TapeCategoryID = " & cquot & stringChk & cquot
myrec.Open stringQry, CurrentProject.Connection, , adLockOptimistic

If myrec.EOF Then
myrec.Close
GoTo BackupLocationChk
End If

BackupLocationChk:
stringQry = "Select * from [Tape_DC] where "
stringQry = stringQry & "BackupLocationID = " & cquot & stringChk & cquot
myrec.Open stringQry, CurrentProject.Connection, , adLockOptimistic

If myrec.EOF Then
myrec.Close
GoTo EmployeeChk
End If

EmployeeChk:
stringQry = "Select * from [Tape_DC] where "
stringQry = stringQry & "EmployeeID = " & cquot & stringChk & cquot
myrec.Open stringQry, CurrentProject.Connection, , adLockOptimistic

If myrec.EOF Then
myrec.Close
GoTo RelocationDateChk
End If

RelocationDateChk:
stringQry = "Select * from [Tape_DC] where "
stringQry = stringQry & "RelocationDate = " & cquot & stringChk & cquot
myrec.Open stringQry, CurrentProject.Connection, , adLockOptimistic

If myrec.EOF Then
myrec.Close
GoTo RelocationSiteChk
End If

RelocationSiteChk:
stringQry = "Select * from [Tape_DC] where "
stringQry = stringQry & "RelocationSiteID = " & cquot & stringChk & cquot
myrec.Open stringQry, CurrentProject.Connection, , adLockOptimistic

If myrec.EOF Then
myrec.Close
GoTo CurrentTapeLocationChk
End If

CurrentTapeLocationChk:
stringQry = "Select * from [Tape_DC] where "
stringQry = stringQry & "CurrentTapeLocation = " & cquot & stringChk & cquot
myrec.Open stringQry, CurrentProject.Connection, , adLockOptimistic

If myrec.EOF Then
myrec.Close
GoTo CommentsChk
End If

CommentsChk:
stringQry = "Select * from [Tape_DC] where "
stringQry = stringQry & "Comments = " & cquot & stringChk & cquot
myrec.Open stringQry, CurrentProject.Connection, , adLockOptimistic

If myrec.EOF Then
myrec.Close
GoTo InComingTapeChk
End If

InComingTapeChk:
stringQry = "Select * from [Tape_DC] where "
stringQry = stringQry & "IncomingTape = " & cquot & stringChk & cquot
myrec.Open stringQry, CurrentProject.Connection, , adLockOptimistic

If myrec.EOF Then
myrec.Close
GoTo PendingFill
End If

PendingFill:
Me.Text01.Value = myrec!BarCode.Value
Me.Text02.Value = myrec!TapeDescription.Value
Me.Text03.Value = myrec!BackupDate.Value
Me.Text04.Value = myrec!SeqNumberofTapes.Value
Me.Text05.Value = myrec!JobID.Value
Me.Text06.Value = myrec!SerialNumber.Value
Me.Text07.Value = myrec!TapeCategoryID.Value
Me.Text08.Value = myrec!BackupLocationID.Value
Me.Text09.Value = myrec!EmployeeID.Value
Me.Text10.Value = myrec!RelocationDate.Value
Me.Text11.Value = myrec!RelocationSiteID.Value
Me.Text12.Value = myrec!CurrentTapeLocation.Value
Me.Text13.Value = myrec!Comments.Value
Me.Check01.Value = myrec!IncomingTape.Value


Me.Text01.Value = Null
Me.Text02.Value = Null
Me.Text03.Value = Null
Me.Text04.Value = Null
Me.Text05.Value = Null
Me.Text06.Value = Null
Me.Text07.Value = Null
Me.Text08.Value = Null
Me.Text09.Value = Null
Me.Text10.Value = Null
Me.Text11.Value = Null
Me.Text12.Value = Null
Me.Text13.Value = Null
Me.Check01.Value = False
GoTo EndSeq

Tape_DC:
Me.Text01.Value = myrec!BarCode.Value
Me.Text02.Value = myrec!TapeDescription.Value
Me.Text03.Value = myrec!BackupDate.Value
Me.Text04.Value = myrec!SeqNumberofTapes.Value
Me.Text05.Value = myrec!JobID.Value
Me.Text06.Value = myrec!SerialNumber.Value
Me.Text07.Value = myrec!TapeCategoryID.Value
Me.Text08.Value = myrec!BackupLocationID.Value
Me.Text09.Value = myrec!EmployeeID.Value
Me.Text10.Value = myrec!RelocationDate.Value
Me.Text11.Value = myrec!RelocationSiteID.Value
Me.Text12.Value = myrec!CurrentTapeLocation.Value
Me.Text13.Value = myrec!Comments.Value
Me.Check01.Value = myrec!IncomingTape.Value

EndSeq:

End Sub
 
Nothing

I see that you close the myrec object, but you do not set it to nothing. I don't know if that will help or not, but I had a problem with it once. At the end try:

Set myrec = nothing
Nothing

The following is from the VBA help file:

The Nothing keyword is used to disassociate an object variable from an actual object. Use the Set statement to assign Nothing to an object variable. For example:

Set MyObject = Nothing

Several object variables can refer to the same actual object. When Nothing is assigned to an object variable, that variable no longer refers to an actual object. When several object variables refer to the same object, memory and system resources associated with the object to which the variables refer are released only after all of them have been set to Nothing, either explicitly using Set, or implicitly after the last object variable set to Nothing goes out of scope.
 
Thanks alot, the worked like a charm. I'm sure there is propably something more elaborate I should use. For future reference, if anyone wants to provide that knowledge, I'll gladly take absorb it.

Thanks again.
 
Okay, I'm back again. In my excitement of finally getting something to work, I forgot about the rest of the items I needed for this form. I would also like to be able to search by any and of fields on the form, ie., Tape Description, Employee or 1 of the date fields. Also, I need to have a section for "that item is not found." I tried typing it in but the error points to:

myrec.Open stringQry, CurrentProject.Connection, , adLockOptimistic

on the TapeDescriptionChk: section.
 
Also, save yourself some coding and use this to set all of your textboxes to "".

Dim ctl As Control
' Clear all the TextBoxes on the form.
For Each ctl In Controls
If TypeOf ctl Is TextBox Then
ctl.SetFocus
ctl.Text = ""
End If
Next ctl


You can use the controls tag to tag only those you want to clear and then you could change If TypeOf ctl Is TextBox to

If TypeOf ctl Is TextBox AND ctl.tag="WhatEverYouPutInTheTag" Then
 
You must log in or register to reply here.

Similar threads

T
Help with vBA and Sql
Replies
5
Views
1,575
LPurvis
LPurvis
D
help with this VBA code
Replies
4
Views
1,198
dbaker
D
L
Help Reading in Query and Setting to Variables
Replies
14
Views
2,182
lawsonta
L

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom