Find record on unbound form

[Guest]

I'm trying to search for a record on an unbound form. The code that works
*if* my form was bound is as follows:

Dim rs As DAO.Recordset

If Not IsNull(Me.cbo_Find) Then
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Training_ID] = " & Me.cbo_Find
If rs.NoMatch Then
MsgBox "Record not found."
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

How do I code something similar for an unbound form?

Thanks,
Melanie

 

[Allen Browne]

You cannot have multiple records in an unbound subform, so presumably you
will have to visit each control in turn (For...Each... loop), discover
whether it has a Value (some controls don't), and if so examine the value to
find what you want.

 

[Tim Ferguson]

How do I code something similar for an unbound form?

An unbound form has, by definition, no recordset and therefore no records.
What exactly are you trying to achieve?

Tim F

 

[Guest]

Tim,

Thanks for the note. I would like to use a combo box at the top of the form
to select and edit a specific record on my unbound form. It's identical to
the combo box wizard the selects specific data to display, but I can't use
that because the form is unbound. Any ideas?

Melanie

 

[Guest]

Hi Allen. Thanks for the response. Sorry, I didn't get it, though. I only
have one record on my form at a time. I just want to be able to use a combo
box to select the record and display it on the form so I can edit it. Is
there any easier way to achieve this?

Melanie

You cannot have multiple records in an unbound subform, so presumably you
will have to visit each control in turn (For...Each... loop), discover
whether it has a Value (some controls don't), and if so examine the value to
find what you want.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm trying to search for a record on an unbound form. The code that works
*if* my form was bound is as follows:

Dim rs As DAO.Recordset

If Not IsNull(Me.cbo_Find) Then
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Training_ID] = " & Me.cbo_Find
If rs.NoMatch Then
MsgBox "Record not found."
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

How do I code something similar for an unbound form?

Thanks,
Melanie

 

[Allen Browne]

Sorry, Melanie, I'm not with you.

If the form is unbound, it has no record.
Therefore you cannot select a record.

1. Open your form in design view.

2. Open the Properties box (View menu).

3. Make sure the title of the Properties box says "Form", so you are not
looking at the properties of a section or control.

4. On the Data tab of the Properties box, what is beside the RecordSource
property?

If that property is blank the form is unbound.
Therefore it has no records, and so you cannot select a record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen. Thanks for the response. Sorry, I didn't get it, though. I
only
have one record on my form at a time. I just want to be able to use a
combo
box to select the record and display it on the form so I can edit it. Is
there any easier way to achieve this?

Melanie

You cannot have multiple records in an unbound subform, so presumably you
will have to visit each control in turn (For...Each... loop), discover
whether it has a Value (some controls don't), and if so examine the value
to
find what you want.

I'm trying to search for a record on an unbound form. The code that
works
*if* my form was bound is as follows:

Dim rs As DAO.Recordset

If Not IsNull(Me.cbo_Find) Then
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Training_ID] = " & Me.cbo_Find
If rs.NoMatch Then
MsgBox "Record not found."
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

How do I code something similar for an unbound form?

Thanks,
Melanie

 

[Allen Browne]

Sure. You could open a recordset using criteria that limit what you get,
e.g.:
Dim strSql As String
strSql = "SELECT * FROM tbl_Main_Table WHERE Class_ID = 99;"
Set rs = db.OpenRecordset(strSql)
If rs.RecordCount = 0 Then
MsgBox "Not found"
Else
'do your stuff

But if you are going to load all the boxes in the form based on the found
record, and then have to write it manipulate the data back into the table
again later by executing an Update/Append/Delete query as appropriate, why
not use a bound form?

If you are trying to write client/server stuff, how about binding the form
to a single record?

Private Sub txtGoto_AfterUpdate()
Dim strSql As String
If Not IsNull(Me.txtGoto) Then
strSql = "SELECT * FROM tbl_Main_Table WHERE Class_ID = " &
Me.txtGoto & ";"
Me.RecordSource = strSql
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ok, now I see what you are saying (duh!). But to really drive the point
into
the ground, I'll see if this makes any sense.

There are unbound text boxes on the form that correspond to fields in a
table. When the 'save record' button is clicked, the following code is
executed:

Dim iItem As Integer
Dim lngEmpTraining As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb

' Open a Recordset based on the table
Set rs = db.OpenRecordset("tbl_Main_Table", dbOpenDynaset)
With Me!lst_Emp
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngEmpTraining = .Column(0, iItem)

' Determine whether this EmployeeID-TrainingID combination is
in
the table
rs.FindFirst "[Class_ID] = " & Me.cbo_Class_ID & " AND
[Employee_ID] = " & lngEmpTraining & _
" AND [Training_Date] = " & Me.Training_Date

If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it

rs.AddNew
rs!Class_ID = Me.cbo_Class_ID
rs!Comment = Me.Comment
rs!Training_Date = Me.Training_Date
rs!Hours = Me.Hours
rs!Instructor_ID = Me.cbo_Instructor
rs!Employee_ID = lngEmpTraining
rs!User = Me.txt_user
rs!Time_Stamp = Now()
rs.Update

End If

' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
rs.Delete ' delete this record if it's been deselected
End If
' if it was selected, leave it alone
End If
Next iItem
End With

rs.Close

Set rs = Nothing
Set db = Nothing

Given this code, and the knowledge that the text boxes are used for
getting
data into tbl_Main_Table from the form, can I open a recordset based on
tbl_Main_Table to search for a specific record from that table and display
it
on the form?

Mel

Sorry, Mel, I have no idea what you are talking about.

To me your question sounds like, "How do you search for a car on an
island
where there are no cars?" If the form has no records, you cannot
meaningfully search for one.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

There is nothing next to the RecordSource property. I understand that
the
form has no records. So, my question is, how does one search for a
record
on
an unbound form? Sorry to be such a pain.

Thanks for hanging in there with me,
Melanie

:

Sorry, Melanie, I'm not with you.

If the form is unbound, it has no record.
Therefore you cannot select a record.

1. Open your form in design view.

2. Open the Properties box (View menu).

3. Make sure the title of the Properties box says "Form", so you are
not
looking at the properties of a section or control.

4. On the Data tab of the Properties box, what is beside the
RecordSource
property?

If that property is blank the form is unbound.
Therefore it has no records, and so you cannot select a record.


Hi Allen. Thanks for the response. Sorry, I didn't get it, though.
I
only
have one record on my form at a time. I just want to be able to use
a
combo
box to select the record and display it on the form so I can edit
it.
Is
there any easier way to achieve this?

Melanie

:

You cannot have multiple records in an unbound subform, so
presumably
you
will have to visit each control in turn (For...Each... loop),
discover
whether it has a Value (some controls don't), and if so examine the
value
to
find what you want.

I'm trying to search for a record on an unbound form. The code
that
works
*if* my form was bound is as follows:

Dim rs As DAO.Recordset

If Not IsNull(Me.cbo_Find) Then
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Training_ID] = " & Me.cbo_Find
If rs.NoMatch Then
MsgBox "Record not found."
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

How do I code something similar for an unbound form?

Thanks,
Melanie