Auto population of a form from a value in a lookup table? 2

[cstringer]

Hello all,
I have a form that is used to enter/view information in a table. One field on this form is a drop down that selects a name from another table. What I am trying to do is have this name auto-populated with the next user's name in sequence and then loop back to the start when it reaches the last name on the list. This only needs to happen when a new record is created while not affecting the selected names on the existing records.
The Form is called "Data" and the Drop-down control is called "Owner" which pulls its information for the "Owner" table.
Any help would be appreciated!

Thanks,
Chuck

 

[sfm6s524]

Here's a thought, might at least get you going in the right direction. Write a function:

Function GetNextOwner(Frm As Form) As String
Dim FrmRS As Recordset
Dim OwnerRS As Recordset
Dim SQL, Crit As String

Set FrmRS = Frm.RecordsetClone
If FrmRS.RecordCount > 0 Then
FrmRS.MoveLast
SQL = "SELECT Owner.Owner FROM Owner ORDER BY Owner.Owner;"
Set OwnerRS = CurrentDb.OpenRecordset(SQL)
Crit = "[Owner]='" & FrmRS![Owner] & "'"
OwnerRS.FindFirst Crit
If OwnerRS.NoMatch Then
GetNextOwner = "Unknown"
Else
OwnerRS.MoveNext
If OwnerRS.EOF Then
OwnerRS.MoveFirst
End If
GetNextOwner = OwnerRS![Owner]
End If
End If

End Function

Then on your form, under the Before Insert event, put:

Me.txtOwner = GetNextOwner(Me)

where txtOwner is the field you want auto-populated. Of course you may need to change some names above, and add more error checking....

 

[cstringer]

Thanks for the response sfm,
Not sure I'm doing this right, but I'm getting a compile error. Seems to be a problem with the FindFirst method. The error is: Method or Data Member not found on the following line:
OwnerRS.FindNext Crit

Is there any additional help I can get for this?

Thanks!

 

[Golom]

If you have a reference to ADO (as well as DAO) then the "As Recordset" specifications may be defaulting to "As ADODB.Recordset" and ADO doesn't have a "FindFirst" method. Try changing

   Dim FrmRS As Recordset
   Dim OwnerRS As Recordset

To

   Dim FrmRS As DAO.Recordset
   Dim OwnerRS As DAO.Recordset

 

[cstringer]

Hello Golom,

I made the requested changes but now I'm getting a "User-defined type not defined" error. I am relativly new at this and any additional help would be appreciated. Just tell me what you need!
Thanks.

 

[sfm6s524]

Not knowing what version of Access you are using, I'll assume Access 2000. First, the GetNextOwner function should have been placed in a module (menu Insert/Module). Save module. Open module in design mode. Choose from menu, Tools/References. If missing, place check next to:

Microsoft DAO 3.6 Object Library

You version may be different. Click OK to close References form. While still in design mode, choose from menu, Debug/Compile ... No errors should occur. If so, please give more info.

 

[cstringer]

Thanks for the excellent help guys!
sfm, your code worked great but had a little trouble when the set hit the end of the list, all new records started showing "Unknown" in the Owner field but after a little tweaking it is now working perfectly! Hope you enjoy the star!

Thanks!