Using variable in a get field content statement (Access2000)

[Guest]

I want to use a form with an activeX Calendar on it [frmCalendar]. There are
many date fields throughout other forms that when dblclicked on would open
[frmCalendar] to allow the user to select a new date. I have this statement
in [frmCalendar] after the new date has been selected [dtCalday] to send the
data to the calling form [frmOpenForm] to the dblclicked field [FieldName]:

[frmOpenForm].Form![FieldName].Value = [dtCalday]

as one example. The data in [] are global variables that would gather the
info at the spot. I cannot figure out how to assign the [frmOpenForm] and
[FieldName] correctly. These are my global variables:

Public frmOpenForm As Form
Public dtCalday As Date
Public FieldName As Field

I believe I have successfully assigned the [frmOpenFrom] correctly with this
statement:

Set frmOpenForm = Forms!frmPatient

but that same technique doesn't seem to work with the [fieldname] variable:
i.e.

Set FieldName = Forms!frmPatient.form!InitialVisit

When my statement is run Access keeps on saying "can't find field
'FieldName' on form"

Can these two areas of this statement be replaced by variables? And if so,
how are they assigned or is there a better way to do this?

Thank you

 

[Albert D.Kallal]

First, you can use a string var to ref a field, and form anytime you like.

So,

strMyForm = "frmPatient"
strField = "InitialVisit"

forms(strMyForm)(strField) would thus ref the form+field you have.

A more proper syntax might be:
forms(strMyForm).contorls(strField)

You can also pass one var via the open args. However, why not consider just
picking up the current form

You can grabs the current screen name. I use:

Dim frmActive As Form

Set frmActive = Screen.ActiveForm

The above code can be run as of late of the on-load event. And, you can also
do the same with the active control.

frmActive.ActiveControl.name would return the active control on that
form.

Further, why not consider wrapping the whole calendar form in a sub, and
making that form wait until the user selects a date, and hits the ok.

In all my date forms, I place a nice little button beside the date field.
The code behind that button is:

Call fEditDate("Enter Start Date for Event", Me.StartDate)

So, I pass a nice text prompt, and the field name.

The code for fEditDate is:

Public Sub fEditDate(strCaption As String, ctlDate As Control)

' general date prompt - called by placing a buttion beside any date field

' Usage:
' Call fEditDate("Enter Billing date",me!BillDate)
' parms:
' strCatpion - prompt text
' ctlDate - a control on a form....
'

Dim dtHold As Date
Dim strF As String ' form name

If IsNull(ctlDate) = True Then
dtHold = Date
Else
dtHold = ctlDate.Value
End If

' openargs needs two values
' params are string text, and a long (internal) date number

strF = "frmGetStartDate"
DoCmd.OpenForm strF, acNormal, , , , acDialog, strCaption & "~" &
CLng(dtHold)

' if form is closed, then user cancled...
If fIsLoaded(strF) = True Then
ctlDate.Value = Forms(strF).ctlStartDate.Form.dtvalue
DoCmd.Close acForm, strF
End If


End Sub

The code in my nice frmGetStartDate form is:

Dim lngDate As Long

If IsNull(Me.OpenArgs) = True Then

Me.Caption = "Enter start date"
Else
Me.Caption = Split(Me.OpenArgs, "~")(0)
lngDate = Nz(Split(Me.OpenArgs, "~")(1), 0)
End If

If lngDate = 0 Then
' no date passed, set as today..
Me.ctlStartDate.Form.dtvalue = Date
Else
Me.ctlStartDate.Form.dtvalue = CDate(lngDate)
End If

Me.ctlStartDate.SetFocus


Of couse, you have to change:

Me.ctlStartDate.Form.dtvalue = CDate(lngDate)
to
me.MyActiveXDateContorl.Value = CDate(lngDate)

And, the trick to making the form code "wait" until the user is done can be
found here:

http://www.members.shaw.ca/AlbertKallal/Dialog/Index.html

 

[Guest]

Thanks a ton-particularily sharing the code. My next effort was to code a
button one day but you've save me considerable agrivation

 

[Tom Ross]

Albert

thank you much for your page on dialog box and modal forms. It cleared
something up for me and taught me to make the form invisible first.

I noticed a typo on that page. Not significant but my way of thanks for
putting the page up.

"strSex = fomrs(strF)!GenderField" => 'fomrs' instead of 'forms'

Tom

 

[Tom Ross]

albert

I have made a simple function, 'JobNumber' for the acdialog code calling my
ActiveJobComboBox form with a combo box. It works well in forms.

Can I use this in queries? I have many queries with variable criteria

[JobNumber], [Start Date], etc

I tried using Jobnumber() as the criteria an got an error that I cannot
perform this operation at this time for the first line in my function
DoCmd.OpenForm "ActiveJobComboBox", acNormal, , , , acDialog

Is there something I can do to make this feature work from queries.

Tom

 

[Albert D.Kallal]

Albert

I noticed a typo on that page. Not significant but my way of thanks for
putting the page up.

"strSex = fomrs(strF)!GenderField" => 'fomrs' instead of 'forms'

Thank you much....I correct this....

 

[Albert D.Kallal]

albert

I have made a simple function, 'JobNumber' for the acdialog code calling
my
ActiveJobComboBox form with a combo box. It works well in forms.

Can I use this in queries? I have many queries with variable criteria

[JobNumber], [Start Date], etc

I tried using Jobnumber() as the criteria an got an error that I cannot
perform this operation at this time for the first line in my function
DoCmd.OpenForm "ActiveJobComboBox", acNormal, , , , acDialog

Hum, you have to be careful if you embed a function in sql, as that function
will get called for EACH ROW of the sql. So, if you make a function to
return JobNumber(), but that function in fact prompts the user, then you
will get your dialog box for EACH row of the sql.

Is there something I can do to make this feature work from queries.

The problem here is you don't say when/where/how you are using that query. I
going to bet that this either for a form, or for a report.

If it is for a report, then dump all of the parameters out of the sql.
Reports and forms have a REALLY NICE feature that allows you to pass any
conditions you want to the report (or form).

So, your code would be:

dim lngJobNumber as long


lngJobNumber = JobNumber()

if lngJobNumber = 0 then

exit sub ' user cancled

end if

' open report to job number

docmd.OpenReport "mycoolReport",acViewPreview,,"[JobNum] = " & lngJobNumber

The above means that your sql is clean, and not all messed up with funny
expressions, or parameters, or functions. Furhter, it also means that some
screen does not have to be open (if you use parmaters tied to a form). The
"where" clause is thus one of my favorate ms-access features. In fact, I as
a general rule don't use a dialog form to get paramters for a report, but in
fact build a nice form, and launch the report from that form. Thus, I simply
build the where clase in code and pass that to the report. This means we
don't have to use a dialog form anyway. Here is some screen shots of what I
mean:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

 

[Tom Ross]

Thanks albert. I already use the 'Where' clause to launch forms and
reports. It is a very nice feature

I do have several queries that users call as a query enter the parameters to
see sets of data. I keep them as queries so that users can sort and filter
the resulting data set. Having to respond to the function for EACH row of
the dataset would be very annoying! I can get much the same look by making
a form with datasheet view.

Thanks for the explanation. I will stop looking for that kind of solution.

Tom

albert

I have made a simple function, 'JobNumber' for the acdialog code calling
my
ActiveJobComboBox form with a combo box. It works well in forms.

Can I use this in queries? I have many queries with variable criteria

[JobNumber], [Start Date], etc

I tried using Jobnumber() as the criteria an got an error that I cannot
perform this operation at this time for the first line in my function
DoCmd.OpenForm "ActiveJobComboBox", acNormal, , , , acDialog

Hum, you have to be careful if you embed a function in sql, as that function
will get called for EACH ROW of the sql. So, if you make a function to
return JobNumber(), but that function in fact prompts the user, then you
will get your dialog box for EACH row of the sql.

Is there something I can do to make this feature work from queries.

The problem here is you don't say when/where/how you are using that query. I
going to bet that this either for a form, or for a report.

If it is for a report, then dump all of the parameters out of the sql.
Reports and forms have a REALLY NICE feature that allows you to pass any
conditions you want to the report (or form).

So, your code would be:

dim lngJobNumber as long


lngJobNumber = JobNumber()

if lngJobNumber = 0 then

exit sub ' user cancled

end if

' open report to job number

docmd.OpenReport "mycoolReport",acViewPreview,,"[JobNum] = " & lngJobNumber

The above means that your sql is clean, and not all messed up with funny
expressions, or parameters, or functions. Furhter, it also means that some
screen does not have to be open (if you use parmaters tied to a form). The
"where" clause is thus one of my favorate ms-access features. In fact, I as
a general rule don't use a dialog form to get paramters for a report, but in
fact build a nice form, and launch the report from that form. Thus, I simply
build the where clase in code and pass that to the report. This means we
don't have to use a dialog form anyway. Here is some screen shots of what I
mean:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal