List Box not saving to bound field.

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!
 
J

Jeanette Cunningham

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
 
H

h2fcell

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!
Click to expand...


.
Click to expand...
 
J

Jeanette Cunningham

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!
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

List box selection into combo box 2
Combo Box Help 1
multi select list box? 3
Refresh the data in dropdown combo box 7
vbNewLine in memo field bound control 1
How to link form and subform w/ Combo 7
Multi Select List box 8
How to save multiple selection from a list box in a bounded field 1

Top