Two Problems

[IannWard]

Problem 1)


I am trying to edit a field in a table using a subform, in my original table I have a combo box in the field I want to edit, but when in subform view, I don't get the combobox option. Any Ideas?

Problem 2)
As an alternative, I have created a form with a subform and copied a vb script to search and edit the different fields (code below) When I search by ID (primary key) I get a result in my subform which will then allow me to edit fields, including my combobox. However, when I search by serial number, nothing populates the subform, although I know the serial number is in the table. Any ideas please...

Private Sub cmdSearch_Click()
On Error GoTo errr
Me.SerialNumberssubform.Form.RecordSource = "SELECT * FROM SerialNumbers " & BuildFilter
Me.SerialNumberssubform.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
tmp = """"
varWhere = Null

If Me.ID > "" Then
varWhere = varWhere & "[ID] like " & Me.ID & " AND "
End If

If Me.SerialNumber > "" Then
varWhere = varWhere & "[SERIALNUMBER] like " & tmp & Me.SerialNumber & tmp & " AND "
End If

If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function

[June7]

How is the subform set up? Is the SourceObject a form or the table itself?

Step debug the code. Review link at bottom of my post for debugging guidelines. Are variables populated as expected?

Use of LIKE operator makes little sense without use of wildcard. Just use = sign.

Why would you want to search by ID and Serial Number at same time? Are these both unique values for a record? Is ID an autonumber primary key value? Why is user even aware of ID? What if user selects unrelated pair?

[IannWard]

How is the subform set up? Is the SourceObject a form or the table itself?

Step debug the code. Review link at bottom of my post for debugging guidelines. Are variables populated as expected?

Use of LIKE operator makes little sense without use of wildcard. Just use = sign.

Why would you want to search by ID and Serial Number at same time? Are these both unique values for a record? Is ID an autonumber primary key value? Why is user even aware of ID? What if user selects unrelated pair?

Hi and thanks for taking the time to reply.

The source of the subform is the table itself. I copied the code from a youtube tutorial. It is not exactly what I wanted and have tried to edit the best I can to do what I want it to do. The ID search was part of the original code and I will be removing this as I only want to search by serial number. As I mentioned before, if I enter an ID the data is populated to the subform for me to edit, but when I enter a serial number to search for, no results are returned.


I basically want to change the status of a serial number from been in stock to allocated to a store or away for repair for example. I know I can just go to the table to do this, but prefer to use forms from a switchboard, as it looks more professional.

Regards

Iann

[June7]

Have you step debugged the code? What happens when the serial number is input? Follow the code as it executes, one line at a time. Find where it deviates from expectations. Fix code. Debug again. If you still have issues, can attach db to post and I can review it after 3/26. Follow instructions at bottom of my post.