Form has record displayed when open

[Guest]

I have a form with a combo box in the header which is used to select a
sampleID, then based on the selection, the form will go to the specified
record and display the info for that record. This works fine, but when I
first open the form although the combo box is empty (until I make a
selection), the other fields have data displayed in them for a certain
record. It is not the first record in the list, nor is it the last record
viewed. It opens to the same record everytime, even if I move to another
record before closing the form.

There is no default value set for any of the controls, and absolutely no
code in the Form Open events, or any other Form events. I've tried setting
all the controls = "" or to null, but I get an error saying I can't set value
for these controls. I wanted all the fields to be empty upon first opening
the form, but at this point I'd settle for getting it to display all the info
for the first record as default. I can get the combo box to display the 1st
item as it's default value, but the rest of the form still displays details
for another record, I can't figure out why it's displaying that record and
how to get rid of it. This is all the code I have in the form:

Private Sub cboSampleID_AfterUpdate()
' Find the record that matches the Sample ID selected in the listbox.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Sample ID] = '" & Me![cboSampleID] & "'"
Me.Bookmark = rs.Bookmark

cmdViewSubtest.SetFocus
End Sub

Private Sub cboSampleID_NotInList(NewData As String, Response As Integer)

MsgBox "The Sample ID you entered could not be found in the database",
vbOKOnly, "Sample ID Not Found!"
Response = acDataErrDisplay
End Sub

Private Sub cmdViewSubtest_Click()
On Error GoTo Err_cmdViewSubtest_Click

DoCmd.OpenForm "fsubSampleSubTestData", acNormal, , [Sample ID] = _
Forms![frmSampleData]![cboSampleID], acFormReadOnly,
acWindowNormal

Exit_cmdViewSubtest_Click:
Exit Sub

Err_cmdViewSubtest_Click:
MsgBox Err.Description
Resume Exit_cmdViewSubtest_Click

End Sub
Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click

DoCmd.Close

Exit_cmdExit_Click:
Exit Sub

Err_cmdExit_Click:
MsgBox Err.Description
Resume Exit_cmdExit_Click

End Sub

 

[Ken Snell [MVP]]

What is the RecordSource of the form? If it's not a query that has a
criterion expression in it that references the combo box as the source of
the comparison value, then the form is opening to the "first" record
("first" being a relative term) because your form was told to get all
records and it will display the "first" one.

 

[Guest]

To do what you want, you will have to use an unbound form. Then, in the
AfterUpdate Event of your combo box, you will have to call a parameter query
that returns only the record you want and code to load the fields from the
query into your text boxes (or other controls).

You will also have to add a SAVE or UPDATE command button and put code in
the Click event to to either add or update the record.

Your problem is why I only use unbound forms.

 

[Guest]

Thanks to both of you, but I finally got it figured out before I got around
to reading your posts, I'll post my solution- just in case someone else runs
into this situation.

The reason I couldn't clear the text boxes was because I forgot that I had
locked each text box becuase I don't want the user to be able to make any
changes it is strictly to look up information, but I couldn't lock the whole
form- because then they wouldn't even able to make a selection from the combo
box. I got that fixed by unlocking the records in each control long enough
to make the selection in the combo box and have all the fields filled in with
the correct details from that record, then locking them after the combo box
selection, then they stay locked until the user enters the combo box to make
a different selection.

And the problem with displaying the wrong record was due to the fact that
the combo box record source was set to a query and even though I had me
selection criteria set to ORDER the items in the combo box correctly- I had
not used any criteria to sort the original query. So the "First record" that
was being displayed in all my text boxes was the details for the first record
in the original query itself before applying the sort criteria in the combo
box.

I'm thinking that unbound forms sounds like a pretty good idea.

I have a form with a combo box in the header which is used to select a
sampleID, then based on the selection, the form will go to the specified
record and display the info for that record. This works fine, but when I
first open the form although the combo box is empty (until I make a
selection), the other fields have data displayed in them for a certain
record. It is not the first record in the list, nor is it the last record
viewed. It opens to the same record everytime, even if I move to another
record before closing the form.

There is no default value set for any of the controls, and absolutely no
code in the Form Open events, or any other Form events. I've tried setting
all the controls = "" or to null, but I get an error saying I can't set value
for these controls. I wanted all the fields to be empty upon first opening
the form, but at this point I'd settle for getting it to display all the info
for the first record as default. I can get the combo box to display the 1st
item as it's default value, but the rest of the form still displays details
for another record, I can't figure out why it's displaying that record and
how to get rid of it. This is all the code I have in the form:

Private Sub cboSampleID_AfterUpdate()
' Find the record that matches the Sample ID selected in the listbox.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Sample ID] = '" & Me![cboSampleID] & "'"
Me.Bookmark = rs.Bookmark

cmdViewSubtest.SetFocus
End Sub

Private Sub cboSampleID_NotInList(NewData As String, Response As Integer)

MsgBox "The Sample ID you entered could not be found in the database",
vbOKOnly, "Sample ID Not Found!"
Response = acDataErrDisplay
End Sub

Private Sub cmdViewSubtest_Click()
On Error GoTo Err_cmdViewSubtest_Click

DoCmd.OpenForm "fsubSampleSubTestData", acNormal, , [Sample ID] = _
Forms![frmSampleData]![cboSampleID], acFormReadOnly,
acWindowNormal

Exit_cmdViewSubtest_Click:
Exit Sub

Err_cmdViewSubtest_Click:
MsgBox Err.Description
Resume Exit_cmdViewSubtest_Click

End Sub
Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click

DoCmd.Close

Exit_cmdExit_Click:
Exit Sub

Err_cmdExit_Click:
MsgBox Err.Description
Resume Exit_cmdExit_Click

End Sub

 

[Guest]

Dear Klatuu,

Can you provide a sample code for how you do this. I have the same issue
and your idea is what I need. Hence, it would save me a big headache instead
of starting from scrtach.

Much appreciated...



--
Jedi022694

To do what you want, you will have to use an unbound form. Then, in the
AfterUpdate Event of your combo box, you will have to call a parameter query
that returns only the record you want and code to load the fields from the
query into your text boxes (or other controls).

You will also have to add a SAVE or UPDATE command button and put code in
the Click event to to either add or update the record.

Your problem is why I only use unbound forms.

I have a form with a combo box in the header which is used to select a
sampleID, then based on the selection, the form will go to the specified
record and display the info for that record. This works fine, but when I
first open the form although the combo box is empty (until I make a
selection), the other fields have data displayed in them for a certain
record. It is not the first record in the list, nor is it the last record
viewed. It opens to the same record everytime, even if I move to another
record before closing the form.

There is no default value set for any of the controls, and absolutely no
code in the Form Open events, or any other Form events. I've tried setting
all the controls = "" or to null, but I get an error saying I can't set value
for these controls. I wanted all the fields to be empty upon first opening
the form, but at this point I'd settle for getting it to display all the info
for the first record as default. I can get the combo box to display the 1st
item as it's default value, but the rest of the form still displays details
for another record, I can't figure out why it's displaying that record and
how to get rid of it. This is all the code I have in the form:

Private Sub cboSampleID_AfterUpdate()
' Find the record that matches the Sample ID selected in the listbox.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Sample ID] = '" & Me![cboSampleID] & "'"
Me.Bookmark = rs.Bookmark

cmdViewSubtest.SetFocus
End Sub

Private Sub cboSampleID_NotInList(NewData As String, Response As Integer)

MsgBox "The Sample ID you entered could not be found in the database",
vbOKOnly, "Sample ID Not Found!"
Response = acDataErrDisplay
End Sub

Private Sub cmdViewSubtest_Click()
On Error GoTo Err_cmdViewSubtest_Click

DoCmd.OpenForm "fsubSampleSubTestData", acNormal, , [Sample ID] = _
Forms![frmSampleData]![cboSampleID], acFormReadOnly,
acWindowNormal

Exit_cmdViewSubtest_Click:
Exit Sub

Err_cmdViewSubtest_Click:
MsgBox Err.Description
Resume Exit_cmdViewSubtest_Click

End Sub
Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click

DoCmd.Close

Exit_cmdExit_Click:
Exit Sub

Err_cmdExit_Click:
MsgBox Err.Description
Resume Exit_cmdExit_Click

End Sub