Help with listbox and append VBA

[deejayquai]

Hi

I'm attempting to append multiple values into a new record, using
multiple criteria from a listbox. I've got the basics for the code
below but I get an "Error 3085 Undefined Function" for the Dlookup part
of it.
Dim db As Database
Dim rec As DAO.Recordset
Dim varSelected As Variant
' Now loop through your selected items and place each selected item
into the table
' as an individual record
Set db = CurrentDb()
Set rec = db.OpenRecordse t("tblReportCom ments")
For Each varSelected In Me!lstGrpMem.It emsSelected
rec.AddNew
rec("[Group Member ID]") = Me!lstGrpMem.Co lumn(0, varSelected)
'Access Debug highlights this code below:
rec("[Basic Skills and Tactics]") = DLookup("[Basic Skills and
Tactics]", "tblAttainmentI ndicators", _
"[Attainment Level]= Me!lstGrpMem.Co lumn(6, varSelected)And [Attainment
Gender] = Me!lstGrpMem.Co lumn(4,varSelec ted)")
rec.Update
Next varSelected
Set db = Nothing ' Clear db...
MsgBox ("This data is now in your table..")

Grateful for any help and advice.

David

[Gox]

de********@gmai l.com wrote:

Hi

I'm attempting to append multiple values into a new record, using
multiple criteria from a listbox. I've got the basics for the code
below but I get an "Error 3085 Undefined Function" for the Dlookup part
of it.
Dim db As Database
Dim rec As DAO.Recordset
Dim varSelected As Variant
' Now loop through your selected items and place each selected item
into the table
' as an individual record
Set db = CurrentDb()
Set rec = db.OpenRecordse t("tblReportCom ments")
For Each varSelected In Me!lstGrpMem.It emsSelected
rec.AddNew
rec("[Group Member ID]") = Me!lstGrpMem.Co lumn(0, varSelected)
'Access Debug highlights this code below:
rec("[Basic Skills and Tactics]") = DLookup("[Basic Skills and
Tactics]", "tblAttainmentI ndicators", _
"[Attainment Level]= Me!lstGrpMem.Co lumn(6, varSelected)And [Attainment
Gender] = Me!lstGrpMem.Co lumn(4,varSelec ted)")
rec.Update
Next varSelected
Set db = Nothing ' Clear db...
MsgBox ("This data is now in your table..")

Grateful for any help and advice.

David

David, try this:

DLookup("[Basic Skills and
Tactics]", "tblAttainmentI ndicators", _
"[Attainment Level]=" & Me!lstGrpMem.Co lumn(6, varSelected) & " And
[Attainment Gender]=" & Me!lstGrpMem.Co lumn(4,varSelec ted))

[Anthony England]

<de********@gma il.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

Hi

I'm attempting to append multiple values into a new record, using
multiple criteria from a listbox. I've got the basics for the code
below but I get an "Error 3085 Undefined Function" for the Dlookup part
of it.
Dim db As Database
Dim rec As DAO.Recordset
Dim varSelected As Variant
' Now loop through your selected items and place each selected item
into the table
' as an individual record
Set db = CurrentDb()
Set rec = db.OpenRecordse t("tblReportCom ments")
For Each varSelected In Me!lstGrpMem.It emsSelected
rec.AddNew
rec("[Group Member ID]") = Me!lstGrpMem.Co lumn(0, varSelected)
'Access Debug highlights this code below:
rec("[Basic Skills and Tactics]") = DLookup("[Basic Skills and
Tactics]", "tblAttainmentI ndicators", _
"[Attainment Level]= Me!lstGrpMem.Co lumn(6, varSelected)And [Attainment
Gender] = Me!lstGrpMem.Co lumn(4,varSelec ted)")
rec.Update
Next varSelected
Set db = Nothing ' Clear db...
MsgBox ("This data is now in your table..")

Grateful for any help and advice.

David

You are missing quotes in the last argument of the dlookup bit. However, we
can't be sure how many quotes you are missing because we don't know if any
of the columns are text columns. The result needs to be something like:
MyNumeric=123 AND MyText="Whateve r"
I would dimension a separate variable, strWhere, to hold this last bit and
build it up slowly and carefully, before using it in the dlookup function.
In fact, you could do a debug.print or msgbox for the strWhere to make sure
it is properly formed.
A final thought is that the dlookup is slow and calling it repeatedly is
often to be avoided. You may have so few records to append that it makes
little difference, but you could open a read-only recordset which has the
necessary data from tblAttainmentIn dicators and loop through this to add the
records.

[deejayquai]

Hi

Thanks for both replies. I've tried both suggestions and for both I
now get Run Time Error 2471: The expression you entered as a query
parameter produced this error: "The Object doesn't contain the
automation object 'Male."

"Male" is the gender of the Dlookup end criteria, and so is a text
field. I'm now assuming I'm missing quotes somewhere?

Here is my amended code:

--------------------
Dim db As Database
Dim rec As DAO.Recordset
Dim varSelected As Variant
Dim strWhere As String
' Now loop through your selected items and place each selected item
into the table
' as an individual record
Set db = CurrentDb()
Set rec = db.OpenRecordse t("tblReportCom ments")
For Each varSelected In Me!lstGrpMem.It emsSelected
strWhere = Me!lstGrpMem.Co lumn(4, varSelected)
rec.AddNew
rec("[Group Member ID]") = Me!lstGrpMem.Co lumn(0, varSelected)
rec("[Basic Skills and Tactics]") = DLookup("[Basic Skills and
Tactics]", "tblAttainmentI ndicators", _
"[Attainment Level]=" & Me!lstGrpMem.Co lumn(6, varSelected) & " And
[Attainment Gender] = " & strWhere)
rec.Update
Next varSelected
Set db = Nothing ' Clear db...
MsgBox ("This data is now in your table..")
--------------

Again thanks for any help.

David

[Anthony England]

<de********@gma il.com> wrote in message
news:11******** *************@g 44g2000cwa.goog legroups.com...

Hi

Thanks for both replies. I've tried both suggestions and for both I
now get Run Time Error 2471: The expression you entered as a query
parameter produced this error: "The Object doesn't contain the
automation object 'Male."

"Male" is the gender of the Dlookup end criteria, and so is a text
field. I'm now assuming I'm missing quotes somewhere?

Here is my amended code:

--------------------
Dim db As Database
Dim rec As DAO.Recordset
Dim varSelected As Variant
Dim strWhere As String
' Now loop through your selected items and place each selected item
into the table
' as an individual record
Set db = CurrentDb()
Set rec = db.OpenRecordse t("tblReportCom ments")
For Each varSelected In Me!lstGrpMem.It emsSelected
strWhere = Me!lstGrpMem.Co lumn(4, varSelected)
rec.AddNew
rec("[Group Member ID]") = Me!lstGrpMem.Co lumn(0, varSelected)
rec("[Basic Skills and Tactics]") = DLookup("[Basic Skills and
Tactics]", "tblAttainmentI ndicators", _
"[Attainment Level]=" & Me!lstGrpMem.Co lumn(6, varSelected) & " And
[Attainment Gender] = " & strWhere)
rec.Update
Next varSelected
Set db = Nothing ' Clear db...
MsgBox ("This data is now in your table..")
--------------

Again thanks for any help.

David

Do not be afraid of using too many variables, lots of white space and
comments - I can't say I've tested this, but I think this is right. You can
see how I build up the strWhere surrounding the text column with quotes and
then try to get a value from tblAttainmentIn dicators. If I do, I add the
record.
Dim lngMemberID As Long
Dim lngLevel As Long
Dim strGender As String
Dim strWhere As String
Dim varAttainment As Variant

With Me.lstGrpMem
For Each varSelected In .ItemsSelected
lngMemberID = CLng(.Column(0, varSelected))
lngLevel = CLng(.Column(6, varSelected)
strGender = .Column(4, varSelected)
strWhere = """" & strGender & """"
strWhere = "[Attainment Gender]=" & strWhere
strWhere = strWhere & " AND [Attainment Level]="
strWhere = strWhere & CStr(lngLevel)
varAttainment = DLookup("[Basic Skills and Tactics]",
"tblAttainmentI ndicators", strWhere)
If Not IsNull(varAttai nment) Then
rec.AddNew
rec.Fields("[Group Member ID]") = lngMemberID
rec.Fields("[Basic Skills and Tactics]") = CLng(varAttainm ent)
rec.Update
End If
Next varSelected
End With
Hope this works without error, but you may need to tweak it - the point is
to do it slowly.

As I mentioned before, repeated use of dlookup can slow things down or cause
other problems. You could perhaps set up the listbox based on a query, so
that one of the columns holds the attainment - so you can get this value
directly from one of the listbox columns without looking it up later.

A further point is that, when you are adding multiple records like this, you
should wrap it in a transaction. This ensures that you either add all the
required records or you add none. As the code is currently, there may be
some error which means that you get half way through the loop and some
records are added and some are not.

[deejayquai]

Anthony thanks again for you help and patience!

Right- I've had a go with your code and I get an error 3464 Data
type-mistmatch in criteria expression:

varAttainment = DLookup("[Basic Skills and Tactics]",
"tblAttainmentI ndicators", strWhere)

I then tweaked the earlier code with some of your suggestions and it
adds a new record with the correct Group Member ID and with no apparent
errors- but does not add anything for the Basic Skills and Tactics. I
tried separating the variables in a MsgBox to see if they were set at
the right value and they are- attain brings the correct attianment
level from the listbox column 6 and strWhere brings the correct gender
from listbox column 4. I then tried assigning a variable to the entire
LookUp bit and msgBox this and it results in a null value error- so I
presume it's not DlookingUp properly!?

Here is my code:

----------------------------
Dim db As Database
Dim rec As DAO.Recordset
Dim varSelected As Variant
Dim strWhere As String
Dim attain As Long

' Now loop through your selected items and place each selected item
into the table
' as an individual record
Set db = CurrentDb()
Set rec = db.OpenRecordse t("tblReportCom ments")

For Each varSelected In Me!lstGrpMem.It emsSelected
strWhere = lstGrpMem.Colum n(4, varSelected)
attain = lstGrpMem.Colum n(6, varSelected)
rec.AddNew
rec.Fields("[Group Member ID]") = Me!lstGrpMem.Co lumn(0,
varSelected)
rec.Fields("[Basic Skills and Tactics]") = DLookup("[Basic Skills
and Tactics]", "tblAttainmentI ndicators", _
"[Attainment Level]=" & attain & " And [Attainment Gender] = " & """
strWhere""")
rec.Update
Next varSelected

----------------------------

I do want to be able to loop this for potentially hundreds of records
selected in my list box but I don't know what you mean by wrap it in a
transaction. I guess what I want to do is far exceeding my Access/VBA
skills, which is very frustrating!

thanks

David

[Anthony England]

<de********@gma il.com> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.com.. .

Anthony thanks again for you help and patience!

Right- I've had a go with your code and I get an error 3464 Data
type-mistmatch in criteria expression:

varAttainment = DLookup("[Basic Skills and Tactics]",
"tblAttainmentI ndicators", strWhere)

I then tweaked the earlier code with some of your suggestions and it
adds a new record with the correct Group Member ID and with no apparent
errors- but does not add anything for the Basic Skills and Tactics. I
tried separating the variables in a MsgBox to see if they were set at
the right value and they are- attain brings the correct attianment
level from the listbox column 6 and strWhere brings the correct gender
from listbox column 4. I then tried assigning a variable to the entire
LookUp bit and msgBox this and it results in a null value error- so I
presume it's not DlookingUp properly!?

Here is my code:

----------------------------
Dim db As Database
Dim rec As DAO.Recordset
Dim varSelected As Variant
Dim strWhere As String
Dim attain As Long

' Now loop through your selected items and place each selected item
into the table
' as an individual record
Set db = CurrentDb()
Set rec = db.OpenRecordse t("tblReportCom ments")

For Each varSelected In Me!lstGrpMem.It emsSelected
strWhere = lstGrpMem.Colum n(4, varSelected)
attain = lstGrpMem.Colum n(6, varSelected)
rec.AddNew
rec.Fields("[Group Member ID]") = Me!lstGrpMem.Co lumn(0,
varSelected)
rec.Fields("[Basic Skills and Tactics]") = DLookup("[Basic Skills
and Tactics]", "tblAttainmentI ndicators", _
"[Attainment Level]=" & attain & " And [Attainment Gender] = " & """
strWhere""")
rec.Update
Next varSelected

----------------------------

I do want to be able to loop this for potentially hundreds of records
selected in my list box but I don't know what you mean by wrap it in a
transaction. I guess what I want to do is far exceeding my Access/VBA
skills, which is very frustrating!

thanks

David

Hi David
Perhaps we need to do one basic thing first:
Ensure we can get a single line of code working involving DLookup.
Find a valid entry in your table, such as Gender=Male and Level=4 and note
the value of the field it should return. Now press CTRL-G to bring up the
immediate window and type in the following:

?DLookup("[Basic Skills and Tactics]", "tblAttainmentI ndicators",
"[Attainment Gender]=""Male"" AND [Attainment Level]=4")

This should return the value you noted down if it doesn't, then there is a
basic problem such as the field or table name being mis-spelled. I'll send
you an e-mail so you can contact me directly if you need to.