Passing String Variable to Newly Opened Dialog Form

[Porisius]

All,

First of all, I want to thank everyone I ever googled to learn VBA and Access. I have never had formal training, or gone to any classes for this, yet managed to land a contract job after leaving the Navy. They have been impressed with my work (Guess I should say, "Our"?). I never got around to sending my thanks, so now that I am fixing that. So, thank you all. Sorry, I haven't just copied and pasted someone's code, but I did learn something from it.

I am working on an inventory/asset database in Access 2010. I have a small issue here, my code listed below works, but I want to pass the string variable "NewItem" to a form that I have open from this code. Thing is, once you open a form as a dialog, all code stops processing (at least to my understanding). Now, that's a good thing for the main purpose I have it for, but I want to make it easier on the user.

Private Sub cmbModelNo_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer

intAnswer = MsgBox(NewData & " is not a recongized Model Number. Would you like to create a new entry for " & NewData & "?", vbYesNo, vbQuestion)
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "frmNewItem", acNormal, , , , acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

The form is "frmNewItem", and the textbox I want to transfer it to is "txtModelNo". If it can be reasonablely done, I would appericate it, however, if it can't, I won't cry about it. I have learned that even VBA has some limitations. (i.e. Conditional Formatting: Only 3 Conditions).

I have painted my desk a little red trying to figure it out. (Kidding) If anyone has any ideas, it would be appericated.

Thanks,
Chris

 

[Mr. B]

Try using the "OpenArgs" parameter when you open the form. Just add "NewItem" as the value for the "OpenArgs" paramter as shownin the line:

DoCmd.OpenForm "frmNewItem", acNormal, , , , acDialog, "NewItem"

Then in the "OnOpen" event of the "frmNewItem" form, just check for the value of "OpenArgs" like this:

If Me.OpenArgs = "NewItem" Then
    'Do something
Else
    'Do something else
End If

 

[Porisius]

Mr. B

:banghead:

Its always a simple solution, I use that in another database for a different reason. I don't know why I didn't think of it.

Also, I made a couple of mistakes when I posted. First, the string variable to be passed was NewData and I needed it to display in the text box. My fault for misleading, but the world didn't end, so, oh well. Anyway, here's what I got for the next guy, and it works!

In the main form with the combobox:

Private Sub cmbModelNo_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
 
intAnswer = MsgBox(NewData & " is not a recongized Model Number. Would you like to create a new entry for " & NewData & "?", vbYesNo, vbQuestion)
If intAnswer = vbYes Then
    DoCmd.RunCommand acCmdUndo
    DoCmd.OpenForm "frmNewItem", acNormal, , , , acDialog, NewData
    Response = acDataErrAdded
Else
    Response = acDataErrContinue
End If
End Sub

In frmNewItem:

Private Sub Form_Open(Cancel As Integer)
txtModel.Value = Me.OpenArgs
End Sub

Thanks for the return of my sanity there, Mr. B.

Chris

 

[Mr. B]

I hate when I get one of these mental blocks. Sometimes you just need a reminder.

Glad to help. Good luck with your project.