Data in list boxes disappears when form refreshed

I have two combo boxes and two list boxes on a form.
Each combo box has a RefreshPage Macro run command on it.
I have an query linked to each list box that uses the combo box data as a
filter to return the correct value to the list box.
After selecting an item in the first combo box the value in the first list
box updates, BUT, when I select the value from the second combo box the first
LIST BOX goes blank when the form refreshes.

Is there a way to get this result without using the RefreshPage command? I
would prefer to have the list boxes being text boxes.

Secondly when I try to write the data from the form into a table using the
INSERT INTO SQL function it wont write to the table despite all of the values
in the SQL statement having valid values against them. I have checked and
rechecked the table column data types to ensure they match. Each table field
can even be NULL or ALLOW BLANK etc so even the form being blank should
insert something surely?

On Apr 14, 8:11*am, Tony Kuiper
wrote:
I have two combo boxes and two list boxes on a form.
Each combo box has a RefreshPage Macro run command on it.
I have an query linked to each list box that uses the combo box data as a
filter to return the correct value to the list box.
After selecting an item in the first combo box the value in the first list
box updates, BUT, when I select the value from the second combo box the first
LIST BOX goes blank when the form refreshes.

Is there a way to get this result without using the RefreshPage command? I
would prefer to have the list boxes being text boxes.

Secondly when I try to write the data from the form into a table using the
INSERT INTO SQL function it wont write to the table despite all of the values
in the SQL statement having valid values against them. I have checked and
rechecked the table column data types to ensure they match. Each table field
can even be NULL or ALLOW BLANK etc so even the form being blank should
insert something surely?

Usually you won't need macro to refresh form. It looks like your first
ListBox lose either record source or filter, or it got wrong record
source. Assuming that both combo boxes has ID and text value (ID will
be used to filter ListBoxes) and you don't clear they content, you can
filter ListBoxes in SQL as record source using ID field. Filter for
first ListBox can look like this:

= Forms!myForm!ComboBox1

When user change value in combo box, just refresh the ListBox with
ListBox1.Requery.

For second issue: it appears there is an error, but you don't see it
using this approach. In your code add break point on "INSERT INTO".
Run code and when it stops, use Immediate window to view SQL (assuming
strSQL is a variable which holds SQL command):

? strSQL

Copy SQL into new query and try to run it. You will see the error.

Regards,
Branislav Mihaljev, Microsoft Access MVP

Usually you won't need macro to refresh form. It looks like your first
ListBox lose either record source or filter, or it got wrong record
source. Assuming that both combo boxes has ID and text value (ID will
be used to filter ListBoxes) and you don't clear they content, you can
filter ListBoxes in SQL as record source using ID field. Filter for
first ListBox can look like this:

= Forms!myForm!ComboBox1

When user change value in combo box, just refresh the ListBox with
ListBox1.Requery.

For second issue: it appears there is an error, but you don't see it
using this approach. In your code add break point on "INSERT INTO".
Run code and when it stops, use Immediate window to view SQL (assuming
strSQL is a variable which holds SQL command):

? strSQL

Copy SQL into new query and try to run it. You will see the error.

Regards,
Branislav Mihaljev, Microsoft Access MVP


The ListBox1.Requery was exactly what I was looking for thanks.
I tried to redo the SQL but still get errors. If I hover the mouse over the
various
items in the SQL they values show from the form (though in a few I get the
ID rather than the value that is actually in the combo box or list box)
This is the SQL

Dim SAVE_DRIVER_LOG_RECORD_SQL As String

SAVE_DRIVER_LOG_RECORD_SQL = "INSERT INTO DRIVER_LOG(MEMBER_NAME,
MEMBER_DRIVER_CATEGORY," & _
"ASSET_CODE, LOG_DATE, DRIVE_TIME, DRIVE_NOTES)" & _
"VALUES (' " & MEMBER_NAME_Combo.Value & " ' ,' " & DRIVER_CODE_List.Value &
" ',' " & ASSET_CODE_Combo.Value & " ', ' " & LOG_DATE_Text.Text & " ',' " &
DRIVE_TIME_Text.Text & " ',' " & DRIVE_NOTES_Text.Text & " ')"

Regardless of using the various quotes and apostrophes etc the values still
seem to appear. The table column types have been checked also.

The ListBox1.Requery was exactly what I was looking for thanks.

I managed to crack it!!!
I was putting "too much" information in the VALUES areas