NotInList VBA Code Problem

[roh710]

Hi Everyone,

I have a form with combobox which has a corresponding customer part number for our own partnumber.

When a customer part number which does not exist in the combobox is entered, the "AddNewCustPartNo" form will pop up. The idea is to have the NewData(the new customer part number) already filled in when the form pops up and fill in the rest of the form manually.

However, when the AddNewCustPartNo pops up, the field is not filled in with the NewData. Below is my code and can anyone tell me what's wrong with the code?

------------------------------------------

Private Sub CustPartNo_NotInList(NewData As String, Response As Integer)
Dim ButtonClicked
ButtonClicked = MsgBox("The customer part number " & _
NewData & " , doe not exist. Do you wish to add " & _
NewData & " to the customer's part list?", vbYesNo)
If ButtonClicked = vbNo Then
CustPartNo.Undo
Response = acDataErrContinue
Else
 
Response = acDataErrAdded
DoCmd.OpenForm "AddNewCustPartNo", , , , acAdd, acDialog, NewData
CustPartNo.Undo
CustPartNo.Requery
CustPartNo = DLookup("CustPart_ID", "CustPartNo", "CustPart_No= '" & NewData & "'")
End If
End Sub

 

[Beetle]

You're passing NewData to the "AddNew..." form via OpenArgs, but are you handling it properly in the open event of the "AddNew.." form? In other words, you can't just pass it, you have to tell the other form what to do with it once it's passed. Example;

Private Sub Form_Open (Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then Me.SomeField = Me.OpenArgs

End Sub

 

[roh710]

Thanks for your post. I kinda follow what you're saying but not sure how I should change my code.

I tried to change

DoCmd.OpenForm "AddNewCustPartNo", , , , acAdd, acDialog, NewData

to

DoCmd.OpenForm "AddNewCustPartNo", , , , , acDialog, "CustPart_No = '" & NewData "'"

but that didn't take me anywhere. I'm just starting to learn VBA and I am struggling a bit. How would one go about changing the code? any suggestion?

 

[Beetle]

You misunderstood my advice. You have two forms (at least for the purposes of this discussion), FormA and FormB. You want to open FormB from the Not In List event of a combo box on FormA, and in the process pass the value of the NewData argument to FormB via the OpenArgs parameter. You already have (or had) the correct code in place to do this;

DoCmd.OpenForm "AddNewCustPartNo", , , , acAdd, acDialog, NewData [COLOR="Red"]'<<NewData passed to FormB via OpenArgs[/COLOR]

However, that only gets you halfway there. Now you need to place some code in the Open event of FormB so that it knows what to do with the value (NewData) that you just passed it. That code would look like;

Private Sub Form_Open (Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then Me.CustPartNo = Me.OpenArgs

End Sub

assuming that the field CustPartNo exists in the record source of FormB.

 

[roh710]

I understand now. I will try that and I will post the result soon.

Thanks!


KR