Too few arguments. Expected 1. (1 Viewer)

[shenty]

I am getting the above error trying to take an data from one listbox and copying it into a field in another listbox.

The first listbox is bound to a table of addresses. The second is a listbox of quotes. In theory there should be a one-to-many relationship but this was originally 2 tables from 2 seperate databases. I am doing a data cleanup operation using this method so that i can begin to restructure the whole database.

This is the code i have:

Private Sub btnLink_Click()
On Error GoTo Err_btnLink_Click

Dim i As Variant
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef
Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("qryQuotes")
For Each i In Me.listQuotes.ItemsSelected
rst.Edit
rst!AID = Me.listAddresses
rst.Update
rst.MoveNext
Next i
Me.listQuotes.Requery

Set rst = Nothing
Set qd = Nothing
Exit_btnLink_Click:
Exit Sub
Err_btnLink_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_btnLink_Click
End Sub

I believe the line in red is causing the error.

The code also does not work correctly as it is not copying the ID from the left table to the selected records in the right table.

Could anyone help with this 2 problems, it would be much appreciated.
Thanks all

P.S. Form attached

 

[shenty]

I have noticed that if i take this criteria out of the query "qryQuotes":-

Like "*" & [forms]![Address-Quotes]![txtSearch4] & "*"

Then i do not get the 3601 Too few arguments. Expected 1. Error message.

This is a bit of a pain as i was hoping to be able to filter the listboxes using the textboxes above them.

The code will then take the ID 2572 (in this example) and put the data into the AID field of the 2nd listbox but it does it for the top 6 records. Not the 6 i have selected.

Help or pointers appreciated.

 

[DCrake]

This error usually means that you have named a field that does not exist. Go to the design of the query and try and open it. See if it gives you the error.

Look for Expr1: in your column heading, etc

David

 

[DCrake]

Now you know what the problem is here is a document that will help your referring to variables form closed sources.

 

[shenty]

DCrake

Thanks for the response - i kind of understand what you mean (although i have never worked with Public Functions or Modules - being a relative newbie i'm still working on Privates).

So in this case i have a text box at the top of the listbox that has an 'on change' event. It passes the text to an invisible text box which the query is looking at. Then a requery is done. This is the code for that.

Private Sub txtSearch3_Change()

Dim vString As String
vString = Me.txtSearch3.Text
Me.txtSearch4.Value = vString
Me.listQuotes.Requery
If Me.listQuotes.ListCount = 0 Then
Me.txtMatching2 = 0
Else
Me.txtMatching2 = Me.listQuotes.ListCount - 1
End If
End Sub

How would i fix it without creating a duplicate query without the criteria or how would a write the criteria so that i don't get the error.

And once this is done how do i alter my code so that the ID 2572 (in example) only gets copied to the correct 6 records i select in the righthand listbox.

I am guessing it is something to do with (itemdata) but i am struggling.

Thanks again

 

[pbaldy]

Does this help?

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

 

[shenty]

Holy shit i've done it !

rst.FindFirst ("ID = " & Me.listQuotes.ItemData(i))
rst.Edit
rst!AID = Me.listAddresses
rst.Update
rst.MoveNext

I've added the line in red. Brilliant - now back to the Too few arguments problem......

It is ever so satifying when you fix a problem in access.....

I just don't know how to fix this one !!!

 

[pbaldy]

Did you miss the link in post 6?

 

[shenty]

The link is actually a page i'd found doing my research but it meant nothing to me.....

What i mean is i didn't understand it !

Thanks anyway.

I've got around it for now by copying the query and taking out the criteria in the one being used by the vba code and leaving it in for the one for the listbox.

But i would like to learn a better way of doing it.

 

[DCrake]

If you've read my document on passing public variables you would have solved your problem. Alot of users tend to refer to controls on forms in a query to apply a condition, this is fine if the query is only going to be used when the said form is open. Consider queries that have a date column that will act as a filter, you could either add parameters to your query which asks for a date range, good but not able to validate responses, or you could use a reference to a date range entered via a form.

Between Forms!FormName.ControlStartName And Forms!FormName.ControlEndName

Ok, but if try to run the query without the form being open you will get an error. My solution is to first create public variables at modular level, this means the variable will remain active throughout the duration of the active session.

Module:ModMain

Public dtmDateLower As Date
Public dtmDateUpper As Date

Next you create two functions

Function GetDateLower() As Date
    GetDateLower = dtmDateLower
End Function

Function GetDateUpper() As Date
    GEtDateUpper = dtmDateUpper
End Function

Then in ANY form that you want to handle a start and end date you would place the following on the AfterUpdate event of the control

Private Sub TxtDateLower_AfterUpdate()
   dtmDateLower = Me.TxtDateLower
End Sub

Private Sub TxtDateUpper_AfterUpdate()
   dtmDateUpper = Me.TxtDateUpper
End Sub

Finally, in any query that is subject to date filtering you would place the following under the column heading iin the condition row

Between GetDateLower() And GetDateUpper()

So what has happened is that the two controls on the form have passed the dates to the two public variables (dtmDateLower & dtmDateUpper). The query call each of the two functions (GetDateLower & GetDateUpper) which read the values in the public variables and pass them back to the query. This way there is no need for the form to be open or to add parameters to the query.

Hope this explains the situation more clearly.

David

 

[shenty]

Thanks DCrake

I am understanding what you are telling me more clearly now. And that is actually one of the things i wanted to learn more about when i was developing this database. I have ended up with multiple queries that are similar because i didn'y know how to do it otherwise.

I am going to try this sometime today and let you know how i get on.


I have read quite a few access books but non of them cover much VBA, are there any VBA books specifically that you would recommend ?. A friend of mine keeps saying i am wasting my time learning all this access stuff and that i should be getting into VB.net instead and Visual Studio & Visual Basic.....but i don't know why i find access so friendly when it comes to databases.

My next adventure will be to completely rewrite this database with all the learning i have done (and once i have done the data cleanup), and i want to make it accessible over the internet so me and my co workers can access the data from home via a website........would you recommend access for this or move onto learning something else ?

I appreciate your help.

 

[shenty]

DCrake - Brilliant it works !

Public FilterQuote As String

Function GetFilteredQuote() As String
GetFilteredQuote = FilterQuote
End Function

Private Sub txtSearch3_Change()

Dim vString As String
vString = Me.txtSearch3.Text
Me.txtSearch4.Value = vString
FilterQuote = Me.txtSearch4
Me.listQuotes.Requery
If Me.listQuotes.ListCount = 0 Then
Me.txtMatching2 = 0
Else
Me.txtMatching2 = Me.listQuotes.ListCount - 1
End If
End Sub

Then in my query:

Like "*" & GetFilteredQuote() & "*"

Thank you for that - you wouldn't believe how much work i can now do re-writing parts of this database

 

[DCrake]

Glad to be of help and you appreciate the work that members provide on this forum.

David

 

[jpindi]

I had the same problem and just applied the DCrake solution: it works and is going to help clean up my code quite a bit. Thank YOU!