Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2005-02-24
11
Medium Priority
?
378 Views
Last Modified: 2008-03-17
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.
0
Comment
Question by:n0v1cE
  • 5
  • 5
11 Comments
 
LVL 5

Accepted Solution

by:
broesi earned 1200 total points
ID: 13390656
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
 
LVL 77

Expert Comment

by:peter57r
ID: 13390658
Hi n0v1cE,
Quotes missing...
  With Forms("PcUsageLogByDateByLogoutByHitsGraph")


Pete
0
 

Author Comment

by:n0v1cE
ID: 13399024
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:n0v1cE
ID: 13412899
Anyone?
0
 
LVL 5

Expert Comment

by:broesi
ID: 13413322
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
 

Author Comment

by:n0v1cE
ID: 13414226
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
 
LVL 5

Expert Comment

by:broesi
ID: 13414315
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
 

Author Comment

by:n0v1cE
ID: 13416836
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
 
LVL 5

Expert Comment

by:broesi
ID: 13417749
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
 

Author Comment

by:n0v1cE
ID: 13417850
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
 
LVL 5

Expert Comment

by:broesi
ID: 13418292
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question