Get values from unbound combo boxes when form closes.

[farmerscott]

Hi Everybody,

I am trying to create a form (called frmTime) that makes entering time a lot easier into a control.

On the form I have 3 unbound combo boxes. These being- Hour, Minutes and AM/PM. Each of these combo boxes has a value list (typed in by me, and not based on a table, etc).

On the form I also have a button to close the form. Once the form is closed the 3 values are to be concatenated to create a time value eg 6:30PM. The VBA code (I have so far) with this event is as follows (and is in the VBA editor associated with the form)-

Private Sub CloseForm_Click()
Dim Hour As String
Dim frmTime As Form

'DoCmd.Close acForm, Me.frmTime

'Hour = Me.Combo8.Text
DoCmd.Close
End Sub

My questions are-
1. Have I the correct change event for my needs?
2. Why does the line

DoCmd.Close acForm, Me.[COLOR=#0000ff]frmTime[/COLOR]

give a compile error (highlighting frmTime)?
3. How do I get the values from the combo boxes?

'Hour = Me.Combo8.Text

errors saying it does not have the focus.

thanks

FarmerScott

 

[xenou]

DoCmd.Close acForm, Me.frmTime

you want:
DoCmd.Close acForm, Me.Name
but I can't really see putting a close command in a closeform event as being useful.

give a compile error (highlighting frmTime)?
3. How do I get the values from the combo boxes?
Code:

'Hour = Me.Combo8.Text

errors saying it does not have the focus.

Not sure but it seems dangerous to try to access control in a close event, since they may not exist anymore (you are closing the form and at some point they no longer exist). You have to experiment. The deactivate event will fire before the close event. Even so, it seems dangerous to rely on close event here - what if the user doesn't close the form?

Personally I generally use a button (labelled ENTER or SUBMIT or OK) and have another one (labelled CANCEL). Users can generally be counted up to understand this very common workflow - hit enter when you are done, or cancel if you want to abort. The code for the button can close the form. If you want to enforce more control, you can take away the red X from you form so that the only way to close it is through your buttons.

 

[farmerscott]

Hi Xenou,

thanks for the input.

you want:
DoCmd.Close acForm, Me.Name
but I can't really see putting a close command in a closeform event as being useful.

In essence I am asking the sub to do the same job twice over?

So we can get these change events right, let me explain how I see the code operating.

On either 'Get Focus' or 'On Click' of a control I want this form to open. Once the 3 combo boxes have been filled, it then puts the time into that control. Once the next control 'Gets Focus' or 'On Click' the form gets closed. Your suggestion is workable but would like to get around making that extra click.

Just wondering if this thread adds something to the debate- https://www.mrexcel.com/forum/excel...tbox-visual-basic-applications-procedure.html

thx

FarmerScott

PS these Access form events are another beast beyond Excel's in understanding their nuances.

 

[xenou]

Generally I think having a user click on OK button is a time-tested workflow that users will understand. You are free to not use one if you prefer (but it seems you are still requiring the users to click the close button or "another control" so I don't see how you save a click).

The other link is for Excel userforms, not Access forms, so potentially will only confuse matters. Also use a bound control if you can - simpler, especially if you are newer to Access - you must learn how bound controls work so you can leverage the built in features of Access effectively.

 

[farmerscott]

Xenou,

thanks for the help. Got it to work.

For reference, here is the code.

Private Sub Command19_Click()
Dim x As Date
Dim H As String
Dim M As String
Dim AMPM As String

'hour value 1-12 from combo box
H = Me.Combo8.Value
'minute value from combo box
M = Me.Combo10.Value
'AM or PM
AMPM = Me.Combo12.Value

'concatenate to create the time value
x = H & ":" & M & AMPM

'declare form and control to paste data into
Dim frmTime2 As Form
Dim Time2 As Control

'paste value to other form
Forms("frmTime2").Time2 = x
End Sub

I took your suggestion where this code submits the data and use another button to close the form.

cheers <label for="rb_iconid_14">

</label>

FarmerScott