"Filtering" default settings on a form 1

[bikerted]

I have used the following code (thanks to a tek-tips contributor) very successfuly, but now wish to enable it for multi-users according to the value receiptlocationID:

Private Sub Form_Current()
Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
If Me.NewRecord Then
Set db = CurrentDb
SQL = "SELECT TOP 1 [ReceiptID], receiptlocationID, Book, Receipt, ReceiptDate, ProjDeptID, LocationSuffixID, AccountID " & _
"FROM Receipts " & _
"ORDER BY [ReceiptID],[receiptlocationID]DESC;"
Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
If Not (rst.BOF Or rst.EOF) Then
Me!Combo172 = Nz(rst!receiptlocationID, "")
Me!Book.DefaultValue = Nz(rst!Book, "")
Me!Receipt.DefaultValue = Nz(rst!Receipt + 1, "")
Me!ReceiptDate.DefaultValue = "#" & Format(Nz(rst!ReceiptDate, Date), "yyyy-mm-dd") & "#"
Me!ProjDeptID.DefaultValue = Nz(rst!ProjDeptID, "")
Me!LocationSuffixID.DefaultValue = Nz(rst!LocationSuffixID, "")
Me!AccountID.DefaultValue = Nz(rst!AccountID, "")
Me!Combo124.DefaultValue = Nz(Me!LocationSuffixID, "")
Me!Combo80.DefaultValue = Nz(Me!AccountID, "")
Me!Amount.DefaultValue = 0
Else
End If
Set rst = Nothing
Set db = Nothing
End If
End Sub

I thought adding the latter field to the SQL would enable one to find the last record by ReceiptID and by receiptlocationID. Am I barking up the wrong tree or am I just barking? Any assistance would be greatfully received.

Ted.

 

[TheAceMan1]

How are ya bikerted . . .

[blue] . . . find the last record by ReceiptID and by receiptlocationID.[/blue]

You have to [blue]sort decending[/blue] on both fields and [blue]in the proper order[/blue], so its:

[blue]"ORDER BY [purple][ReceiptID][/purple] DESC, [receiptlocationID] DESC;"
   [green]'or[/green]
"ORDER BY [receiptlocationID] DESC, [purple][ReceiptID][/purple] DESC;"[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[bikerted]

Thanks Aceman,

I've tried your suggestion as follows:

Private Sub Form_Current()
Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
If Me.NewRecord Then
Set db = CurrentDb
SQL = "SELECT TOP 1 [receiptlocationID], Book, Receipt, ReceiptDate, ProjDeptID, LocationSuffixID, AccountID " & _
"FROM receiptsinputqry " & _
"ORDER BY [receiptlocationID] DESC, [ReceiptID] DESC;"
Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
If Not (rst.BOF Or rst.EOF) Then
Me!Combo172.DefaultValue = Nz(rst!receiptlocationID, "")
Me!Book.DefaultValue = Nz(rst!Book, "")
Me!Receipt.DefaultValue = Nz(rst!Receipt + 1, "")
Me!ReceiptDate.DefaultValue = "#" & Format(Nz(rst!ReceiptDate, Date), "yyyy-mm-dd") & "#"
Me!ProjDeptID.DefaultValue = Nz(rst!ProjDeptID, "")
Me!LocationSuffixID.DefaultValue = Nz(rst!LocationSuffixID, "")
Me!AccountID.DefaultValue = Nz(rst!AccountID, "")
Me!Combo124.DefaultValue = Nz(Me!LocationSuffixID, "")
Me!Combo80.DefaultValue = Nz(Me!AccountID, "")
Me!Amount.DefaultValue = 0
Else
End If
Set rst = Nothing
Set db = Nothing
End If
End Sub

Trouble is I'm getting the following familiar error message:

Run-time error'3061': To few parameters. Expected 1.

I say familiar, because (after some deliberation on a previous occasion I found the missing item was a control name which I had substituted with it's field name). I can't see it this time. I must admit I'm confused by the layout of this procedure, though I understand what it's supposed to achieve. The major change I have made is to base the repeating and incrementing default value fields on a query containing the Receipts Table fields. The idea is to select the receiptlocationID when opening the form and have all the controls containing the last entered values (or value + 1) for only that receiptlocationID - since my users only work on one location's receipts. I also need the receiptlocationID to repeat (if that's not enough already!). I hope that makes some sense?

Ted

 

[PHV]

What are the fields returned by receiptsinputqry ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bikerted]

PHV,

They're all the same fields as in Receipts table (on which I had based this form). The only difference is that I decided to base the form on a parameter query which queries the Receipts table based on receiptlocationID, because now I have three sets of default data to present the three different users. I was hoping (and just unsuccessfully attempted this) to open ReceiptsInput form as any of the three users by selecting the receiptlocationID on a pop-up from my main database form. The hope was that each user would only see their default values. I hope that's clear, because I'm in several minds as to how to approach this now.

Sorry if I'm a little vague.

Ted.

 

[PHV]

OK, receiptsinputqry is a parametized query, right ?
Could you please post its SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bikerted]

PHV,

This is the SQL:

SELECT Receipts.receiptlocationID, Receipts.ReceiptID, Receipts.[Payee ID], Receipts.ServiceUserID, Receipts.ServiceDate1, Receipts.ServiceDate2, Receipts.Book, Receipts.Receipt, Receipts.ReceiptDate, Receipts.AccountingDate, Receipts.ProjDeptID, Receipts.LocationSuffixID, Receipts.AccountID, Receipts.[Method of Payment], Receipts.Fund, Receipts.IncomeType, Receipts.Amount, Receipts.[Gift Aid], Receipts.GiftAmtExt, Receipts.Comments
FROM Receipts
WHERE (((Receipts.receiptlocationID)=[Forms]![rcptofficefrm]![receiptoffice]));

I should say I removed the receiptlocationID from the ORDER BY part of the procedure, and as I suspected the correct last-entered values re-appeared in my form. I tried opening the form with the single parameter from the above query and unfortunately it didn't picked out the last entered receipt for selected receiptlocationID.

Ted

 

[PHV]

I guess you shouldn't close the rcptofficefrm form.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bikerted]

PH,

The query works fine in selecting the parameter but I can't stop this error 3061 when using the query name in the ORDER BY part of the procedure, because, if the Receipts table is referred to here then the default values will be those in the overall table and not the queried/filtered part. I just can't see what is causing the error to generate - any ideas?

Ted.

 

[PHV]

any ideas
Yes, provided the rcptofficefrm form is still open:

SQL = "SELECT TOP 1 receiptlocationID, ReceiptID, Book, Receipt, ReceiptDate, ProjDeptID, LocationSuffixID, AccountID " & _
"FROM Receipts " & _
"WHERE receiptlocationID='" & Forms!rcptofficefrm!receiptoffice & "' " & _
"ORDER BY receiptlocationID DESC, ReceiptID DESC"

If receiptlocationID is defined as numeric then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bikerted]

Well PH,

No surprise, it works, only I was able to close rcptofficefrm - I have the query running from Combo172, which is on the ReceiptInput form. It's perfect - sorry to have got you so embroiled in my confusion. I hope to write my own SQL when I obtain a brain.

Thanks a million,
Ted