Re: Parameter Query Form Problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

How would I requery it.

Here is my button code.

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click
Dim stDocName As String

stDocName = "Trial"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.Requery
Exit_Command16_Click:
Exit Sub

Err_Command16_Click:
MsgBox Err.Description
Resume Exit_Command16_Click

End Sub


Addy wrote:
Thanks Allen, I shall try you method and let you know the results.

This is what I used to make my query, its was a Microsoft support site.

http://support.microsoft.com/kb/304428/EN-US/


Allen Browne wrote:
There is probably a timing issue here.

When the form first loads, Access has to run the query before it can assign
values to the controls on the form. But it needs the values from the
controls on the form so it can run the query. Catch 22. You might be able to
verify that this is the problem by issuing a Requery after it loads. If that
works, it is the chicken'n'egg problem.

A workaround might be to build the SQL statement for the form from the
unbound boxes that have a value. Say you add a command button for the user
to click after she enters the limiting values she wants. The Click event of
the command button would build the WHERE clause from the controls that have
a value, and patch it in between the stub of the SQL string (TRANSFORM and
SELECT clauses) and the tail (ORDER BY and PIVOT clauses.)

This kind of thing (comletely untested aircode--concept only):

Dim strWhere As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

Const strcStub = "TRANSFORM Sum([Hours Consumed].[Adjusted Hours]) AS
[SumOfAdjusted Hours] " & _
"SELECT [Hours Consumed].ContractNumber, " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].ContractEndDate, " & _
" [Hours Consumed].[Service Order Number], " & _
" [Hours Consumed].[Sold to Customer Name], " & _
" Format([ActivityStartDate],""dddd', 'mmm d yyyy"") AS ActivityStart, " & _
" [Hours Remaining].[Hours Used], " & _
" [Hours Remaining].HoursRemaining, " & _
" [Hours Remaining].[Target Hours], " & _
" Sum([Hours Consumed].[Adjusted Hours]) AS [Adjusted Hours] " & vbCrLf & _
"FROM [Hours Consumed] INNER JOIN [Hours Remaining] " & _
" ON ([Hours Consumed].[Sold to Customer Name] " & _
" =[Hours Remaining].[Sold to Customer Name]) " & _
" AND ([Hours Consumed].ContractNumber=[Hours Remaining].ContractNumber) "

Const strcTail = "GROUP BY [Hours Consumed].ContractNumber, " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].ContractEndDate, " & _
" [Hours Consumed].[Service Order Number], " & _
" [Hours Consumed].[Sold to Customer Name], " & _
" Format([ActivityStartDate],"dddd"", ""mmm d yyyy"), " & _
" [Hours Remaining].[Hours Used], " & _
" [Hours Remaining].HoursRemaining, " & _
" [Hours Remaining].[Target Hours], " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].ContractEndDate " & vbCrLf & _
"ORDER BY [Hours Consumed].ContractNumber, " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].[Service Order Number] " & vbCrLf & _
"PIVOT [Hours Consumed].ActivityType; " & _

'Build the WHERE clause from the unbound controls with a value.
If Not IsNull(Me.cbo_client) Then
strWhere = "([Hours Consumed].[Sold to Customer Name] = """ & _
Me.cbo_client & """) AND "
End If
If Not IsNull(Me.txt_start_date) Then
strWhere = strWhere & "([Hours Consumed].ContractStartDate >= " & _
Format(Me.txt_start_date, strcJetDate) & ") AND "
End If
If Not IsNull(Me.txt_end_date) Then
strWhere = strWhere & "([Hours Consumed].ContractStartDate < " & _
Format(Me.txt_end_date + 1, strcJetDate) & ") AND "
End If
If Not IsNull(Me.cbo_cont_num) Then
strWhere = strWhere & "([Hours Consumed].ContractNumber = """ & _
Me.cbo_cont_num & """) AND "
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen > 0 Then
strWhere = " WHERE " & Left$(strWhere, lngLen)
End If

'Finally: assign the whole thing as the RecordSource of the form.
Me.RecordSource = strcStub & strWhere & strcTail

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Addy" <adnan.zamir@xxxxxxxxx> wrote in message
news:1149559159.398645.305300@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have a crosstab query which is two joined tables giving a sum of
hours worked.

Now on my form I want to be able to select certain fields for a
criteria.

I want to search by

* Sold to Customer Name
* ContractStartDate (Greater than or equal to)
* ContractEndDate (Less than or equal to)
* ContractNumber

I want to search for all of these fields or even a single one. It is
not important that the user selects all for criterias. I have made a
form which has the following

* Combo Box for searching Customer Name
* Text Box for searching the start date
* Text Box for searching the End date
* Combo Box for searching Contract Number


I have pasted my code below. The problem is that when I run this query
directly WITHOUT the form, the logic works perfectly. however when I
run it via the form, it always gives blank records. It seems to me that
the form control values are not being passed to the query parameters
properly.

Here is my code.

PARAMETERS [Forms]![Transaction Details]![cbo_client] Text ( 255 ),
[Forms]![Transaction Details]![cbo_cont_num] Text ( 255 ),
[Forms]![Transaction Details]![txt_start_date] DateTime,
[Forms]![Transaction Details]![TXT_end_date] DateTime;
TRANSFORM Sum([Hours Consumed].[Adjusted Hours]) AS [SumOfAdjusted
Hours]
SELECT [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].ContractEndDate, [Hours
Consumed].[Service Order Number], [Hours Consumed].[Sold to Customer
Name], Format([ActivityStartDate],"dddd"", ""mmm d yyyy") AS
ActivityStart, [Hours Remaining].[Hours Used], [Hours
Remaining].HoursRemaining, [Hours Remaining].[Target Hours], Sum([Hours
Consumed].[Adjusted Hours]) AS [Adjusted Hours]
FROM [Hours Consumed] INNER JOIN [Hours Remaining] ON ([Hours
Consumed].[Sold to Customer Name]=[Hours Remaining].[Sold to Customer
Name]) AND ([Hours Consumed].ContractNumber=[Hours
Remaining].ContractNumber)
[b]WHERE ((([Hours Consumed].[Sold to Customer
Name])=Forms![Transaction Details]!cbo_client Or Forms![Transaction
Details]!cbo_client Is Null) And (([Hours
Consumed].ContractStartDate)>=Forms![Transaction
Details]!txt_start_date Or ([Hours
Consumed].ContractStartDate)>=Forms![Transaction
Details]!txt_start_date Is Null) And (([Hours
Consumed].ContractEndDate)<=Forms![Transaction Details]!txt_End_date Or
([Hours Consumed].ContractEndDate)<=Forms![Transaction
Details]!txt_End_date Is Null) And (([Hours
Consumed].ContractNumber)=Forms![Transaction Details]!cbo_cont_num Or
Forms![Transaction Details]!cbo_cont_num Is Null))[/b]
GROUP BY [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].ContractEndDate, [Hours
Consumed].[Service Order Number], [Hours Consumed].[Sold to Customer
Name], Format([ActivityStartDate],"dddd"", ""mmm d yyyy"), [Hours
Remaining].[Hours Used], [Hours Remaining].HoursRemaining, [Hours
Remaining].[Target Hours], [Hours Consumed].ContractStartDate, [Hours
Consumed].ContractEndDate
ORDER BY [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].[Service Order Number]
PIVOT [Hours Consumed].ActivityType;

.

Relevant Pages

  • Re: Parameter Query Form Problem
    ... This is what I used to make my query, its was a Microsoft support site. ... controls on the form so it can run the query. ... ContractStartDate ... Combo Box for searching Customer Name ...
    (microsoft.public.access.formscoding)
  • Re: Parameter Query Form Problem
    ... Access has to run the query before it can assign ... controls on the form so it can run the query. ... ContractStartDate ... Combo Box for searching Customer Name ...
    (microsoft.public.access.formscoding)
  • Re: Parameter Query Form Problem
    ... Tips for Access users - http://allenbrowne.com/tips.html ... This is what I used to make my query, its was a Microsoft support site. ... controls on the form so it can run the query. ... Combo Box for searching Customer Name ...
    (microsoft.public.access.formscoding)
  • Re: Parameter Query Form Problem
    ... This is what I used to make my query, its was a Microsoft support site. ... controls on the form so it can run the query. ... ContractStartDate ... Combo Box for searching Customer Name ...
    (microsoft.public.access.formscoding)
  • dates in query HELP PLEASE!
    ... I got some controls in a form, I'm trying to run a query with the values of ... Then I append the where string to the rest of the SQL string, ... mm/dd/yyyy format when running a query. ...
    (microsoft.public.access.forms)