Find record in table and populate fields on form (1 Viewer)

This is probably one of the stupidest questions to ask but i am lost. this is my delema. I have never made a form that grabs data from a table without actually using the recordset property on the form. I am trying to move up a level by having an unbound text box and grabbing the record I need from that and then populating my form from the data in that certain record. I just don't like the fact that when you pull up a form that uses the recordset property you are pulling up the first record in the table. It just doesn't look very professional to me. The problem that i am having is that i am not too familiar with recordset coding. i looked all around the forum for what i need but can't get it to work.


here is my code for my after update of my text box "txtpartnum"

'Find the record that matches the control.
Dim rs As DAO.Recordset
Dim db As DAO.Database
If partnum.Value <> "" Then
Set db = Application.CurrentDb

Set rs = db.OpenRecordset("labels") 'my table is called labels
rs.FindFirst "[PartNumber] = '" & Me![txtpartnum] & "'" 'partnumber is my PK field in my labels table
Me.Bookmark = rs.Bookmark
If rs.NoMatch Then
Dim msg, style, varchoice
msg = "This record does not exist. Do you want to Make a new record?"
style = vbYesNo
varchoice = MsgBox(msg, style)
If varchoice = vbYes Then
DoCmd.GoToRecord , , acNewRec
txtpart.Value = txtpartnum.Value
Me.Refresh
txtdata.SetFocus

Else
Combo33.Value = txtpart.Value
End If
End If

Else
Exit Sub
End If


I know this is probably so off so please let me know
p.s. i haven't even gotten around to populate the fields on the form. once the record is found. i figured i should do this in baby steps.
Thank you
Sean

Ummmm...I think you are making it a little more difficult than it needs to be.

Also, you can't set a form's bookmark = to a recordset bookmark based on a table (it needs to be a recordset based on the form's recordsetclone).

I don't even know if I understand what you want, but let me take a stab.

When your form opens, you don't want any data displayed. The user enters a number in an unbound textbox, called txtpartnum, and you want to display the data from the label table for that particular part number if it exists, else you want to add a new record and allow the user to enter the data for the new part number. Do I have it? If so, the only problem you're facing, is figuring out how to open the form with no data (which is easily solved by setting the form's DataEntry property to Yes).

As far as finding the particular record, and diplaying the data, you should have bound fields on your form. (I don't know why you want to use unbound)

your code would look like this (I don't know what Combo33 is, so I'm leaving it out

Private Sub txtpartNum_AfterUpdate()
Dim rs as DAO.Recordset
Dim msg, style
If txtPartNum<>"" then
set rs=me.recordsetclone
rs.findfirst "[partnumber]=" & me!txtPartNum
if rs.nomatch then
msg = "This record does not exist. Do you want to Make a new record?"
style = vbYesNo
If MsgBox(msg, style) =vbYes Then
me!PartNumber = txtpartnum
Else
txtPartNum=""
End If
else
me.bookmark=rs.bookmark
End If
EnD IF

With Access, unbound forms are not considered "moving up a level". One of the big benefits of Access is bound forms.

You don't need to go to unbound forms to achieve the results you are looking for. Create a normal bound form. Add an unbound combobox in the form's header. Use this combo to find records. Then base your form on a query that uses the "find" combo as criteria rather than binding the form to a table. You'll need to requery the form after a value has been selected in the combo.

You're always so much better at explanations, Pat!!

that's great! thank you i have been trying to do that for a while. i am not a programmer and I will never claim to be one

thank you both again for your help.