Automation to fill 2 parameter dialogs with 2 parameters from VB program.

Private Sub Form_Load()
  Dim intPos As Integer 'Position of the Pipe
  Dim strControlName As String 'Controlname passed
  Dim strValue As String 'Value to assign

  If Len(Me.OpenArgs) > 0 Then
    intPos = InStr(Me.OpenArgs, "|")
    If intPos > 0 Then
      'Retrieve Control Name
      strControlName = Left$(Me.OpenArgs, intPos - 1)
      'Retrieve Value to Assign
      strValue = Mid$(Me.OpenArgs, intPos + 1)
      'Make the Assignment
      Call SendParameters(strControlName, strValue)
    End If
  End If
End Sub

Public Sub SendParameters(strControlName, strValue)
    With Forms(PcUsageLogByDateByLogoutByHitsGraph)
      .Controls("[Enter Start Date: (mm/dd/yyyy)]") = strControlName
         If Not IsMissing(strValue) Then
            .Controls("[Enter End Date: (mm/dd/yyyy)]") = strValue
         End If
   End With
End Sub

I have this codes in my access's form load and I have an access form which will prompt the 2 parameter dialog which is [Enter Start Date: (mm/dd/yyyy)] and [Enter End Date: (mm/dd/yyyy)]. I would want to fill the 2 parameter dialogs with the parameters which i parse in from my VB program, strControlName and strValue. Having executing the codes, error dialog prompting saying Access can't find the field '[Enter Start Date: (mm/dd/yyyy)]' referred to in your expression.
n0v1cEAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

broesiCommented:
Are these parameter dialogs pop-up-windows appearing one after another? If so it looks like they are no form controls at all but query parameters. To validate this, please look up the data source of your form. Is that a query? If yes, open the query in design view and check if there are any parameters defined.

Post the results and then we can work from there.

broesi
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
peter57rCommented:
Hi n0v1cE,
Quotes missing...
  With Forms("PcUsageLogByDateByLogoutByHitsGraph")


Pete
0
n0v1cEAuthor Commented:
1st Query: PcUsageLogByDateByLogout
PARAMETERS [Enter Start Date: (mm/dd/yyyy)] DateTime, [Enter End Date: (mm/dd/yyyy)] DateTime;
SELECT PcUsageLog.Computer, PcUsageLog.Date, PcUsageLog.Action
FROM PcUsageLog
WHERE (PcUsageLog.Date) Between [Enter Start Date: (mm/dd/yyyy)] And [Enter End Date: (mm/dd/yyyy)];

2nd Query: PcUsageLogByDateByLogoutByHits
SELECT Computer, Date, COUNT(Computer) AS Hits
FROM PcUsageLogByDateByLogout
GROUP BY Computer, Date;



This is the query from the database and in design view, I have already gone to Query, Parameters and added the 2 parameters of [Enter Start Date: (mm/dd/yyyy)] and [Enter End Date: (mm/dd/yyyy)]. After adding,Access still can't find the field '[Enter Start Date: (mm/dd/yyyy)]' referred to in your expression. On top of that it will still have the query parameter prompt dialog asking to key the parameter.



Question 1 : Is it possible to give an alias to the parameter [Enter Start Date: (mm/dd/yyyy)] so that when
With Forms(PcUsageLogByDateByLogoutByHitsGraph)
      .Controls("[Enter Start Date: (mm/dd/yyyy)]") = strControlName
it will recognise "[Enter Start Date: (mm/dd/yyyy)]" for the function?

Question 2 : For the code
With Forms("PcUsageLogByDateByLogoutByHitsGraph")
do I put the last query(2 query in total with the last query building on first query)or do I put my form name?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

n0v1cEAuthor Commented:
Anyone?
0
broesiCommented:
Hello N0v1cE,

since an answer has already been accepted, I considered the problem as solved. However:

With Forms(PcUsageLogByDateByLogoutByHitsGraph).Controls("[Enter Start Date: (mm/dd/yyyy)]")  you are trying to reference a control on a form. However, [Enter Start Date: (mm/dd/yyyy)] is actually a query parameter that cannot be referenced like this.

The easiest way would be to replace the query parameters with references to the controls on your form. This way, every time the query is run it will look up the values in the controls on your form (which has of course to be open for this to work).

Simply put this in the criteria row of the corresponding fields:

=forms("frmYourFormNameHere").form.txtYourTextboxNameHere

and remove the query parameters. The problem with this approach might be, that you cannot open the query only to look at the data - the form has to be open and meaningful values have to be in the referenced text boxes.

HTH,

broesi
0
n0v1cEAuthor Commented:
Hi broesi,

The easiest way would be to replace the query parameters with references to the controls on your form. This way, every time the query is run it will look up the values in the controls on your form (which has of course to be open for this to work).

- - - - - - - - - - - - - - - - - - - - - - - - -

Regarding this, I think you refer the form as a access form but I only have my form in my VB.exe program. Is it possible to refer to my VB.exe's DatePicker?
0
broesiCommented:
Ooops, didn't realize that you are talking about doing this from VB.. Sorry.
Nope, I don't think you can reference anything within a VB-form from a query :-(

One way could be to dynamically change the SQL-code of the query. But you have to be very careful if your app is multi-user...

Something like currentdb.querydefs("YourQueryNameHere").SQL="SELECT ..."

broesi
0
n0v1cEAuthor Commented:
broesi,

      'Retrieve Control Name
      strControlName = Left$(Me.OpenArgs, intPos - 1)
      'Retrieve Value to Assign
      strValue = Mid$(Me.OpenArgs, intPos + 1)

For this part of code in my access form_load(), it did managed to retrieve the value from my vb.exe program. So will it possible to assign the value to fill up the 2 query parameter dialog box or filter the form with the value and finally showing what is needed?

Sorry for my ingnorance
and
All the thanks for the help.
0
broesiCommented:
novice,

sorry, I think I do not really understand the architecture of your application. Could you please explain that a little further?

So you have an Access database file (MDB). That contains the tables and the query and a form.
Then you have a VB-exe. What exactly does this?

broesi
0
n0v1cEAuthor Commented:
broesi,

[VB.exe  program]
An application that the user interact. Interface for users to choose start date and end date from DTPicker1 and DTPicker2. On clicking the button generate, the VB.exe will open access and open the Form in this case form of a chart base on the DTPicker1 and DTPicker's value. The user will then be able to see the graph.
[VB.exe  program]

[Access database]
Table 1 : PCLog
Raw data of the PC information.
- - - - - - - - - - - - - - - - - - - - - - - - -
1st Query: PcUsageLogByDateByLogout
PARAMETERS [Enter Start Date: (mm/dd/yyyy)] DateTime, [Enter End Date: (mm/dd/yyyy)] DateTime;
SELECT PcUsageLog.Computer, PcUsageLog.Date, PcUsageLog.Action
FROM PcUsageLog
WHERE (PcUsageLog.Date) Between [Enter Start Date: (mm/dd/yyyy)] And [Enter End Date: (mm/dd/yyyy)];
- - - - - - - - - - - - - - - - - - - - - - - - -
2nd Query(built on 1st Query): PcUsageLogByDateByLogoutByHits
SELECT Computer, Date, COUNT(Computer) AS Hits
FROM PcUsageLogByDateByLogout
GROUP BY Computer, Date;
- - - - - - - - - - - - - - - - - - - - - - - - -
1 Form: PcUsageLogByDateByLogoutByHitsGraph
Generate graph base on 2nd Query.
[Access database]
0
broesiCommented:
Well, then it shouldn't be that difficult:

Once your VB-app has opened the Access form, it can write the date to text fields on that form (these fields could even be hidden fields!).
Then modify your query so it looks up it's parameters on the Access form (as described in one of my earlier postings).
You might need to requery the form or the graph.

HTH,

broesi
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.