Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    disappearing combo box info (2000 all updates)

    Using Access 2000

    A new wrinkle has developed.

    I have a form called frmCustomers. On this form is a tab page which has fsubProjects, where the bulk of the work is done.

    On fsubProjects, there are 2 additional subforms, fsubMaterials and fsubLabour.

    In fsubMaterials, there are 3 interdependent combo boxes. The user first selects a Category, then an Item which is dependent upon the Category, then an Item Type which is dependent upon the Item selection. This is followed by quantity and cost per unit.

    What is happening is that when I add a new Project, and add the appropriate materials, and click a "Save" button to save the Project, the information that was formerly displayed in the Item and Item Types disappears from view. The data is still there back in the table, but it's disconcerting to have it go away from view.

    It have tried
    Me.fsubMaterials.Requery
    Me.fsubMaterials.Repaint
    Me.fsubMaterials.Refresh

    Me.fsubMaterials.Form.Requery
    Me.fsubMaterials.Form.Refresh
    Me.fsubMaterials.Form.Repaint

    None of these make a difference.

    Any suggestions?

    Tom

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: disappearing combo box info (2000 all updates)

    What is the code behind the Save button? What form is that button on?
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: disappearing combo box info (2000 all updates)

    The Save button is on the fsubProjects form

    code behind the Save button is

    <code>Private Sub cmdSaveProject_Click()

    On Error GoTo Err_cmdSaveProject_Click

    Me.TotalMaterialsCost.Requery

    If Me.TotalMaterialsCost = 0 And Me.txtLabourCost = 0 Then
    Select Case MsgBox("Materials = $0.00 and Labour = $0.00" _
    & vbCrLf & " Do you want to CANCEL this Project?" _
    , vbYesNo Or vbExclamation Or vbDefaultButton1, "Empty project check")
    Case vbYes
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    Me.Undo
    DoCmd.GoToRecord , , acLast
    Forms!frmCustomers!cmdFirstRecord.Enabled = True
    Forms!frmCustomers!cmdPreviousRecord.Enabled = True
    Forms!frmCustomers!cmdNextRecord.Enabled = True
    Forms!frmCustomers!cmdLastRecord.Enabled = True
    Forms!frmCustomers!cmdNewRecord.Enabled = True
    Me.NavigationButtons = True
    Exit Sub
    Case vbNo
    Me.QuoteDate.SetFocus
    End Select
    End If

    If IsNull(Me.CompletionDateDesired) Then
    Me.CompletionDateDesired = DateAdd("d", 30, [QuoteDate])
    End If

    If Me.CompletionDateDesired < Me.QuoteDate Then
    Call MsgBox("Desired completion date cannot be before Quote Date.", vbExclamation, "Desired Completion Date error")
    Me.CompletionDateDesired.SetFocus
    Exit Sub
    End If

    If IsNull(Me.Description) Then
    Call MsgBox("DESCRIPTION OF WORK TO BE DONE has been left blank." _
    & vbCrLf & " Please enter a Description." _
    , vbExclamation, "Description needed")
    Me.Description.SetFocus
    Exit Sub
    End If

    If Me.txtTotalCost <> 0 Then
    If IsNull(Me.Deposit) Then
    Me.txtDeposit = Me.txtTotalCost * 0.5
    Call MsgBox("50% of Project cost has been entered as the Deposit amount." _
    & vbCrLf & " Either leave that amount or edit as desired." _
    , vbExclamation, "Deposit amount check")
    Me.txtDeposit.SetFocus
    End If
    End If


    If IsNull(Me.Location) Then
    Me.Location = [Forms]![frmCustomers]![Address] & IIf(IsNull([Forms]![frmCustomers]![SecondAddress]), " ", ", " & [Forms]![frmCustomers]![SecondAddress] & ", ") & [Forms]![frmCustomers]![City]
    End If

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    Forms!frmCustomers!cmdFirstRecord.Enabled = True
    Forms!frmCustomers!cmdPreviousRecord.Enabled = True
    Forms!frmCustomers!cmdNextRecord.Enabled = True
    Forms!frmCustomers!cmdLastRecord.Enabled = True
    Forms!frmCustomers!cmdNewRecord.Enabled = True
    Me.NavigationButtons = True

    Exit_cmdSaveProject_Click:
    Exit Sub

    Err_cmdSaveProject_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveProject_Click

    End Sub
    </code>

    Tom

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: disappearing combo box info (2000 all updates)

    I'm afraid I can't say anything about this without seeing the database.

    BTW, please replace the outdated DoCmd.DoMenuItem instructions with RunCommand instructions - see Converting DoMenuItem to RunCommand.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: disappearing combo box info (2000 all updates)

    Hans
    I scaled down a copy and zipped it, and it is attached...if you wish to have a look at it.

    There are scads of Items and Item Types, and I have removed most of them. I hope there is enough for you to have a look.

    Thanks.

    Tom
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: disappearing combo box info (2000 all updates)

    Does the problem occur for you in the version you attached? I couldn't reproduce it - the values in the combo boxes remained visible after clicking Save Project.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: disappearing combo box info (2000 all updates)

    Yes, the problem occurs with what I sent.

    I go to a new record on fsubProjects, on the tab page. I add a new record in the Materials subform. After I save it, only the data for Item and Item Type for the particular selection I made in Category stays visible on other Projects.

    Tom

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: disappearing combo box info (2000 all updates)

    I think I know what is happening. In a continuous or datasheet form, there is only one set of controls. If you change the row source of a combo box, it will affect the combo box in all visible records.
    If you select a category in record A, the row source of the item combo box is updated to reflect the choice. You select an item.
    Next, you go to record B and select a different category. This updates the row source of the item combo box for all records, not just record B. The item selected in record A may not be an item in the list any more!

    One thing you need to do is to update the row source of the item combo box in the On Current event of the fSubMaterials form, so that the row source conforms to the value of the category combo box. Similar for the item type combo box.

    That won't prevent the combo boxes from becoming blank. You can't work around this in a datasheet form, but in a continuous form you can place a text box on top of the combo box (not covering the dropdown arrow) that displays the text of the combo box underneath.

  9. #9
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: disappearing combo box info (2000 all updates)

    Hans
    The row source for Item combo box is set on the After Update event for the Category combo box. Similarly, the row source for Item Type is set on the After Update event for the Item combo box. Will that not take care of that? Or is it essential to put additional code on the Current event for the form?

    Your method of switching the Materials subform from Datasheet to Continuous Form view, and then putting a text box over the Item and Item Type combo boxes seems to do the trick...the values seem to display consistently. There is never a problem with the Category combo box because it isn't dependent upon any other selection.

    Tom

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: disappearing combo box info (2000 all updates)

    As the user moves between records in the materials subform, the row source of the item and item type combo boxes doesn't get updated automatically to match the selected value in the "previous" combo box, since the After Update event of those combo boxes only fires when the user selects another item, not when the user moves to a different record.

    You're correct that you don't need a text box over the category combo box - its row source never changes.

  11. #11
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: disappearing combo box info (2000 all updates)

    Hans

    Just to clarify...
    The After Update event on the Category combo box has the following code to set the possibilities for the Item combo box, and the SQL for that is
    <code>sql = "SELECT ItemID, CategoryID, Item " & _
    "FROM tblItems " & _
    "WHERE ([CategoryID] = " & Me!Category.Column(0) & ") " & _
    "ORDER BY tblItems.Item;"
    Me!Item.RowSource = sql</code>

    Are you saying that I should place this exact same code on the Current event for the form?

    Tom

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: disappearing combo box info (2000 all updates)

    Yep. You can in fact do something like this:

    Private Sub Form_Current()
    Call Category_AfterUpdate
    Call Item_AfterUpdate
    End Sub

  13. #13
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: disappearing combo box info (2000 all updates)

    Hans
    Actually, doing as we have been discussing doesn't totally fix things. But there is a solution.

    Behind the Save command button, and following the DoCmd to Save, include
    <code>Dim SQL As String
    Dim SQL2 As String
    SQL = "SELECT ItemID, CategoryID, Item " _
    & "FROM tblItems " _
    & "ORDER BY Item;"
    Me.fsubMaterials.Form!Item.RowSource = SQL

    SQL2 = "SELECT ItemTypeID, ItemID, ItemType " _
    & " FROM tblItemType " _
    & "ORDER BY ItemType;"
    Me.fsubMaterials.Form![Item Type].RowSource = SQL2</code>

    This resets the Item and Item Type combo boxes to their "default" row sources so that all combo box possibilities will display. What will show will be those values that correspond to what was actually selected in the Category combo box.

    Tom

  14. #14
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: disappearing combo box info (2000 all updates)

    Hans
    I have discovered that something unhealthy has crept into the database. It occurs In the database that I sent the other day as an attachment.

    An attempt to enter a new record results in an error message "You can't enter a new record because a related record is required in tblCustomers." But the form is based on tblCustomers.

    Additionally, clicking on a record adds a new Project in fsubProject. And clicking on the Projects tab adds a new Project, even though it contains no data, and no field on that tab page was erntered.

    Do you have any feel for what's causing that, and/or where I look to track it down.

    Tom

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: disappearing combo box info (2000 all updates)

    As soon as you start typing in anything for the new customer, your form is creating a new Project, before the customerID is created.

    If you remove the "check for Referential Integrity" between Projects and Customers the error message will go away. (I am not saying to really remove it, this is just to confirm the cause of the problem.)

    I have found that having default values defined on the form rather than the table can cause new records to get created too early, and you have a couple of them. But I think there may be some code does it as well. As soon as I have typed in a name, and go to the Project page a "Desired Completion Date" is already there.
    Added later
    If you unhide the ProjectID field, you will see that as soon as you click the "new" button the ProjectID field gets a value rather than showing auotnumber.
    Regards
    John



Page 1 of 2 12 LastLast

Posting Permissions

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