disappearing combo box information on subform 1

[THWatson]

Using Access 2000

I have a form "frmCustomers." On the form is a tab control page which has a subform "fsubProjects." On fsubProjects is an additional subform "fsubMaterials."

On fsubMaterials there are 3 dependent combo boxes, Category, Item and [Item Type]. When I make a selection in Category then Item possibilities are dependent upon the selection in Category. Likewise, when a selection is made in Item then [Item Type] possibilities are dependent upon the selection in Item.

All of this works fine.

The problem occurs when a Project is saved. Then the information in the Item combo box and the [Item Type] combo box disappear from the screen. Only the Category remains.

The data is still there, of course, but won't display until fsubProjects is closed and reopened.

I have tried a number of ways to get that information to continue to display - Repaint, Requery, Refresh. I have also tried referring to the subform control like this...
Me.fsubMaterials.Form!Item.RowSource = Me.fsubMaterials.Form!Item.RowSource

I also tried putting this latter code on the After Update event of fsubMaterials itself.

Any suggestions as to how I can get this information to stay displayed on the screen?

Thanks.

Tom

 

[jazzz]

You do have the subforms linked with a child master field I assume to your main table where you are storing the info.

Life's a journey enjoy the ride...

jazzz

 

[THWatson]

Yes
They are linked by a ProjectID field.

There is no trouble in saving the data. It's a matter of not displaying after a New Record is saved.

Tom

 

[TheAceMan1]

Howdy THWatson . . .

How are you saving?

and

Are the comboboxes bound? . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[THWatson]

TheAceMan1
There is a Save button that saves the record.

The combo boxes are bound to fields in tblMaterials.

Tom

 

[THWatson]

This is the code behind the Save button on fsubProjects.

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.RunCommand acCmdSelectRecord
        DoCmd.RunCommand acCmdDeleteRecord
        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.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

DoCmd.RunCommand acCmdSaveRecord
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

Me.fsubMaterials.Form!Item.Requery
Me.fsubMaterials.Form![Item Type].Requery

Exit_cmdSaveProject_Click:
    Exit Sub

Err_cmdSaveProject_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveProject_Click
    
End Sub

This is code behind the Before Update event on fsubProjects

Private Sub Form_BeforeUpdate(Cancel As Integer)

   On Error GoTo Form_BeforeUpdate_Error

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")
Cancel = True
Me.CompletionDateDesired.SetFocus
Exit Sub
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

   On Error GoTo 0
   Exit Sub

Form_BeforeUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate of VBA Document Form_fsubProjects"

End Sub

This is code behind the Current Event on fsubProjects

Private Sub Form_Current()
Dim SQL As String
Dim SQL2 As String

If Me.NewRecord Then
Me.cmdUndo.Enabled = True
Me.cmdDelete.Enabled = True
Else
Me.cmdUndo.Enabled = False
Me.cmdDelete.Enabled = False
End If

If Me.NewRecord Then
Dim curTemp As Currency
Dim curTemp2 As Currency
curTemp = DLookup("PSTRate", "tblPST", "#" & Me.QuoteDate & "# Between StartDate And EndDate")
curTemp2 = DLookup("GSTRate", "tblGST", "#" & Me.QuoteDate & "# Between StartDate And EndDate")
Me.CurPSTRate = curTemp
Me.CurGSTRate = curTemp2
Forms!frmCustomers!cmdFirstRecord.Enabled = False
Forms!frmCustomers!cmdPreviousRecord.Enabled = False
Forms!frmCustomers!cmdNextRecord.Enabled = False
Forms!frmCustomers!cmdLastRecord.Enabled = False
Forms!frmCustomers!cmdNewRecord.Enabled = False
Me.NavigationButtons = False
End If

Me.QuoteDate.SetFocus
Me.fraDisplay = Null

If Me.Accepted = True Then
SQL = "SELECT tblMaterials.Category, tblMaterials.Item, tblMaterials.ItemType, tblMaterials.ProductCode, tblMaterials.Quantity, tblMaterials.ItemCost, tblMaterials.MaterialID, tblMaterials.ProjectID, tblMaterials.Invoice " _
& "FROM tblMaterials " _
& "WHERE (((tblMaterials.Invoice)=Yes));"

SQL2 = "SELECT tblLabour.LabourID, tblLabour.WorkDate, tblLabour.ProjectID, tblLabour.LabourRate, tblLabour.Hours, tblLabour.Invoice " _
& "FROM tblLabour " _
& "WHERE (((tblLabour.Invoice)=Yes));"

Me.cmdCreateInvoiceDetails.Enabled = False
Me.optQuote.Enabled = False
Me.optInvoice.Enabled = True
Me.optBoth.Enabled = True
Me.lblCalculateDeposit.Visible = False
Me.txtDeposit.Visible = False
Me.cmdPreviewInvoice.Enabled = True
Me.cmdPrintInvoice.Enabled = True

Else
SQL = "SELECT tblMaterials.Category, tblMaterials.Item, tblMaterials.ItemType, tblMaterials.ProductCode, tblMaterials.Quantity, tblMaterials.ItemCost, tblMaterials.MaterialID, tblMaterials.ProjectID, tblMaterials.Invoice " _
& "FROM tblMaterials " _
& "WHERE (((tblMaterials.Invoice)=No));"

SQL2 = "SELECT tblLabour.LabourID, tblLabour.WorkDate, tblLabour.ProjectID, tblLabour.LabourRate, tblLabour.Hours, tblLabour.Invoice " _
& "FROM tblLabour " _
& "WHERE (((tblLabour.Invoice)=No));"

Me.optQuote.Enabled = True
Me.optInvoice.Enabled = False
Me.optBoth.Enabled = False
Me.cmdCreateInvoiceDetails.Enabled = True
Me.lblCalculateDeposit.Visible = True
Me.txtDeposit.Visible = True
Me.cmdPreviewInvoice.Enabled = False
Me.cmdPrintInvoice.Enabled = False

End If

Me.fsubMaterials.Form.RecordSource = SQL
Me.fsubLabour.Form.RecordSource = SQL2
Me.fsubMaterials.Form.Requery

End Sub

Tom

 

[TheAceMan1]

THWatson . . .

The problem is your filtering (via a where clause in the [blue]RowSource[/blue] of the combo's . . . or however else your doing it). Meaning, all values in the combo's [blue]Item[/blue] & [blue]Item Type[/blue] are not included! . . . [purple]Can you see it![/purple]

[blue]What does Access do for those records who's selections are now not included in the [blue]RowSource[/blue] . . . [purple]considering, the current [blue]RowSource[/blue] is all thats available for every combo in every record![/purple][/blue]

?
Answer . . . It shows a blank!

Under present conditions, I'd expect, as you navigate thru projects, materials will be peppered with blanks!

What you need is [blue]a method to show all[/blue] in the combo's after a project is saved.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[THWatson]

AceMan
I understand what you mean. Although I'm not exactly clear how to fix it.

The reason I have the code on the Current Event is this...
When the customer Accepts the Project Quote, the rows from Materials and Labour are duplicated in the same table and the Invoice (a Y/N field) is switched to Yes for the duplicated rows. This is to preserve the integrity of the original Quote but also allow for Invoiced rows to be edited, deleted, added to, or adjusted however.

If the Project has been Accepted, I want to show only the Invoice = Yes rows in Materials and Labour; otherwise I want the Quote rows to show. That's the reason for the Where clause in the RowSource.

The Materials, fsubMaterials, and Labour, fsubLabour, are in Datasheet view. Would things work better if I were to switch those subforms to Continuous forms?

Tom

 

[THWatson]

TheAceMan1

One method would be to change fsubMaterials from Datasheet to Continuous Form view. Then, put a text box over the Item combo box that displays the value beneath it, and the same with the Item Type combo box. The Category combo box, since it is not dependent upon anything else always displays.

Is this a reasonable fix?

Tom

 

[TheAceMan1]

THWatson . . .

The best I can offer involves the Save Button. For the combo's [blue]Item[/blue] & [blue]Item Type[/blue], update their [blue]RowSource[/blue] with [purple]SQL that doesn't include any [blue]Where Clause[/blue] (AKA the full array of selections are available)[/purple].

This way all combo selections are available to show for any and all records! . . . and your filtering can still be accomplished when the user modifies the selections for a new or previously saved record! . . .

Be sure you fully understand my prior post! If there's any doubt, let me know and I'll go into deeper detail.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[THWatson]

There are only 20 Categories, but there are 263 Items and 937 Item Types.

In the Materials subform "fsubMaterials," the After Update event on the Category combo box has the following SQL to restrict the Item combo box to only those values which are appropriate for the selection in Category.

Private Sub Category_AfterUpdate()
Dim sql As String
   
   sql = "SELECT ItemID, CategoryID, Item " & _
         "FROM tblItems " & _
         "WHERE ([CategoryID] = " & Me!Category.Column(0) & ") " & _
         "ORDER BY tblItems.Item;"
   Me!Item.RowSource = sql

End Sub

The After Update event for combo box Item has the following SQL to restrict the selections in Item Type to those that are appropriate for the selection made in the Item combo box.

Private Sub Item_AfterUpdate()
Dim sql2 As String
   
   sql2 = "SELECT ItemTypeID, ItemID, ItemType " & _
         "FROM tblItemType " & _
         "WHERE ([ItemID] = " & Me!Item.Column(0) & ") " & _
         "ORDER BY tblItemType.ItemType;"
   Me![Item Type].RowSource = sql2

End Sub

So I guess I am not fully understanding what additional code you want me to put on the Save command in fsubProjects.

I think I have been looking at this for so long that I am getting lost in the forest.

Sorry for the "thickness of head." Maybe it's just because it's Saturday evening and where I live we're getting hit with a blizzard.

Tom

 

[TheAceMan1]

THWatson . . .

As I said before . . . its the [blue]Where Clauses[/blue] of the combo's you've shown thats causing the problem (counts are academic here). [blue][blue]Data is being excluded[/blue], compared to a full listing![/blue] Any record with selections involving the excluded data can only show a blank!

Consider your default [blue]Row Sources[/blue] of the combo's, which I'm sure have [blue]no criteria![/blue] This is the reason why when you close & reboot the form, all is OK. The default takes over and the full array of selections are available to be displayed.

However you decide to do it, you need to get the [purple]Default SQL's[/purple] back into their respective [blue]Row Source[/blue] . . . [purple]just as a matter of display![/purple]

I'm sure you can do it! . . .

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[THWatson]

I have a feeling that you finally got through to me. So let's see if I have it right (at least, it seems to work).

Behind the Save button in fsubProjects I included the following code...

Dim SQL As String
Dim SQL2 As string

SQL = "SELECT tblItems.ItemID, tblItems.CategoryID, tblItems.Item " _
& "FROM tblItems " _
& "ORDER BY tblItems.Item;"
Me.fsubMaterials.Form!Item.RowSource = SQL

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

Thanks for your patience. Your words "I'm sure you can do it!" were most encouraging!

Tom

 

[TheAceMan1]

[blue] . . . So let's see if I have it right (at least, it seems to work).[/blue]

Either it works or it doesn't!

As a matter of making more readable SQL's, just make a comparsion with the following:

[blue]   Dim SQL As String
   
   SQL = "SELECT ItemID, CategoryID, Item " & _
         "FROM tblItems " & _
         "ORDER BY Item;"
   Me.fsubMaterials.Form!Item.RowSource = SQL
   
   SQL = "SELECT ItemTypeID, ItemID, ItemType " & _
         "FROM tblItemType " & _
         "ORDER BY tblItemType.ItemType;"
   Me.fsubMaterials.Form![Item Type].RowSource = SQL2[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[THWatson]

Yep. I agree.

I just copied over the SQL that Access prepared behind the combo box and put in my own line continuations.

Thanks again!!!

Tom

 

[THWatson]

TheAceMan1
With further testing, I find that following the 2 SQL statements, to make all Item and Item Type possibilities available in the combo boxes, I have to add one additional line.

Me.fsubMaterials.Form.Requery

Also, I have to add the two SQL statements to the Current event for fsubMaterials, as follows

SQL = "SELECT ItemID, CategoryID, Item " & _
         "FROM tblItems " & _
         "ORDER BY Item;"
   Me.Item.RowSource = SQL
   
   SQL2 = "SELECT ItemTypeID, ItemID, ItemType " & _
         "FROM tblItemType " & _
         "ORDER BY tblItemType.ItemType;"
   Me.[Item Type].RowSource = SQL2

Tom