Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help with lstbox vba code

Posted on 2014-07-16
7
Medium Priority
?
187 Views
Last Modified: 2014-07-16
I am trying to move information from selected items in a list box into a table. I keep receiving errors when I try to run the code.

This is what I have:

Private Sub btnAddSelected_Click()

Dim frm As Form
Dim ctl As Control
Dim intCounter As Integer

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set frm = Forms!("frm_Act_Enter")
Set ctl = frm![lstPrevRpts]

Set db = CurrentDb
Set rst = rst.OpenRecordset("Act_SubTo_Date")


If ctl.ItemsSelected.Count > 0 Then
    For intCounter = 0 To ctl.ItemsSelected.Count - 1
        rst.AddNew
            rst("ActID") = ctl.Column(5, intCounter)

        rst.Update
    Next intCounter
End If

rst.Close
Set rst = Nothing



End Sub

Open in new window


My error is "Type-declaration character does not match declared data type."


This may very well be one of those cases where I just missed something tiny, but I can't see it.

When the debugger runs, it highlights this line:

Set frm = Forms!("frm_Act_Enter")

Open in new window

0
Comment
Question by:Megin
  • 4
  • 3
7 Comments
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40199736
It should read:

Set frm = Forms("frm_Act_Enter")

/gustav
0
 

Author Comment

by:Megin
ID: 40199792
That error isn't happening now, but I have a new error.

"Run-Tim error '91': Object variable or With block variable not set." The line highlighted is:
Set rst = rst.OpenRecordset("Act_SubTo_Date")

Open in new window


I looked up the error and it appears to derive from an incorrect or missing Set statement. But I don't know which one I am missing. I thought I had set everything I need. I double checked that the name of the table was right.
The other thing I saw with that error was that it can be associated with With statements, but I don't have that in my code. Does it also associate with If statements?

Thank you for the help!
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40199801
Yes. That should read:

Set rst = db.OpenRecordset("Act_SubTo_Date")

/gustav
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Megin
ID: 40199831
ugh!
 
Now that is working! No more errors. Thank you very much for that.

Now I have another problem with the code not adding any information to my ActID field in the table.

Should I post this as a new question, or can I follow up in this thread.

Again, no errors. The code just isn't adding the actid to the table. It is creating a new, blank record.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40199853
Insert a debug line and perhaps add .Value:

        rst.AddNew
            Debug.Print intCounter, ctl.Column(5, intCounter)
            rst("ActID").Value = ctl.Column(5, intCounter)
        rst.Update

to see what it finds.

/gustav
0
 

Author Comment

by:Megin
ID: 40199881
The immediate window shows "0         Null"

I don't know what this means.

Now the code doesn't add anything.

You know what? You answered the original question. I am going to close this question and submit the new problem as a new question.

Thank you for all your help!
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40200281
You are welcome!

/gustav
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question