If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Data in list boxes disappears when form refreshed
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 |
#3
|
|||
|
|||
Data in list boxes disappears when form refreshed
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. |
#4
|
|||
|
|||
Data in list boxes disappears when form refreshed
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 |
Thread Tools | |
Display Modes | |
|
|