Run a command built from a string. 1

[pdldavis]

Hi, How do you run a command created from a string?

I have:

Dim txtString, txtDate As String

Me.calDate1.Value = Now()

txtDate = Me.calDate1.Value

txtString = Me.txtFormName & Me.txtDateField & " = #" & txtDate & "#"

This produces: forms!iInstFrmAnalogDCS.iInstFrmAnalogDCSSub.Form.txtPerfDate = #3/8/2007#

- which is what I want.

How do I run this? - DoCmd.Run--- txtString?

Thanks, Dan

 

[Eupher]

Dan,

Your string concatenation does not produce an executable command. What are you trying to do exactly? It looks like you are trying to assign a value to a textbox on your form. If so, all you need is:

Me![blue]MyFieldName[/blue]=[blue]SomeValue[/blue]

...where [blue]MyFieldName[/blue] is the name of the control on your form, and [blue]SomeValue[/blue] is the value you want to assign. [blue]SomeValue[/blue] may be a variable which holds the value in question, or it may be a literal. If literal, it should be properly delimited: double quotes for strings, # sign for dates, no delimiter for numbers. For instance:

Dim MyString As String

MyString = "Howdy, pardner!"

Me!MyTextbox = MyString     [green]'initialized variable[/green]

[b]' OR[/b]

Me!MyTextbox = "Howdy, pardner!"    [green]'literal string[/green]

[b]' OR[/b]

Me!MyTextbox = #4/1/2007#     [green]'literal date[/green]

[b]' OR[/b]

Me!MyTextbox = 2409     [green]'literal number[/green]

I apologize if I've misjudged your intent and spewed a lot of blather that you didn't need.

HTH,

Ken S.

 

[pdldavis]

Hi Ken,

What I have is a calendar that can be opened by six buttons
on six forms.

When I open the calendar using button 1, I pass the associated text box and its form name to the calendar to build a string based on the date picked from the calendar control:

Using button 1, it builds this string:

iInstFrmAnalogDCS.iInstFrmAnalogDCSSub.Form.txtPerfDate = #3/8/2007#

Using button 2, it builds another string based on another form name, control name and calendar date. And so on.

Now, if I hard code this, it works fine.

What I want to do is give this some flexability so I can use the same calendar for many forms.

Is there an equivalent - something like

doCmd.runSql txtString

where I can turn that text string into a command.

Maybe I need to dim the txtString as something else to run it.

I hope that's clearer.

Thanks, Dan

 

[JoeAtWork]

It sounds to me like your calendar is in a dialog box?

Instead of trying to get the dialog box to insert the value into the calling form, how about having the calling form read what value was entered in the calendar? You could pass the value using a global variable. So the steps would be:

1. Button opens the calendar form as a dialog box (so that it is modal - i.e. execution pauses on the calling form until the dialog box is closed).
2. User chooses a date on the calendar.
3. User clicks OK on the dialog box, which sets the global variable to the date on the calendar. Then the dialog box closes.
4. Execution resumes in the calling form, which sets the textbox to the value in the global variable.

 

[pdldavis]

Yes, that is what I want to do. I wasn't opening the form in dialog, which will be a new experience for me. I'll give that a try.

Thanks, Dan

 

[Eupher]

Another thing I have done is pass the entire calling form as an argument to a function that opens the calendar (or whatever) form; then I can refer directly to the form object and all its child objects, properties, and methods within the calendar function.

But JoeAtWork's idea may be simpler to implement.

Ken S.

 

[pdldavis]

Ok, here is what I've got so far:

I have a function:

Public Function calDate(frm As Form, dcsDate As Date)

dcsDate = frm.calDate1.Value

MsgBox dcsDate

End Function

I open the Calendar in dialog:

DoCmd.OpenForm "frmCalendarDCS", acNormal, , , , acDialog

When I click the Calendar I call the function:

Private Sub calDate1_Click()

Call calDate(Me, Me.calDate1.Value)

End Sub

This picks up the date I want.
I close the calendar and the code resumes from
the form:

Me.txtPerfDate = dcsDate

- at which point I get an error, Variable not Defined.

So, I guess I am not properly defining dcsDate to be a global variable?

Thanks, Dan

 

[pdldavis]

I got it - finally.

Thanks, Dan