Form field question

[Guest]

Hi, Im fairly new to Access but I have learned a good bit since I started. I
have a form that looks up part numbers from a query and drops the description
fields (3 of them) into the form. This is then sent to a label printer to
print inventory labels. Everything works well except the ability to enter the
part number. There is a dropdown box in the part number section that displays
all of the parts, but it will not allow me to select them (click on them).
Currently I have to use the next record button at the bottom to select the
part, but I would like the simply enter the part number and have the desc and
other info display.
I saw the post that added a unbound combo box and I did that, but Im not
sure if I have the properties for the field correct. Any help would be
greatly appreciated.

Thanks,

 

[Guest]

First, a little terminology seminar. Forms do not have fields. Fields are
properties of tables and queries. Forms have controls. I know, Microsoft
often refers to them as fields, but Microsoft doesn't really use correct
terminology all the time.

You do want to use an unbound combo box to look up your part number. It
should not be that hard to set up. You will need a row source. A row source
property of a combo box is a lot like the record source of a form. It can be
a table or a query based on a table. Most often you will use a query. In
this case, you will want to select the part number from the table where the
part number is carried. Then to actually make the record for the selected
part the current record for the form, you use the After Update event of the
combo box. The typical code is llike this:

Dim rst As Recordset

Set rst = Me.RecordsetClone

With rst
.FindFirst "[PartNo] = '" & Me.cboPartNo & "'"
If Not .NoMatch Then
Me.BookMark = .BookMark
End If
End With

In the above code, [PartNo] would be the field in the form's record source.
The syntax assumes PartNo is a text field.
Set rst = Nothing

 

[Guest]

Thanks for the detailed description. I found that I didnt have things
correct. My setup is now

Private Sub Combo66_AfterUpdate()
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = '" & Me![Combo66] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This was the code already entered. Looked like I didnt have the field name
correct since it was originally IDPart. There was also a setting that allowed
me to type in the control (Im trying , and your suggestion made the whole
thing work smoothly.

Im very new to this and I have a HUGE amount to learn, but this is great
fun. Thanks again for you help with this issue and thanks for being nice to a
noob

Regards,

First, a little terminology seminar. Forms do not have fields. Fields are
properties of tables and queries. Forms have controls. I know, Microsoft
often refers to them as fields, but Microsoft doesn't really use correct
terminology all the time.

You do want to use an unbound combo box to look up your part number. It
should not be that hard to set up. You will need a row source. A row source
property of a combo box is a lot like the record source of a form. It can be
a table or a query based on a table. Most often you will use a query. In
this case, you will want to select the part number from the table where the
part number is carried. Then to actually make the record for the selected
part the current record for the form, you use the After Update event of the
combo box. The typical code is llike this:

Dim rst As Recordset

Set rst = Me.RecordsetClone

With rst
.FindFirst "[PartNo] = '" & Me.cboPartNo & "'"
If Not .NoMatch Then
Me.BookMark = .BookMark
End If
End With

In the above code, [PartNo] would be the field in the form's record source.
The syntax assumes PartNo is a text field.
Set rst = Nothing
--
Dave Hargis, Microsoft Access MVP

Hi, Im fairly new to Access but I have learned a good bit since I started. I
have a form that looks up part numbers from a query and drops the description
fields (3 of them) into the form. This is then sent to a label printer to
print inventory labels. Everything works well except the ability to enter the
part number. There is a dropdown box in the part number section that displays
all of the parts, but it will not allow me to select them (click on them).
Currently I have to use the next record button at the bottom to select the
part, but I would like the simply enter the part number and have the desc and
other info display.
I saw the post that added a unbound combo box and I did that, but Im not
sure if I have the properties for the field correct. Any help would be
greatly appreciated.

Thanks,