vbYes from Not In List

[acnovice]

Hi,

I have a comboBox for part number and set to LimitToList "Yes".

Following is my NotInList Event Handler. If the part number hasn't registered yet, click Yes, then product registration(PR) form's opened and register part number.

My question is that when the PR form's opened, new part number that was entered from comboBox earlier should be copied to part number field in PR.
Then, I don't need to retype it.

If uMsg() = vbYes Then

DocName = "frmProductReg"
DoCmd.OpenForm DocName, acNormal
DoCmd.GoToRecord acDataForm, DocName, acNewRec

End If

Any help will be appreciated.

 

[THWatson]

acNovice
Here's an alternative for you.

Create a table to hold your part numbers. For sale of example, I will call it tblPartNumbers.

Then put this code on the NotInList event for your combo box.

Private Sub cboYourComboBox_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
   On Error GoTo cboYourComboBox_NotInList_Error

If NewData = "" Then Exit Sub

Msg = """" & NewData & """ is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add this Part Number?"


i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Part #...")
    If i = vbYes Then
      strSQL = "Insert Into tblPartNumbers ([YourPartNumberField]) values ('" & NewData & "')"
           DoCmd.SetWarnings False
           CurrentDb.Execute strSQL
            Response = acDataErrAdded
           DoCmd.SetWarnings True
    Me.cboYourComboBox.Requery
        Else
            Response = acDataErrContinue
    End If

   On Error GoTo 0
   Exit Sub

cboYourComboBox_NotInList_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboYourComboBox_NotInList of VBA Document Form_frmYourFormName"

End Sub

Tom

 

[acnovice]

THWatson,

Thank you for the reply but I need to open PR form(Product Registration)to register a NewPartNumber.

What I want to do is when PR form is opened, new part number is already there and click Save button to register new part number.

I want to avoid retype part number from PR. I don't know what it is exactly but it's a sort of copy function from my comboBox to part number field in PR.

Thank you.

 

[PHV]

If uMsg() = vbYes Then
DocName = "frmProductReg"
DoCmd.OpenForm DocName, acNormal
DoCmd.GoToRecord acDataForm, DocName, acNewRec
DoEvents
Forms(DocName).Controls("P/N control name").Value = NewData
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[acnovice]

PHV,

I tried your code and I'm getting a following error.

Run-time error '3315':
Field 'tblProduct.ShopPN' cannot be a zero-length string.

The "frmProductReg" updates tblProduct.

Thank you for your answer ahead.

 

[formerTexan]

You have a few options. Here are a couple:

1. Open tblProduct in design view and change the ShopPN field's zero Length string property to Yes;

2. Or amend the code slightly from;

If NewData = "" Then Exit Sub

to

If Len(NewData & vbnullstring) = 0 then exit Sub

Cheers,
Bill