Code problem with list change

[Faray]

Alright hopefully this will be my last problem. I have the search going, and if a user enters a item number it will bring them all up with a list of serial numbers they can choose. When they choose a serial number it should bring up all the info for that serial number.

One of the items that needs to be updated is a checkbox. In normal searches the checkbox works, however in the list change it does not.
Here is the code:

strSQL = "SELECT * FROM PrintTable WHERE SerialNum = " & Me!SNsearch
  If rs!PartMod = True Then
    Me!modbox = True
  Else
    Me!modbox = False
  End If

The field in the table is a yes/no field. When I enter it into a textbox and search it works, it's just when I try to change a list.

Thanks.

 

[pbaldy]

Is that all the code? You haven't opened the recordset.

 

[Faray]

Nah that's just the parts I'm having trouble with. The rest of the code works (I did add the recordset too, I have Set rs = db.OpenRecordset(strSQL) )

 

[Scooterbug]

You should post the full code...helps with troubleshooting

 

[Faray]

You should post the full code...helps with troubleshooting

Ok here it is then:

Private Sub snlist_Change()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
' Search for when an item on the list is changed
If switch2.Enabled = True Then
 If IsNull(SNlist) Then
   Exit Sub
 End If
  SNsearch = SNlist
  OrderSearch = ""
  PQ.Requery
  OrderSearch = OrderNum.Value
  strSQL = "SELECT * FROM PrintTable WHERE SerialNum = " & Me!SNsearch
  Set rs = db.OpenRecordset(strSQL)
  SElist = rs![Part#]
  rp = rs!reprintnum
  OrderSearch = rs![OrderNum]
  MsgBox (rs!PartMod)
  If rs!PartMod = True Then
    Me!modbox = True
  Else
    Me!modbox = False
  End If
  SElist.Enabled = False
  modbox.Enabled = False
  switch.Enabled = False
  PQ.Visible = True
  SNlist.Visible = True
  Snlabel.Visible = True
  Me.modbox = False
End If
End Sub

Everything else in the code works, except for the modbox true or false. If there are any questions let me know.

 

[Scooterbug]

Well, after a quick look...

If IsNull(SNlist) Then
   Exit Sub

SNlist isn't defined nor do you give it any value prior to the If statement. So I'm guessing that it's always null...so the sub exits.

 

[Faray]

That is there incase someone enters a number into the list that does not exsist in the table so there isn't a search error. Rather than brining up the error it just exits the sub.

 

[Scooterbug]

I understand why it's there...but the way you have it written it's acting like a variable.

Have you tried compiling the code with Option Explicit declared? I notice that you have things like

If switch2.Enabled = True Then

As far as I know, if you are referencing controls on a form, you need to include Me. to it.

 

[pbaldy]

So everything works but that? Does it error, or? What does the message box return? Have you set a breakpoint and stepped through it?

 

[Faray]

The message box returns true and false, if it is true and false. The checkbox always is unchecked though, and just in this area. I started taking parts away and I am currently working on that now trying to figure out why it doesn't work here but everywhere else.

 

[pbaldy]

Is that the name of the checkbox, the field, or both? Does the enabled line below work for the checkbox? If so, does this work?

modbox = rs!PartMod

or my preference, presuming that's the checkbox name:

Me.modbox = rs!PartMod

 

[Faray]

Is that the name of the checkbox, the field, or both? Does the enabled line below work for the checkbox? If so, does this work?

modbox = rs!PartMod

or my preference, presuming that's the checkbox name:

Me.modbox = rs!PartMod

After redoing parts of the code, and using your code instead of mine I got it to work. I'm not sure if it was the new code, or becuase of something I changed in the second try, but it works now. Thanks for your help