Open a form from vba and wait for input

[Deleted member 8621]

I'm using vba in one form and if they decide to save the record I check to see if the "upgrade" check box is checked. If it is I'm trying to open another form that makes them select the software that makes the upgrade legit. I will then save that value, return to the original form and save the new record with the value from the second form.

I can't seem to open the second form and wait for input. vba just runs straight through my code without waiting for input on the second form.

Help

thanks
matthew my code is below

Private Sub btnAssign_Click()
On Error GoTo Err_btnAssign_Click
Dim rst As DAO.Recordset
Dim strsql As String
Dim hdw As String
Dim oem As String
Dim viol As String
Dim compoem As String

viol = "Computer: " & GetWinComputerName() & " User: " & GetWinUserName & " " & Now()
hdw = Forms!frmAssignLicenses!HdwSn
oem = Forms!frmAssignLicenses!OemSn

Upgd_Check:
If Me!Upgrade = True Then
DoCmd.OpenForm "frmAssignUpgdLic"
Forms!frmAssignUpgdLic!sftw = "is this working"

End If

Assign_Lic:
If Forms!frmAssignLicenses.opgSelectSftwCat = 2 Then
strsql = "SELECT TOP 1 Company, Software, Version," & _
" Upgrade, LicenseNum, PartNum," & _
" NumLicenses, OperatingSys, OemSn," & _
" SnSrvtag, UpgComplianceOem, Note," & _
" [Site Licensed], LicViolation" & _
" FROM tblLicenses" & _
" WHERE (((OemSn)='" & oem & "') AND ((SnSrvtag)='2'));"

Set rst = CurrentDb.OpenRecordset(strsql)
rst.Edit
rst!SnSrvtag = hdw
rst.Update
Else: Me!SftwHdwSn = Me!HdwSn
Me!Installed = True
End If

DoCmd.Close
Exit_btnAssign_Click:
Exit Sub

Err_btnAssign_Click:
MsgBox Err.Description
Resume Exit_btnAssign_Click

 

[jjturner]

Hello Matthew,

I've been racking my brains on the same conceptual problem, if I understand you correctly, which is this - how to halt code in execution and wait for user action without using a Msgbox or Inputbox.

The only solution I've been able to come up with so far is to do this:

1) Set up a public (global) variable like 'booUserChoiceMade'.
2) Code in an infinite 'Do' loop, nesting in a For/Next that sets up a counter (iLong = 1 to 300000) that will check periodically for user input by using the 'DoEvents' command.
3) Once the user has made a choice, the code will exit the loop and resume with the next task in the procedure.

Not very elegant and potentially hazardous, but so far I don't know any other way. Here's my example:

If booPreConditionMet Then ' enter infinite loop looking for user input
Do Until booUserChoiceMade
For iLong = 1 to 300000 ' or whatever interval you prefer
If iLong = 300000 Then
DoEvents
iLong = 1 ' reset counter
End If
Next iLong
Loop
End If

Hopefully, by now you've found a better solution, in which case I'd be grateful if you could relate that to me.

Regards,
John

 

[RichMorrison]

You can set the "Modal" property of a form to Yes. When you open such a form from VBA, the VBA code does halt until the form is closed.

Unfortunately, that will not help in your example.

You wrote
<<
Upgd_Check:
If Me!Upgrade = True Then
DoCmd.OpenForm "frmAssignUpgdLic"
Forms!frmAssignUpgdLic!sftw = "is this working"
End If
<<

You open a form in one statement.
In the next statement you try to assign a value to a field in the form.

See the problem ?????

The solution is to open your form as modal, let the user enter any information in the form, store the user info in a "property", close the form, return to your VBA code, get the value from a "property".

See Access help for more info on "property procedures".

RichM