Need Blank Form

[knowshowrosegrows]

I have a simple form where I use a combo box list to choose a record and then
it displays in the form. I want the form and the drop down to be blank when
I open the form. Right now, the drop down shows the first record in the
query that the form uses.

That query is as follows
SELECT tblAgencyShortened.PROV_ID, tblAgencyShortened.Agency, tblType.Type,
tblStaff.StaffName, tblEvents.Event_ID, tblEvents.Reg, tblEvents.Agency_ID,
tblEvents.EventDate, tblEvents.StartTime, tblEvents.Type_ID,
tblEvents.Staff_ID, tblEvents.Notes, tblEvents.EventDescription,
tblEvents.ReportSubmitted
FROM tblType INNER JOIN (tblAgencyShortened INNER JOIN (tblStaff INNER JOIN
tblEvents ON tblStaff.Staff_ID=tblEvents.Staff_ID) ON
tblAgencyShortened.PROV_ID=tblEvents.Agency_ID) ON
tblType.Type_ID=tblEvents.Type_ID;

The code behind the combo box is as follows
Private Sub cmbChooseRecord_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

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

End Sub

Does anyone have a suggestion?

 

[Gina Whipp]

knowshowrosegrows,

Put this on the On_Open event of the form. However, you might want to
switch to an unbound combo box for the look up. Using a bound combo box
will not give you any options because it will be based on the query or table
that your form is drawing from and it too will be on a new record.

DoCmd.GoToRecord , , acNewRec

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[knowshowrosegrows]

Thanks so much for your reply.

I made a mistake in my original description of the design of the form and my
issue.

The dropdown combo box is unbound.

The Row Source for that dropdown reads from a different query than the form :

SELECT qryUpdateForm.Event_ID, qryUpdateForm.Reg, qryUpdateForm.Agency,
qryUpdateForm.EventDate FROM qryUpdateForm ORDER BY Reg, Agency, EventDate
DESC;

The purpose of the form is to edit existing records, not to create new ones.

The issue I have is that when I open the form, the drop down shows the first
field and the first record in the query. So the Event_ID is hidden in the
dropdown, so the first field is Reg. The Reg for the first record in the
query is "5." So when I open the form the dropdown has a "5" in it.

I want it to be blank until I choose a record from the dropdown.
--
Thanks

You all are teaching me so much

knowshowrosegrows,

Put this on the On_Open event of the form. However, you might want to
switch to an unbound combo box for the look up. Using a bound combo box
will not give you any options because it will be based on the query or table
that your form is drawing from and it too will be on a new record.

DoCmd.GoToRecord , , acNewRec

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I have a simple form where I use a combo box list to choose a record and
then
it displays in the form. I want the form and the drop down to be blank
when
I open the form. Right now, the drop down shows the first record in the
query that the form uses.

That query is as follows
SELECT tblAgencyShortened.PROV_ID, tblAgencyShortened.Agency,
tblType.Type,
tblStaff.StaffName, tblEvents.Event_ID, tblEvents.Reg,
tblEvents.Agency_ID,
tblEvents.EventDate, tblEvents.StartTime, tblEvents.Type_ID,
tblEvents.Staff_ID, tblEvents.Notes, tblEvents.EventDescription,
tblEvents.ReportSubmitted
FROM tblType INNER JOIN (tblAgencyShortened INNER JOIN (tblStaff INNER
JOIN
tblEvents ON tblStaff.Staff_ID=tblEvents.Staff_ID) ON
tblAgencyShortened.PROV_ID=tblEvents.Agency_ID) ON
tblType.Type_ID=tblEvents.Type_ID;

The code behind the combo box is as follows
Private Sub cmbChooseRecord_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

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

End Sub

Does anyone have a suggestion?

 

[Gina Whipp]

Did you place DoCmd.GoToRecord , , acNewRec in the On_Open event of your
form?
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Thanks so much for your reply.

I made a mistake in my original description of the design of the form and
my
issue.

The dropdown combo box is unbound.

The Row Source for that dropdown reads from a different query than the
form :

SELECT qryUpdateForm.Event_ID, qryUpdateForm.Reg, qryUpdateForm.Agency,
qryUpdateForm.EventDate FROM qryUpdateForm ORDER BY Reg, Agency, EventDate
DESC;

The purpose of the form is to edit existing records, not to create new
ones.

The issue I have is that when I open the form, the drop down shows the
first
field and the first record in the query. So the Event_ID is hidden in
the
dropdown, so the first field is Reg. The Reg for the first record in the
query is "5." So when I open the form the dropdown has a "5" in it.

I want it to be blank until I choose a record from the dropdown.
--
Thanks

You all are teaching me so much

knowshowrosegrows,

Put this on the On_Open event of the form. However, you might want to
switch to an unbound combo box for the look up. Using a bound combo box
will not give you any options because it will be based on the query or
table
that your form is drawing from and it too will be on a new record.

DoCmd.GoToRecord , , acNewRec

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

in
message news:[email protected]...

I have a simple form where I use a combo box list to choose a record and
then
it displays in the form. I want the form and the drop down to be blank
when
I open the form. Right now, the drop down shows the first record in
the
query that the form uses.

That query is as follows
SELECT tblAgencyShortened.PROV_ID, tblAgencyShortened.Agency,
tblType.Type,
tblStaff.StaffName, tblEvents.Event_ID, tblEvents.Reg,
tblEvents.Agency_ID,
tblEvents.EventDate, tblEvents.StartTime, tblEvents.Type_ID,
tblEvents.Staff_ID, tblEvents.Notes, tblEvents.EventDescription,
tblEvents.ReportSubmitted
FROM tblType INNER JOIN (tblAgencyShortened INNER JOIN (tblStaff INNER
JOIN
tblEvents ON tblStaff.Staff_ID=tblEvents.Staff_ID) ON
tblAgencyShortened.PROV_ID=tblEvents.Agency_ID) ON
tblType.Type_ID=tblEvents.Type_ID;

The code behind the combo box is as follows
Private Sub cmbChooseRecord_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

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

End Sub

Does anyone have a suggestion?