?
Solved

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

Posted on 2005-02-24
11
Medium Priority
?
371 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

777 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