Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Location
    Minneapolis, MN
    Posts
    12

    Unanswered: Upcoming deadline / requery problem

    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.

    Any suggestions?

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    What is the data type for the underlying table? String? Long? What data is in the list box? String?

    Is there any code in the Before Updates event of the form that would modify the field or prevent the changes to be written?

  3. #3
    Join Date
    Apr 2004
    Location
    Minneapolis, MN
    Posts
    12
    Hi DC,

    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.

    Thanks,
    Paul

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    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?

  5. #5
    Join Date
    Apr 2004
    Location
    Minneapolis, MN
    Posts
    12
    Checking the listbox properties, here's what I have:

    Control Source: ProdBucket1AHT
    Row SourceType: Table/Query
    Row Source: SELECT qryBucket1C.Bucket1AHT FROM qryBucket1C;
    Bound Column: 1
    Default Value:
    Validation Rule:
    Validation Text:
    Enabled: Yes
    Locked: Yes

    I do know what you mean regarding the data type, but the option simply isn't present on this listbox.

    In the underlying table, the data should be saved as a Long Integer.

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Show some examples of what you can select in the listbox. For example if I were picking fruit from a list box my choices would be:

    Apple
    Orange
    Banana
    Cherry

  7. #7
    Join Date
    Apr 2004
    Location
    Minneapolis, MN
    Posts
    12
    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?

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I am confused. Does your query only return one value? And is that the value you want to save in the underlying table?

  9. #9
    Join Date
    Apr 2004
    Location
    Minneapolis, MN
    Posts
    12
    Originally posted by DCKunkle
    I am confused. Does your query only return one value? And is that the value you want to save in the underlying table?
    Yes. It only returns the one value, and we need to find a way to save that to the table.

  10. #10
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Have you ever used DLookup? Instead of a listbox use a textbox. Then as the Control Source for the textbox set it equal to:

    =DLookup(Parameters)


    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.

  11. #11
    Join Date
    Apr 2004
    Location
    Minneapolis, MN
    Posts
    12
    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.

    Thanks for the direction!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •