combo box work like a InputBox ?

[redFred]

Please Help
How do I get user input from a combo box pick into a dimensioned variable. My problem is; in my code for a On_Click event I want a combo box to become visible, (code) stop and allow the user to pick from the combo, fill the variable with the pick data then move on to the next lines of code.

My problem is I can’t figure out how to stop the code to get the combo box pick into the variable.

The following is snap shot of my code at the point where I need the combo data.


stDocName = "cjl_frm_carbon_tb_feature_dialog"
stLinkCriteria = "[IDNo]=" & "Forms!cjl_frm_carbon_tb_feature_dialog![IDNo]"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Forms!cjl_frm_carbon_tb_feature_dialog!IDNo = FileID


Forms!cjl_frm_carbon_tb_feature_dialog!comboFeature1.Visible = True
Forms!cjl_frm_carbon_tb_feature_dialog!comboFeature1.SetFocus
Feature1_Input = Forms!cjl_frm_carbon_tb_feature_dialog!comboFeature1.Text




much thanks inadvance
redFred

 

[RuralGuy]

Why are you trying to run the 2nd form remotely? Why not move your code to the other form?

 

[MarkK]

In the 'WindowMode' parameter of the DoCmd.OpenForm action specify 'acDialog'. This opens the form as a modal, popup dialog box and calling code execution halts until the opened form is closed or hidden.
Note that to return a value you must not allow your user to close the form, but rather only hide it. If the form is closed the user input is not recoverable.

Private Sub asdkjf()
   Const FN As String = "fMyDialog"
   Dim userInput As Long
   'open with WindowMode = acDialog
   DoCmd.OpenForm FN, , , , , acDialog
   'execution paused until FN closed or hidden - don't allow user to close it!!!
   
   'recover user input here
   userInput = Forms(FN).cboUserInput
   'close FN
   DoCmd.Close acForm, FN
End Sub

In the open dialog, provide a command button that sets...

  Me.Visible = False

...to return execution to the calling routine.
Cheers,

 

[redFred]

Let me explain a little better on what I am trying to accomplish.

I am adding a module to a database that is in use, the business need is to monitor user input based on products selected. If a particular product combination is selected the user in interrupted and asked to enter additional information as part of an auditing scheme. This additional information needs to be tied to the PK of the main table & frm.

My pseudo code design is as follows:

1.) create and run a module that adds the required form & tables (1 to store the new data) & 4 that contain the list for the combos) – this is done

2.) add a module to call when the user is done entering a new record, this module runs a select case to determine if the product combination is true

3.) if product combination is true a form is open that does the following:

a. links to the PK
b. adds the PK (auto Num) number to a text box on the new form
c. make visible the first of a series of combo box’s
d. based on the pick of the first combo the data picked is run thru a case select to determine the next combo list.

The problem with using acDialog as the window mode, is it stops the execution before making the PK link & before I can make the combo visible.

Here is the module as is stands now:

Public Function Test1() As Boolean

Dim PartName As String
Dim Turnback As String

Form_DataEntryForm.Combo170.SetFocus
PartName = Form_DataEntryForm.Combo170.Text
Form_DataEntryForm.Combo190.SetFocus
Turnback = Form_DataEntryForm.Combo190.Text

Select Case PartName
Case "Carbon Seal"
On Error GoTo Err_Command0_Click

Select Case Turnback
Case "YES"
On Error GoTo Err_Command0_Click

MsgBox "You have recorded a Carbon Seal Turn Back record. As part of an ACE event, please enter the following additional data"


Dim stDocName As String
Dim stLinkCriteria As String
Dim FileID As Long

' get the pk of the main frm & put in FileID
Form_DataEntryForm.IDNo.SetFocus
FileID = Form_DataEntryForm.IDNo.Text
' MsgBox FileID
' open the form and create the pk link
stDocName = "cjl_frm_carbon_tb_feature_dialog"
stLinkCriteria = "[IDNo]=" & "Forms!cjl_frm_carbon_tb_feature_dialog![IDNo]"


DoCmd.OpenForm stDocName, , , stLinkCriteria
' put the pk in the new frm
Forms!cjl_frm_carbon_tb_feature_dialog!IDNo = FileID
' make the 1st combo visable
Forms!cjl_frm_carbon_tb_feature_dialog!comboFeature1.Visible = True
Forms!cjl_frm_carbon_tb_feature_dialog!comboFeature1.SetFocus


'**** need to stop the code here to get the user data into Feature1_Input

Feature1_Input = Forms!cjl_frm_carbon_tb_feature_dialog!comboFeature1.Text


'**** start case select statement here based on 1st combo data



Exit_Command0_Click:
Exit Function

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

Case Else

End Select

Case Else

End Select

End Function

 

[RuralGuy]

An interesting project. Are you saying that you are not allowed by your client to directly modify the existing form to add some auditing?

 

[MarkK]

The problem with using acDialog as the window mode, is it stops the execution before making the PK link & before I can make the combo visible.

So pass the PK link and combo name into the acDialog form.

DoCmd.OpenForm FN, , , , , acDialog, _
  OpenArgs:=YourPKLink & "~" & ComboHostFormName & "~" & ComboName

Then, in the acDialog form, split the OpenArgs string at the tilde (~) and you can separate out your parameters into a variant array.

Private Sub Form_Open(Cancel as integer)
  Dim var as variant
  var = split(me.openargs, "~")
  debug.print "PK Link ID is: " & var(0)
  Forms(var(1)).Controls(var(2)).visible = true
End Sub

Maybe this steers you somewhere useful.
Cheers,

 

[redFred]

Yes the client is not allowing the main form to be altered. This database is used to collect data of products at the factory floor by factory workers, that means that the data entry process has to be very straight forward. A seprate client wants addition information about one particular product based on two combinations of data about that product. My plan was to bring up the new frm then cascade combo box's by using the visible prop and case select.

I am studying lagbolt's suggestion this morning.

 

[redFred]

It is apparent that there is no way to pause code to allow user input in VBA like using a Input Box in VB. If I break the code manually and pick a selection from the combo and manually re- start the code it works perfectly.

I am going to write an executable class module in VB and port it over to this database that I sadly vol to add function to. In reality the db should have be written in VB, not Access......some much for wasted time

 

[MarkK]

It is apparent that there is no way to pause code to allow user input

Untrue. There is a way. One is posted here. There is also an InputBox.

 

[redFred]

lagbolt - your right, I should have said - I am incapable to do what I want to do. Unfortuantly I do not write code everyday, more like once a year.

However I did try the acDialog and Hide method but still had problems. I have to finish up another project, but want to keep this post going and will be back in a day or so.

thanks very much for the help you have given me so far.