disappearing combo box info (2000 all updates)

[THWatson]

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

[johnhutchison]

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

[THWatson]

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

[HansV]

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.

[THWatson]

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

[HansV]

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.

[THWatson]

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

[HansV]

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.

[THWatson]

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

[HansV]

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.

[THWatson]

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

[HansV]

Yep. You can in fact do something like this:

Private Sub Form_Current()
Call Category_AfterUpdate
Call Item_AfterUpdate
End Sub

[THWatson]

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

[THWatson]

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

[johnhutchison]

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.