Solved Stuck on (seemingly simple) set value of Combo box on form open usin OpenArgs (1 Viewer)

[gakiss2]

I have a form which contains a combo box named DocType. In usual operation it works fine. user selects from a list coming off a table. real simple, must be from list.

I added a button on my 'Front Page' form called NewSwitchboard . I want the button to launch the above form in Add mode and to prepopulate DocType with a specific one of the choices, that is, "Deviation Authorization". In normal use there are no issues with the user selecting "Deviation Authorization", works like a charm. There is more automation that I want to do after I get past this roadblock but most of that is unrelated to this topic. Here is the code that I have to launch the form in addmode, you'll see I added an OpenArg.

Private Sub cmdDev_Click()
DoCmd.Close acForm, "NewSwitchboard", acSaveNo
DoCmd.OpenForm "frmDocDetail", acNormal, , , acFormAdd, , "Deviation Authorization"

End Sub

And I added an Form_Open procedure to the form that gets opened above, frmDocDetail.

Private Sub Form_Open(Cancel As Integer)
Dim initDocType As String

' MsgBox "open args" & Forms!frmDocDetail.OpenArgs

initDocType = Forms!frmDocDetail.OpenArgs
If Len(initDocType) > 0 Then
            MsgBox "OK, now what"
            Me.DocType.Column(1) = initDocType
End If

End Sub

I've gotten so far that I can have the opened form spit the OpenArg back to me in a Msgbox but I'm having trouble getting it to populate the DocType combo box with "Deviation Authorization". I'm not really sure I need OpenArgs since I want this button to always prepopulate with "Dev..." It was a suggestion from something I found on line. I Don't want it prepopulated with "Dev.." every time this form gets opened in add mode though, just when it gets opened form my new button. Also I have a button that just opens a new form (same form in add mode) and it works fine. There is code to make sure the user selects the DocType first by locking all other records after Form_Activate event and stays locked until 'After_Update' for DocType. I think this should be OK since I am trying to populate DocType first and I don't really know if locking a field / control even prevents code from manipulating it. the database it attached if you need to look there. the locking and unlocking occurs just below the Form_Open procedure on the frmDocDetail code.

 

[bob fitz]

The first column of the combo box is hidden. Try the following line of code in the forms On Open event.
Me.DocType = "DEV"

Or try:

Private Sub cmdDev_Click()
DoCmd.Close acForm, "NewSwitchboard", acSaveNo
DoCmd.OpenForm "frmDocDetail", acNormal, , , acFormAdd
Forms!frmDocDetail.DocType = "DEV"

End Sub

 

[Cronk]

Whata is the row source for the combo and how many rows is it set to display, as well as the column widths?

 

[arnelgp]

move your code to the Form's (frmDocDetail) Load Event:

Private Sub Form_Load()
Dim initDocType As String

' MsgBox "open args" & Forms!frmDocDetail.OpenArgs

initDocType = Me.OpenArgs & ""
If Len(initDocType) > 0 Then
            MsgBox "OK, now what"
            Me.DocType = DLookup("DocType", "tblDocs", "DocName='" & Replace(initDocType, "'", "''") & "'")
End If

End Sub

 

[gakiss2]

The first column of the combo box is hidden. Try the following line of code in the forms On Open event.
Me.DocType = "DEV"

Or try:

Private Sub cmdDev_Click()
DoCmd.Close acForm, "NewSwitchboard", acSaveNo
DoCmd.OpenForm "frmDocDetail", acNormal, , , acFormAdd
Forms!frmDocDetail.DocType = "DEV"

End Sub

Thank You very much, that worked like a charm. I guess I got hung up trying to set the .column(1).

 

[gakiss2]

move your code to the Form's (frmDocDetail) Load Event:

Private Sub Form_Load()
Dim initDocType As String

' MsgBox "open args" & Forms!frmDocDetail.OpenArgs

initDocType = Me.OpenArgs & ""
If Len(initDocType) > 0 Then
            MsgBox "OK, now what"
            Me.DocType = DLookup("DocType", "tblDocs", "DocName='" & Replace(initDocType, "'", "''") & "'")
End If

End Sub

bob fitz pointed me to just setting the first column value and that worked great. But thank you, I think your solution would work great. I had already started to think about skipping the button on the front page entirely since I am going to have to put together some procedures on frmDocDetail to automate the process I want to do next anyway.

 

[gakiss2]

The first column of the combo box is hidden. Try the following line of code in the forms On Open event.
Me.DocType = "DEV"

Or try:

Private Sub cmdDev_Click()
DoCmd.Close acForm, "NewSwitchboard", acSaveNo
DoCmd.OpenForm "frmDocDetail", acNormal, , , acFormAdd
Forms!frmDocDetail.DocType = "DEV"

End Sub

Minor Glitch. I rely on the 'After_Update' on that DocType combo to launch the next part of the procedure. Quickly, I tried to add a line to refresh frmDocDetail to the procedure and that didnt' work. I am looking for a solution but would appreciate any idea on how to fix, Tab to next field?

 

[bob fitz]

Minor Glitch. I rely on the 'After_Update' on that DocType combo to launch the next part of the procedure. Quickly, I tried to add a line to refresh frmDocDetail to the procedure and that didnt' work. I am looking for a solution but would appreciate any idea on how to fix, Tab to next field?

You can set the focus with something like:

Me.NameOfControl.SetFocus

 

[gakiss2]

You can set the focus with something like:

Me.NameOfControl.SetFocus

I've tried a couple of things including setfocus. The first tab position is the DocType combo so the cursor starts there anyway This new button doesn't behave any differently except (thanks to your guidance) the DocType is prepopulated with 'Deviation Authorization'. I had thought that when the code put that value into the combo box, the after update would fire and the rest of the process would proceed. The rest of the process is to generate a string for Doc Track Number then unlock the field sourced controls so the user can make a new record. My next move is to just copy the code that would get fired by the after_update and paste it in the Form_Open procedure. BTW. I also added a check control that I set to True right after I set DocType so now I can use an IF Then in the Form_Open so I can do this only when coming to this form from the new button.

 

[arnelgp]

you can make this Public:

Private Sub DocType_AfterUpdate()

then you can call it:

Private Sub cmdDev_Click()
DoCmd.Close acForm, "NewSwitchboard", acSaveNo
DoCmd.OpenForm "frmDocDetail", acNormal, , , acFormAdd
Forms!frmDocDetail.DocType = "DEV"
Forms!frmDocDetail.DocType_AfterUpdate
End Sub

 

[gakiss2]

you can make this Public:

Private Sub DocType_AfterUpdate()

then you can call it:

Private Sub cmdDev_Click()
DoCmd.Close acForm, "NewSwitchboard", acSaveNo
DoCmd.OpenForm "frmDocDetail", acNormal, , , acFormAdd
Forms!frmDocDetail.DocType = "DEV"
Forms!frmDocDetail.DocType_AfterUpdate
End Sub

you can make this Public:

Private Sub DocType_AfterUpdate()

then you can call it:

Private Sub cmdDev_Click()
DoCmd.Close acForm, "NewSwitchboard", acSaveNo
DoCmd.OpenForm "frmDocDetail", acNormal, , , acFormAdd
Forms!frmDocDetail.DocType = "DEV"
Forms!frmDocDetail.DocType_AfterUpdate
End Sub

Thank you. A) I didn't know click events could be Public and B) I would have said Call DocType_AfterUpdate. I didn't know you could call it that way. Working great now and on to the rest of the process.