List Box inserting into empty field in existing record

[cstanley]

Hello all,

I need help with something that I am not sure is possible. What I would like to do is the following:

I have two list boxes on the same form, call 'em List 1 and List 2. They might look like this:

List 1 List 2
A 1
B 2
C 6
D 10

What I would like to do is to be able to select multiple values from List 1 and a single value from List 2, and associate them together into a table where the values from List 1 already exist and the values from List 2 are just an empty field. Meaning, in this table, there are records with values A,B,C, etc., but empty spaces fore the 1 or 2 or whatever. So I can't use an "append record" command. How would I insert a value into an empty field in an existing record?

Thanks!

Chris

 

[dcx693]

You're going to need some VBA programming in there to go through each item that selected in ListboxA and set the found value in your underlying table to the choice made in ListboxB.

But what if someone's already chosen say, itemA before and the next person chooses itemA. Do you want the old value to be overwritten? Or do you want a new record written with itemA and the value from listboxB?

This situation sounds rather weird. Can I ask what your application is? We might be able to suggest a better way to structure it, if possible.

 

[cstanley]

Thanks for looking at this. Yes, I would like the old value to be overwritten. This is a form for modifying existing records, not creating new ones.

It is a little wierd - this is a one-time form that I am creating because the information was not put into the table at the time the original records were entered. I had to go back and create an extra field for the information, and now there are several thousand records.

The application is pertaining to assigning section numbers to text from documents. Basically, someone went through numerous documents and extracted text excerpts, and put them into the database. The excerpts are associated in a table with the ID of the documents themselves. Now we want to go back and drill down one level and enter the table of contents section number where the text came from into the table (e.g. 1.1, 1.2, etc.) does that make sense?

Any code examples would be appreciated. I have code for extracting items from a multi-select list box and associating them with a single value from a combo box and inserting them into a table as a new record. I just don't know how to modify existing records.

Thanks!

Chris

 

[WayneRyan]

Chris,

Dim dbs As Database
Dim rst As RecordSet
Dim sql As String

Set dbs = CurrentDb

'
' Method 1
'
sql = "Update YourTable " & _
      "Set    SomeField = '" & Me.SomeControl & "' " & _
      "Where  OtherField = '" & Me.OtherControl & "'"
dbs.Execute(sql)

'
' Method 2
'
sql = "Select * " & _
      "From   YourTable " & _
      "Where  OtherField = '" & Me.OtherControl & "'"
Set rst = dbs.OpenRecordset(sql)
If rst.EOF and rst.BOF Then
   MsgBox("No record found.")
   Exit Sub
End If

While Not rst.EOF and Not rst.BOF
  rst!SomeField = Me.SomeControl
  rst.MoveNext
  Wend

hth,
Wayne

 

[cstanley]

Hey, this looks good - I will try it.

So the "Where" statement is the value that already exists in the table and is being displayed in List 1?

Thanks,

Chris

 

[cstanley]

I'm getting an error when I use this code - the compiler stops on :

Dim dbs as Database

and says that a user-defined type is not defined.... what does this mean???


Thanks!

Chris

 

[WayneRyan]

Chris,

That is a reference problem.

Go to your code window; Tools - References; Select DAO.

For more info, do a search here on References, this is one
of the most common problems.

Wayne

 

[cstanley]

Thanks - my bad- I found the reference issue by searching on here about thirty seconds after I posted the message. Mea culpa, Mea culpa...

But now I have a new problem, of course - I keep getting a "Data Type Mismatch" error, but I checked the data from the queries behind each of the list boxes where it is coming from, and it matches up just fine, and the "bound column" points back to the right location in the query, and I'm stumped. My code looks like this:

sql = "Update tblDesignUserRequirements " & _
"Set [tblDesignUserRequirements].[SectionID] = '" & Me.[lstSectionID] & "' " & _
"Where [Index] = '" & Me.[lstRequirements] & "'"
dbs.Execute (sql)


So, I'm trying to put the value from the "lstSectionID" list box into the "SectionID" field of "tblDesignUserRequirements", where the "Index" field of that table matches the value in the "lstRequirements" list box.... yes?

Thanks!!!

Chris

 

[Bello]

Hi Chris,

I thought you can't include an item from multi-select listbox into SQL statement just like that. Your code should rather looks like this:

-------------------------------------------------------------------
Dim ItemIndex as Variant

For Each ItemIndex In lstRequirements.ItemsSelected

sql = "Update tblDesignUserRequirements " & _
"Set [tblDesignUserRequirements].[SectionID] = '" & Me.[lstSectionID] & "' " & _
"Where [Index] = '" & Me.[lstRequirements].ItemData(ItemIndex) & "'"
dbs.Execute (sql)

Next ItemIndex
--------------------------------------------------------------------

As for 'Type Mismatch' problem, I suspect your [SectionID] to be a numeric field. Perhaps you need to take off the quotes used on it:

sql = "Update tblDesignUserRequirements " & _
"Set [tblDesignUserRequirements].[SectionID] = " & Me.[lstSectionID] & _
"Where [Index] = '" & Me.[lstRequirements].ItemData(ItemIndex) & "'"


Hope that helps.

Bello

 

[cstanley]

So close, I think!

The data type mismatch error went away, but with this new code, I get a syntax error (missing operator) referencing the Where portion - is there something I need to add?

Thanks!

Chris

 

[WayneRyan]

cstanley,

You are probable missing a space in your SQL string.

If you can get your code in the Debug window, you can
inspect the string and it should be obvious.

wayne

 

[cstanley]

I've done that, and the compiler picks up nothing... any suggestions?

Chris

 

[WayneRyan]

cstanley,

Not the compiler.

View your code in the code window.

Click on the left-margin by the line where
your sql assignment is made. You should see
a little red circle.

Close the code window and run your form.

When the breakpoint is hit, you will be
back in the code window with your line
highlighted in yellow.

The F8 key will single-step you, the F5
will "run on".

If you hover the cursor over a variable
its contents (at least some of it) will
be displayed.

If you View --> Immediate Window
you can type ?sql and it will display
its value.

Then you shouldn't have much trouble seeing
the syntax error.

Wayne

 

[Bello]

Hi Chris,

I changed the where portion. Please try this one:

sql = "Update tblDesignUserRequirements " & _
"Set [tblDesignUserRequirements].[SectionID] = " & Me.[lstSectionID] & _
" Where [Index] = " & Chr(34) & Me.[lstRequirements].ItemData(ItemIndex) & Chr(34) & ";"

Hope it works.

Bello