I am still new at access and been struggling for two days. Please see below the code that I have used. It is a bit long winded, and I am not sure if that is causing my problem and that is what takes long to run through, sometimes the Combobox hangs for 2-3 Seconds, as it runs through the code as well as the ListParts Query. What I want to know is, is there a way to get the combobox to preload the query so that when I start to input the number it doesn’t hang? Please note I found some of the code like this but have been adding to it to try and improve the program.

Here is the code for a PartType Combobox that is the first field in the record. Once this is selected also from a drop down then

Private Sub PartType_AfterUpdate()
If Me!PartType.Value = "PARTS" Then
Me!PartNumber.RowSource = "ListPART"
Me!UnitPrice.Locked = True
ElseIf Me!PartType.Value = "LABOUR" Then
Me!PartNumber.RowSource = "ListLABOUR"
Me!UnitPrice.Locked = True
ElseIf Me!PartType.Value = "SUNDRIES" Then
Me!PartNumber.RowSource = "ListSUNDRIES"
Me!UnitPrice.Locked = False
ElseIf Me!PartType.Value = "SUBLET" Then
Me!PartNumber.RowSource = "ListBLANK"
Me!UnitPrice.Locked = False
End If

End Sub

I have also used this code in the Forms Change() Sub

Private Sub Partnumber_Change()
'filter dropbox as you type
    'If Len(PartNumber.Text) > 6 Then
    'Dim rs As Recordset
    'Set rs = CurrentDb.OpenRecordset(RecordSQL & " WHERE Code = '" & PartNumber.Text & "' ORDER BY [Code]")
     
    'If (rs.BOF And rs.EOF) Then 'only requery on no exact match
     '   PartNumber.RowSource = RecordSQL & " WHERE Code Like '*' & PartNumber.text & '*' ORDER BY [Code]"
      '  PartNumber.Dropdown
    'End If
    'End If
End Sub

The added kicker is that I have the following code running as I need to check and see if the part has been superceded. How it works is that it opens another form that populates with the data and then sees if there is an “S” is the supercede Textbox. There can sometimes be that a part has had three or four supercession. Please dont judge me on coding.. found this like this.. Any help would be greatly appreciated.

Private Sub PartNumber_AfterUpdate()

If Me!PartType.Value = "PARTS" Then
'FilterComboAsYouType Me.PartNumber, "SELECT * FROM ListParts", "Code"
'PartNumber.LimitToList = False
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious
'DoCmd.Requery "ListParts"
'PartNumber.LimitToList = True
'DoCmd.GoToRecord , , acNext
'DoCmd.GoToRecord , , acPrevious
DoCmd.Close acForm, "PartPricesSUB"
DoCmd.OpenForm "PartPricesSUB"
Forms!PartPricesSUB.Visible = False
'supercede 1
    If Forms!PartPricesSUB!Supercede.Value = "S" Then
    Me!PartNumber.Value = Forms!PartPricesSUB!SupercedeCode.Value
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "UpdateSupercede1"
    DoCmd.SetWarnings True
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    Forms!PartPricesSUB.Visible = False
    DoCmd.Close acForm, "PartPricesSUB"
    DoCmd.OpenForm "PartPricesSUB"
    Forms!PartPricesSUB.Visible = False
    MsgBox "This part number is a supercession here is the new code", vbOKOnly
    Me!TotalPrice.Value = (Me!Qty.Value * Me!UnitPrice.Value) - ((Me!Qty.Value * Me!UnitPrice.Value) * Me![Discount])
        'supercede 2
    If Forms!PartPricesSUB!Supercede.Value = "S" Then
    Me!PartNumber.Value = Forms!PartPricesSUB!SupercedeCode.Value
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "UpdateSupercede1"
    DoCmd.SetWarnings True
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    Forms!PartPricesSUB.Visible = False
    DoCmd.Close acForm, "PartPricesSUB"
    DoCmd.OpenForm "PartPricesSUB"
    Forms!PartPricesSUB.Visible = False
    MsgBox "This part number is a supercession here is the new code", vbOKOnly
    Me!TotalPrice.Value = (Me!Qty.Value * Me!UnitPrice.Value) - ((Me!Qty.Value * Me!UnitPrice.Value) * Me![Discount])
    End If
        
    'supercede 3
    If Forms!PartPricesSUB!Supercede.Value = "S" Then
    Me!PartNumber.Value = Forms!PartPricesSUB!SupercedeCode.Value
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "UpdateSupercede1"
    DoCmd.SetWarnings True
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    Forms!PartPricesSUB.Visible = False
    DoCmd.Close acForm, "PartPricesSUB"
    DoCmd.OpenForm "PartPricesSUB"
    Forms!PartPricesSUB.Visible = False
    MsgBox "This part number is a supercession here is the new code", vbOKOnly
    Me!TotalPrice.Value = (Me!Qty.Value * Me!UnitPrice.Value) - ((Me!Qty.Value * Me!UnitPrice.Value) * Me![Discount])
    End If
        
    'Supercede 4
    If Forms!PartPricesSUB!Supercede.Value = "S" Then
    Me!PartNumber.Value = Forms!PartPricesSUB!SupercedeCode.Value
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "UpdateSupercede1"
    DoCmd.SetWarnings True
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    Forms!PartPricesSUB.Visible = False
    DoCmd.Close acForm, "PartPricesSUB"
    DoCmd.OpenForm "PartPricesSUB"
    Forms!PartPricesSUB.Visible = False
    MsgBox "This part number is a supercession here is the new code", vbOKOnly
    Me!TotalPrice.Value = (Me!Qty.Value * Me!UnitPrice.Value) - ((Me!Qty.Value * Me!UnitPrice.Value) * Me![Discount])
    End If
            
    'Supercede 5
    If Forms!PartPricesSUB!Supercede.Value = "S" Then
    Me!PartNumber.Value = Forms!PartPricesSUB!SupercedeCode.Value
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "UpdateSupercede1"
    DoCmd.SetWarnings True
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    Forms!PartPricesSUB.Visible = False
    DoCmd.Close acForm, "PartPricesSUB"
    DoCmd.OpenForm "PartPricesSUB"
    Forms!PartPricesSUB.Visible = False
    MsgBox "This part number is a supercession here is the new code", vbOKOnly
    Me!TotalPrice.Value = (Me!Qty.Value * Me!UnitPrice.Value) - ((Me!Qty.Value * Me!UnitPrice.Value) * Me![Discount])
    End If
            
 Else
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    DoCmd.Close acForm, "PartPricesSUB"
    DoCmd.OpenForm "PartPricesSUB"
    Forms!PartPricesSUB.Visible = False
    Me!Description.Value = Forms!PartPricesSUB!Description.Value
    Me!UnitPrice.Value = Forms!PartPricesSUB!UnitPrice.Value
    Me!DscCode.Value = Forms!PartPricesSUB!DscCode.Value
    Me!Qty.Value = "1"
    DoCmd.GoToControl "Qty"
    Me!TotalPrice.Value = (Me!Qty.Value * Me!UnitPrice.Value) - ((Me!Qty.Value *           Me!UnitPrice.Value) * Me![Discount])
    End If

ElseIf Me!PartType.Value = "LABOUR" Then
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious
DoCmd.Close acForm, "PartPricesSUB"
DoCmd.OpenForm "PartPricesSUB"
Forms!PartPricesSUB.Visible = False
Me!Description.Value = Forms!PartPricesSUB!Description.Value
Me!UnitPrice.Value = Forms!PartPricesSUB!UnitPrice.Value
Me!DscCode.Value = Forms!PartPricesSUB!DscCode.Value
Me!Qty.Value = "1"
DoCmd.GoToControl "Qty"
Me!TotalPrice.Value = (Me!Qty.Value * Me!UnitPrice.Value) - ((Me!Qty.Value * Me!UnitPrice.Value) * Me![Discount])
        
    
ElseIf Me!PartType.Value = "SUNDRIES" Then
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious
DoCmd.Close acForm, "PartPricesSUB"
DoCmd.OpenForm "PartPricesSUB"
Forms!PartPricesSUB.Visible = False
Me!Description.Value = Forms!PartPricesSUB!Description.Value
Me!UnitPrice.Value = Forms!PartPricesSUB!UnitPrice.Value
Me!DscCode.Value = Forms!PartPricesSUB!DscCode.Value
Me!Qty.Value = "1"
DoCmd.GoToControl "Qty"
Me!TotalPrice.Value = (Me!Qty.Value * Me!UnitPrice.Value) - ((Me!Qty.Value * Me!UnitPrice.Value) * Me![Discount])

    
ElseIf Me!PartType.Value = "SUBLET" Then
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious
DoCmd.Close acForm, "PartPricesSUB"
DoCmd.OpenForm "PartPricesSUB"
Forms!PartPricesSUB.Visible = False
Me!Description.Value = Forms!PartPricesSUB!Description.Value
Me!UnitPrice.Value = Forms!PartPricesSUB!UnitPrice.Value
Me!DscCode.Value = Forms!PartPricesSUB!DscCode.Value
Me!Qty.Value = "1"
DoCmd.GoToControl "Description"
Me!TotalPrice.Value = (Me!Qty.Value * Me!UnitPrice.Value) - ((Me!Qty.Value * Me!UnitPrice.Value) * Me![Discount])

Else

End If
DoCmd.GoToControl "Qty"
'PartNumber.RowSource = RecordSQL
End Sub