List Box not saving to bound field.

  • Thread starter Thread starter h2fcell
  • Start date Start date
H

h2fcell

Using Access 2007. I’ll try to describe my issue as detailed as possible.
I’ve got a form with tblComplaints as its “Record Sourceâ€.
One Combo Box cboBookingID, on the form uses the below query as a “Row
Source†and is bound to tblComplaints.BookingID .

SELECT tblCRMBookingIDs.booking_id, tblCRMBookingIDs.BookingDate FROM
tblCRMBookingIDs ORDER BY tblCRMBookingIDs.BookingDate DESC;

I have the following [Event Procedure] on the “After Update†of cboBookingID.

Private Sub cboBookingID_AfterUpdate()
Me.cboServiceIssue.RowSource = "SELECT DISTINCT
tblCRMInvoicedBooking.item_type " & _
"FROM tblCRMInvoicedBooking " & _
"WHERE [booking_id] = [Forms]![frmComplaints]![cboBookingID]
" & _
"ORDER BY tblCRMInvoicedBooking.item_type;"

Me.lboItems.Requery
Me.lboPaxName.Requery
Me.lboDepDate.Requery
End Sub

Three list boxes on the form populate based on the selection in cboBookingID
with “Row Source†queries that look like the below.

SELECT [FirstOfpass_salutation] & " " & [FirstOfpass_fname] & " " &
[FirstOfpass_lname] AS Name FROM tblCRM_PAX WHERE
(((tblCRM_PAX.booking_id)=[Forms]![frmComplaints]![cboBookingID]));

All three list boxes populate correctly.
The problem come up when trying to save the data of two of them that are
bound to the following two fields in tblCRMBookingIDs.
tblCRMBookingIDs.PaxName
tblCRMBookingIDs. USDepDate

When I click a Save Record button with the following code

Private Sub cbSaveRec_Click()
DoCmd.GoToRecord , , acNewRec
Me.lboItems.Requery
Me.lboPaxName.Requery
Me.lboDepDate.Requery
End Sub

The list box data is not saved unless I previously clicked in each list box
before clicking the Save Record button.
I either have a troublesome list box property or need to add additional code
to my Save Record button that’s outside the scope of my knowledge as is
usually the case.

Thanks for the help and Happy Holidays!
 
The list box data is not saved unless I previously clicked in each list box
before clicking the Save Record button.

That is how access works - access does not what row of the listbox to save
unless you click in the listbox.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Hi Jeanette,
My listbox SQL's only return one record. Is there anyway to do the same
thing with a text box?

Jeanette Cunningham said:
The list box data is not saved unless I previously clicked in each list box
before clicking the Save Record button.

That is how access works - access does not what row of the listbox to save
unless you click in the listbox.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

h2fcell said:
Using Access 2007. I'll try to describe my issue as detailed as possible.
I've got a form with tblComplaints as its "Record Source".
One Combo Box cboBookingID, on the form uses the below query as a "Row
Source" and is bound to tblComplaints.BookingID .

SELECT tblCRMBookingIDs.booking_id, tblCRMBookingIDs.BookingDate FROM
tblCRMBookingIDs ORDER BY tblCRMBookingIDs.BookingDate DESC;

I have the following [Event Procedure] on the "After Update" of
cboBookingID.

Private Sub cboBookingID_AfterUpdate()
Me.cboServiceIssue.RowSource = "SELECT DISTINCT
tblCRMInvoicedBooking.item_type " & _
"FROM tblCRMInvoicedBooking " & _
"WHERE [booking_id] = [Forms]![frmComplaints]![cboBookingID]
" & _
"ORDER BY tblCRMInvoicedBooking.item_type;"

Me.lboItems.Requery
Me.lboPaxName.Requery
Me.lboDepDate.Requery
End Sub

Three list boxes on the form populate based on the selection in
cboBookingID
with "Row Source" queries that look like the below.

SELECT [FirstOfpass_salutation] & " " & [FirstOfpass_fname] & " " &
[FirstOfpass_lname] AS Name FROM tblCRM_PAX WHERE
(((tblCRM_PAX.booking_id)=[Forms]![frmComplaints]![cboBookingID]));

All three list boxes populate correctly.
The problem come up when trying to save the data of two of them that are
bound to the following two fields in tblCRMBookingIDs.
tblCRMBookingIDs.PaxName
tblCRMBookingIDs. USDepDate

When I click a Save Record button with the following code

Private Sub cbSaveRec_Click()
DoCmd.GoToRecord , , acNewRec
Me.lboItems.Requery
Me.lboPaxName.Requery
Me.lboDepDate.Requery
End Sub

The list box data is not saved unless I previously clicked in each list
box
before clicking the Save Record button.
I either have a troublesome list box property or need to add additional
code
to my Save Record button that's outside the scope of my knowledge as is
usually the case.

Thanks for the help and Happy Holidays!


.
 
The quickest thing is to use code to select that one record in the listbox.
After you requery the listboxes, you can do

Me.lboItems = Me.lblItems.ItemData(0)
Me.lboPaxName= Me.lboPaxName.ItemData(0
Me.lboDepDate= Me.lboDepDate.ItemData(0)


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

h2fcell said:
Hi Jeanette,
My listbox SQL's only return one record. Is there anyway to do the same
thing with a text box?

Jeanette Cunningham said:
The list box data is not saved unless I previously clicked in each list
box
before clicking the Save Record button.

That is how access works - access does not what row of the listbox to
save
unless you click in the listbox.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

h2fcell said:
Using Access 2007. I'll try to describe my issue as detailed as
possible.
I've got a form with tblComplaints as its "Record Source".
One Combo Box cboBookingID, on the form uses the below query as a "Row
Source" and is bound to tblComplaints.BookingID .

SELECT tblCRMBookingIDs.booking_id, tblCRMBookingIDs.BookingDate FROM
tblCRMBookingIDs ORDER BY tblCRMBookingIDs.BookingDate DESC;

I have the following [Event Procedure] on the "After Update" of
cboBookingID.

Private Sub cboBookingID_AfterUpdate()
Me.cboServiceIssue.RowSource = "SELECT DISTINCT
tblCRMInvoicedBooking.item_type " & _
"FROM tblCRMInvoicedBooking " & _
"WHERE [booking_id] =
[Forms]![frmComplaints]![cboBookingID]
" & _
"ORDER BY tblCRMInvoicedBooking.item_type;"

Me.lboItems.Requery
Me.lboPaxName.Requery
Me.lboDepDate.Requery
End Sub

Three list boxes on the form populate based on the selection in
cboBookingID
with "Row Source" queries that look like the below.

SELECT [FirstOfpass_salutation] & " " & [FirstOfpass_fname] & " " &
[FirstOfpass_lname] AS Name FROM tblCRM_PAX WHERE
(((tblCRM_PAX.booking_id)=[Forms]![frmComplaints]![cboBookingID]));

All three list boxes populate correctly.
The problem come up when trying to save the data of two of them that
are
bound to the following two fields in tblCRMBookingIDs.
tblCRMBookingIDs.PaxName
tblCRMBookingIDs. USDepDate

When I click a Save Record button with the following code

Private Sub cbSaveRec_Click()
DoCmd.GoToRecord , , acNewRec
Me.lboItems.Requery
Me.lboPaxName.Requery
Me.lboDepDate.Requery
End Sub

The list box data is not saved unless I previously clicked in each list
box
before clicking the Save Record button.
I either have a troublesome list box property or need to add additional
code
to my Save Record button that's outside the scope of my knowledge as is
usually the case.

Thanks for the help and Happy Holidays!


.
 
Back
Top