n0v1cE
asked on
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(strControlN
End If
End If
End Sub
Public Sub SendParameters(strControlN
With Forms(PcUsageLogByDateByLo
.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.
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(strControlN
End If
End If
End Sub
Public Sub SendParameters(strControlN
With Forms(PcUsageLogByDateByLo
.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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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: PcUsageLogByDateByLogoutBy
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(PcUsageLogByDateByLo
.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("PcUsageLogByDateByL
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?
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: PcUsageLogByDateByLogoutBy
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(PcUsageLogByDateByLo
.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("PcUsageLogByDateByL
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?
ASKER
Anyone?
Hello N0v1cE,
since an answer has already been accepted, I considered the problem as solved. However:
With Forms(PcUsageLogByDateByLo
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("frmYourFormNameHer
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
since an answer has already been accepted, I considered the problem as solved. However:
With Forms(PcUsageLogByDateByLo
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("frmYourFormNameHer
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
ASKER
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?
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?
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("YourQ
broesi
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("YourQ
broesi
ASKER
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.
'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.
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
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
ASKER
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): PcUsageLogByDateByLogoutBy
SELECT Computer, Date, COUNT(Computer) AS Hits
FROM PcUsageLogByDateByLogout
GROUP BY Computer, Date;
- - - - - - - - - - - - - - - - - - - - - - - - -
1 Form: PcUsageLogByDateByLogoutBy
Generate graph base on 2nd Query.
[Access database]
[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): PcUsageLogByDateByLogoutBy
SELECT Computer, Date, COUNT(Computer) AS Hits
FROM PcUsageLogByDateByLogout
GROUP BY Computer, Date;
- - - - - - - - - - - - - - - - - - - - - - - - -
1 Form: PcUsageLogByDateByLogoutBy
Generate graph base on 2nd Query.
[Access database]
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
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
Quotes missing...
With Forms("PcUsageLogByDateByL
Pete