Using a recordset to fill text boxes with field values

[Bada bing!]

Hi guys,

This is my first post of hopefully many, and I hope to be able to keep visiting and helping others in the future.

I'm building a database at the moment which I've done basing forms on Queries as I'm very familiar with doing so. However, one form in the database if a bit different.

The form in question is for shipping off refurbished units at the warehouse, by adding a SHIP ID to the record for each unit scanned in. The form before creates a recird ubdexed by a SHIP ID in the SHIPPING table, along with some other info such as delivery address and ship date. Each unit will be assigned this SHIP ID by the child form I wish to create, by scanning the Serial Number against it.
Two actions are done on the child form:

• Read in Serial Number of unit, find record in MAIN table and display key fields such as the units status (i.e. "WIP" or "REPAIRED" into text boxes on the form
• Allow unit to be shipped (by assigning the SHIP ID to the unit's record in the main table, only if the STATUS field shows as "REPAIRED"

Now, because I want to scan units into the form's Serial Number textbox and do the STATUS field check, then assign the SHIP ID; I need a recordset, right? I've inherited development of datdbases which used recordsets in their forms before, but have never had to create one from scratch. Also I wasn't able to take a copy of the source code of those databases before I left my last company.

Can anyone give me some info on what the bare minimum is to create a recordset, and to use it to save the record once I've made changes.

For your info I would consider myself an Access intermediate, and I've worked with it in various roles and versions for around 5 years.

Thanks,
Gareth

 

[Pat Hartman]

Nothing in your post indicates to me that you need to write code. Won't a bound form work for you?

The RecordSource for a form or report can be a table or a query (preferable) or an SQL String.

 

[WayneRyan]

Gareth,

Use the Search Facility here and look for "unbound". You'll see that
people have a lot of problems (challenges?) with them. Essentially,
you're foregoing all that Access offers with respect to synchronizing
your data & forms. You don't really want to go down that road unless
you have to & you'll be prepared to write lots of code.

You can also use the Search Facility here and look for "Search". I
think maybe some of the examples of how people have taken "bound"
forms and tied Search capabilities to them is what you really want.
Also, look at the Sample Database Forum here (more examples).

After some research, if you really want to pursue "unbound" forms,
we'll start over.

hth,
Wayne

 

[Bada bing!]

Thanks guys. I'd certainly prefer not to go down the recordset route, because it's not something I'm familiar with, and like you've said it does tend to go away from what Access does best.

I'll take a new approach then, and only have one (unbound) text box on the form called txtTRACKER_ID. I'll base the form on a query and then allow the user to scan in the TRACKER_ID of the unit, and make the form search the query for the relevant ID, where I can do my field checks/updates.

How do I do that then without using the standard crappy Find function which brings up a subform?

EDIT: From doing another search I've built this little bit of code that sits on a command button to be clicked after the user enters the TRACKER_ID:

Private Sub Command8_Click()
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[TRACKER_ID] = " & Str(Nz(Me![txtTRACKER_ID], 0))
    If Not rs.EOF Then
    Me.Bookmark = rs.Bookmark
    End If

End Sub

In all cases, it finds the correct record and I've added a textbox to the form, and assigned a field value to it for testing and it worked fine. How can I record a search miss though, if a search value isn't found?

 

[alastair69]

Thanks guys. I'd certainly prefer not to go down the recordset route, because it's not something I'm familiar with, and like you've said it does tend to go away from what Access does best.

I'll take a new approach then, and only have one (unbound) text box on the form called txtTRACKER_ID. I'll base the form on a query and then allow the user to scan in the TRACKER_ID of the unit, and make the form search the query for the relevant ID, where I can do my field checks/updates.

How do I do that then without using the standard crappy Find function which brings up a subform?

You could use the following:
=DLookUp("[BankName]","My Company Information")

The item in [] is the field and the item in "" is the table.

Alastair Lane

 

[WayneRyan]

Gareth,

You can also base your form on a query. The criteria for the PK field is:

Forms![YourMainForm]![YourUnboundField]

Then, in the unbound field's AfterUpdate: Me.ReQuery

You can give the unbound field an appropriate DefaultValue.

Wayne

 

[Pat Hartman]

Perhaps the control should be a combo rather than a textbox.