We have a form called "frmScorecard" based on a table called "tblScorecardFinal." When the form loads, it's filtered to load to a specific record. When the form loads, it displays the data that's already been saved to the table. Ok so far.
Now, we have several text boxes with a similar problem - I'll just detail one here. We have a list box called "lstProdBucket2Calls." It's control source is bound to a corresponding field in the underlying table, "ProdBucket2Calls." Now, when the form is opened, there is no data in "ProdBucket2Calls" in the underlying table.
The control's rowsource is set to "SELECT qryBucket1C.Bucket1AHT FROM qryBucket1C; " That query works. The data displays on the form. However, when we close the form the data from the control does not pass & save to the underlying table.
I have experimented with DAO .Edit, "me.lstProdBucket2Calls.requery", and nothing seems to work.
The data type for ProdBucket2Calls in the underlying table is Long Integer. Because the control in this case is a listbox, I do not believe I can specify the data type. The data does display in the list box in the proper format, just doesn't update.
Form_Open does not contain any events that would impact the record, and there are no Before_Update events.
What does the user see in the List Box? Strings? Or Numbers? With a list box you can have several fields (columns), but only one bound column. The type of data in the bound column is what is being saved to the underlying table. How many columns do you have? Which is the bound column (it's in the Properties of the Listbox in the Data tab)? And is the data in that column a Long Integer?
This could be the problem. We chose to go with a listbox (or a combo box) so we could specify a rowsource. The listbox is populated with the rowsource "SELECT qryBucket1C.Bucket1AHT FROM qryBucket1C;" and the listbox itself is locked down so the user can't edit it.
Is there a way we could query the information we wanted to this control as a textbox instead of a listbox? I can easily copy the sql statement into VBA and do a DoCmd.RunSQL, but I'm not sure how to output the result to the textbox.
Would the only option be to run the sql statement and output it directly to the table?
Have you ever used DLookup? Instead of a listbox use a textbox. Then as the Control Source for the textbox set it equal to:
The data will be saved as long as the textbox is bound to the field.
I am not sure how your query works, but DLookup will lookup in a table where a field equals some criteria. You will want to check out the help for the syntax. You can test it out in the Immediate window of the VB editor (2000 or 2002) or use the debug window in 97.
Also keep in mind, as a general rule DLookup can be slow. If your table grows very large you will want to convert it to VB code to look up the value using indexes.
DLookup is an interesting thought. I do use it several other places in my application, but haven't thought about using it to populate this particular control. The underlying query is somewhat complex, so I'll have to explore DLookup's capabilities beyond what I've already done.
One thought is to copy the query's sql statement into a string in vba, then name the string as part of DLookup's criteria.