Custom Dialog Boxes---- grrrrr... 3

[Serin]

I'm trying to automate a form in Access where clicking a command button will bring up a subwindow depicting a calendar control, that will then return a value of the selected date to a text box on the parent form, in the nature of an InputBox() function. It seems the obvious way to do this would be to make a custom dialog, but my coding skills in VBA are less than exemplary, and the database is refusing to compromise. (Go figure.)

Is there a way to pass arguments to a modal form in code, and return a value like a function? Sample code would be greatly appreciated. Thanks!

-Serin

 

[tunsarod]

This is for Access 97

Create two forms:

Form: MyForm has a textbox "Text1" and a command button that opens the form CalFOrm.

Form: CalForm is a popup dialogue box.

In CalForm paste a calendar control object using Calendar Control 8.0 from the extended toolbox ActiveX component list. Assuming the calendar Control is named "ActiveXctl0" in the module window of CalForm type:


Private Sub ActiveXCtl0_AfterUpdate()

Forms!MyForm!Text1 = Me.ActiveXCtl0

End Sub

If you can be sure that CalFOrm will never be opened when MyForm is not open the above will ensure that Text1 on MyForm is updated with the date from CalForm every time you click on a new date.

If there is a possibility for CalForm to be open when MyForm is closed then:

Private Sub ActiveXCtl0_AfterUpdate()

If IsLoaded("MyForm&quot Then
Forms!MyForm!Text1 = Me.ActiveXCtl0
End If

End Sub


will prevent an error. Copy IsLoaded from the Northwind database Miscellaneous module.

I recently tried using the Calendar Control 8.0 ActiveX component on a couple of PC's running Windows98SE and it failed because the MCSCAL.OCX file was missing. So if you're using Win98SE and this doesn't work that's a possibility.

Can anyone offer a solution to this problem, i.e. how can the Calendar Control 8.0 object be implemented on a Win98SE system?

Regards
Rod

Set your mind on the Son, before the Sun sets on you.

 

[Serin]

Problem is, there are quite a few date fields that I need to update in this fashion, and I need a way to use the same dialog form for all of them, but deposit the return value in different text boxes depending on on the button they click. Information needs to be passed to the calendar control in the form of a default value, and returned in the nature of a function so that I can direct the data appropriately.

-Serin Some people ask, "Why get out of bed in the morning?" but I say, "Where's my beer?"

 

[PaulCoop]

You can send arguments to a form using the "Openargs" string as the last part of the Openform method:

DoCmd.OpenForm formname[, view][, filtername]_
[, wherecondition][, datamode]_
[, windowmode][, openargs]

If you wish to send more than one argument just put the values together as a delimited string and use that instead.
If you use a delimited string you will need a Args function to read off the nth field.

There is no inbuilt way to return value(s) from a form. You must either set some global variable(s), send the results to a table or do as Rod suggests.

A more sophisticated method using a variable table is to pass the name of the variable(s) to form and update the relavent record in the variable table.

This variable table would consist of two texts fields, the first would be the variable name the second would be the value as string. You would only then have to read of the relavent record and convert the value text to its correct type.

Paul Cooper

 

[jebry]

Hi!

To combine Paul and Rod's ideas use the OpenArgs to pass the form name:

DoCmd.OpenForm "CalForm", , , , , , Me.Name

Then in CalForm use:

Forms!(Me.OpenArgs)!YourTextBox = Me.ActiveXCtl0

If you need the text boxes to have different names (which is likely) then do the following:

DoCmd.OpenForm "CalForm", , , , , , Me.Name & ";" & "YourTextBox"

Then in the CalForm's Update event use:

Dim strForm As String
Dim strTextBox As String

strForm = Left(Me.OpenArgs, InStr(Me.OpenArgs, ";&quot - 1)
strTextBox = Mid(Me.OpenArgs, InStr(Me.OpenArgs, ";&quot + 1)

Forms!(strForm).Controls(strTextBox) = Me.ActiveXCtl0

If you have more than one date field per form then it is probably easiest to train the user to doubleclick on the appropriate text box to get the calendar form. In the doubleclick event for each text box you can use the following:

DoCmd.OpenForm "CalForm", , , , , , Me.Name & ";" & "YourTextBox"

like before except the textbox name will change depending on which is clicked.

Finally, you could do all of this from one public function in a module and get the form name and text box name from the Screen object. Then in the On DoubleClick in the Event tab of the property window you could call that function. If you are interested in that code let me know.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Serin]

Yes, please send that code, I'd appreciate it. Thanks.

-Serin Some people ask, "Why get out of bed in the morning?" but I say, "Where's my beer?"

 

[jebry]

Hi Serin!

Sorry I was gone so long, long meeting! Create the following function in a module:

Public Function GetDate()

Dim frm as Form
Dim cntl As Control

Set frm = Screen.ActiveForm
Set cntl = Screen.ActiveControl

DoCmd.OpenFrom "CalForm", , , , , , frm.Name & ";" & cntl.Name

Set frm = Nothing
Set cntl = Nothing

End Function

On the Event tab of the property sheet for each textbox which will store a date, in the On DoubleClick line type:

=GetDate()

Then in the CalForm use:

Private Sub ActiveXCtl0_AfterUpdate()

Dim strForm As String
Dim strTextBox As String

strForm = Left(Me.OpenArgs, InStr(Me.OpenArgs, ";&quot - 1)
strTextBox = Mid(Me.OpenArgs, InStr(Me.OpenArgs, ";&quot + 1)

Forms!(strForm).Controls(strTextBox) = Me.ActiveXCtl0
DoCmd.Close acForm, Me.Name

End Sub

This will get the date the user selects into whichever textbox they doubleclick. I like this method because the user finds it to be intuitional.

hth
Jeff Bridgham
bridgham@purdue.edu