Feed values from a text box to a table

  • Thread starter Thread starter Dan Hovden via AccessMonster.com
  • Start date Start date
D

Dan Hovden via AccessMonster.com

Hello!
I've been using Access for just a few weeks and for several days (and nights) I have tried to feed the values of a multi-select listbox into a table. After searching the web for some time I found out that this wasn't easy. I've never done programming before. I've found a lot of different codes on the web, but I don't know what they mean and where to
put them. I would be greatful if someone could help med with this.
When I open the form in design view and choose the properties of the list box, and 'event', I can write codes for the "On Click" event when I click on the build button (...) and choose Code Builder.
Is this a good place to start? Since I don't know anything about programming the easyest thing for me to do is to get someone
to write the codes for me, so I can just cut and paste it into the "On clic [Event Procedure]" (if that is the right place
to put the code) I don't know if there is someone out there who would be that kind, but I'll give it a try.

Here are som info about my project:
The name of the List Box: lstGenre
The name of the form: frmCollection
The name of the table that the List Box values
are being fed to: tblGenre

Thanx!!!

Daniel
 
Hello!
I've been using Access for just a few weeks and for several days (and nights) I have tried to feed the values of a multi-select listbox into a table. After searching the web for some time I found out that this wasn't easy. I've never done programming before. I've found a lot of different codes on the web, but I don't know what they mean and where to
put them. I would be greatful if someone could help med with this.
When I open the form in design view and choose the properties of the list box, and 'event', I can write codes for the "On Click" event when I click on the build button (...) and choose Code Builder.
Is this a good place to start? Since I don't know anything about programming the easyest thing for me to do is to get someone
to write the codes for me, so I can just cut and paste it into the "On clic [Event Procedure]" (if that is the right place
to put the code) I don't know if there is someone out there who would be that kind, but I'll give it a try.

Here are som info about my project:
The name of the List Box: lstGenre
The name of the form: frmCollection
The name of the table that the List Box values
are being fed to: tblGenre
Click to expand...

Well... sorry. I won't (for free) rewrite the code for you, but here's
a sample; this uses the Click event of a button named cmdProcess to
read data from a listbox named lstHealthIssues (the name of the form
is irrelevant; it populates the table AnimalCondition. If the
comments, and perhaps using the online Help in the VBA editor, isn't
enough to let you adapt this, post back with your specific questions
and I or someone else should be able to help.

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the
' selections in the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table, newly cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
' setting the Dirty property of a form to False forces
' a write to disk.
If Me.Dirty = True Then
Me.Dirty = False
End If
' open a reference to the current database
Set db = CurrentDb
' Open a Recordset based on the table; this is how you write to a
' table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
' ListCount is the number of rows in the listbox
For iItem = 0 To .ListCount - 1
' select the first column of the current row
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
' currently in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it by using the AddNew method of the Recordset
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
' close what you opened, put away what you got out
rs.Close
Set rs = Nothing
Set db = Nothing
' requery the Subform which also displays the data. Omit this
' if you're not using a subform along with the listbox
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub



John W. Vinson[MVP]
 
<<start of code snipped>>
' close what you opened, put away what you got out
rs.Close
Set rs = Nothing
Set db = Nothing
<<end of code snipped>>

I really like that comment in there John.
That is a very interesting, and easily remembered,
way to look at the cleanup code.
 
I renamed my listbox lstHealthIssues, made a commandbutton (cmdProcess), made a new table (AnimalCondition) and pasted your code into the 'on click' event procedure of the button. When I chose values from the listbox and hit the button I got this error message: "Microsoft Visual Basic. Compile error: Method or data member not found" and the '.AnimalID' part of " rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _ " was highlighted.

Those 'AnimalID' and 'HealthIssueID' ...are they columns in the AnimalCondition table, or do they have something to to with the listbox?

Now my AnimalCondition table consists of these two columns. Can you please explain this a little bit closer? I really want it to work before I try to change the code, so it fits my wish.

I really appreciate your help.

Daniel
 
I renamed my listbox lstHealthIssues, made a commandbutton (cmdProcess), made a new table (AnimalCondition) and pasted your code into the 'on click' event procedure of the button. When I chose values from the listbox and hit the button I got this error message: "Microsoft Visual Basic. Compile error: Method or data member not found" and the '.AnimalID' part of " rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _ " was highlighted.
Click to expand...

Well... I was *thinking* that you'ld adapt my code to your database
rather than adapting your database to my code! said:
Those 'AnimalID' and 'HealthIssueID' ...are they columns in the AnimalCondition table, or do they have something to to with the listbox?
Click to expand...

They are the columns in the AnimalCondition table, which is a
"resolver" table between Animals (AnimalID primary key) and
HealthIssues (HealthIssueID primary key). This table resolves the many
to many relationship between Animals and Conditions - an animal may
have two or three medical conditions, and many animals may have each
condition.

In your case, if you don't already have one, you'll need a similarly
structured table to resolve the many to many relationship between
Recordings and Genres. There are many recordings of "chamber music"
and of "baroque music"; and the Vivaldi concerto I'm listening to is
in both categories. This table should have the unique ID of the
recording, and the unique ID of the genre (which could simply be the
name of the genre, you don't need an autonumber in this case).
Now my AnimalCondition table consists of these two columns. Can you please explain this a little bit closer? I really want it to work before I try to change the code, so it fits my wish.
Click to expand...

The error message is because the code assumes that the Form contains a
field named AnimalID in its Recordsource ("Me.AnimalID"). In your case
you'ld presumably have a field for the RecordingID which you would use
instead.

John W. Vinson[MVP]
 
First of all. I've learned more about access the last 24h than at the one-week course I took.

And second....When you're talking about "resolver." Do you mean relationships between different columns in different tables?? In that case I have made direct relationships between the "HealthIssueIDs" from the AnimalCondition- and the HealthIssues tables, and the "AnimalIDs" from the AnimalCondition- and the Animals tables.

Do these relationships imply that whatever is fed into one column also is fed into the relationship-column?

And I assume that all the different HealthIssues (or in my case Genres) are to be listed in the HealthIssueID as the only column. I used this column as the row source for the lstHealthIssues listbox.

Here comes the problem. When I hit the button I get this error message:
"Microsoft Access. Error 2465 in cmdProcess_Click: Microsoft Access can't find the field 'lstHealthIssues' referred to in your expression.

I don't understand this. The listbox is most definitely named lstHealthIssues and is in the same form as the button.

Daniel
 
First of all. I've learned more about access the last 24h than at the one-week course I took.

And second....When you're talking about "resolver." Do you mean relationships between different columns in different tables?? In that case I have made direct relationships between the "HealthIssueIDs" from the AnimalCondition- and the HealthIssues tables, and the "AnimalIDs" from the AnimalCondition- and the Animals tables.
Click to expand...

A Many to Many relationship requires THREE tables: in your case,
Recordings, Genres, and a table to list which recordings are in which
genre. This third table is often called a "Resolver" table. It is
related one-to-many to the Recordings table, and also related
one-to-many to the Genres table.
Do these relationships imply that whatever is fed into one column also is fed into the relationship-column?
Click to expand...

No, certainly not!

You add a new record to the resolver table to establish a
relationship. For instance, if you have a recording of Vivaldi's
Concerto for Two Flutes, you would create a record in the resolver
table with that recording's ID and "Baroque", and then a second record
for the same ID and "Chamber Music".
And I assume that all the different HealthIssues (or in my case Genres) are to be listed in the HealthIssueID as the only column. I used this column as the row source for the lstHealthIssues listbox.
Click to expand...

You'll have a table of Genres with each genre listed once. The
resolver table will have each genre repeated as many times as you have
recordings in that genre. That is, if you have 37 chamber music
recordings, there will be 37 records (all with different
RecordingID's) in the table.
Here comes the problem. When I hit the button I get this error message:
"Microsoft Access. Error 2465 in cmdProcess_Click: Microsoft Access can't find the field 'lstHealthIssues' referred to in your expression.

I don't understand this. The listbox is most definitely named lstHealthIssues and is in the same form as the button.
Click to expand...

Then I don't understand it either! You're not using a Subform are you?
The code assumes that there is a Listbox control with a Name property
of lstHealthIssues; doublecheck that this listbox in fact has this
Name (not its caption or its control source, but the name of the
control).

John W. Vinson[MVP]
 
I made a completly new database, and this time it found lstHealthIssues.
Anyhow, I got more errors. Run-time error '3464' (I got this one in norwegian, and in english it would be something like) The field data types do not correspond with this condition. When I clicked on 'Debug' this was highlighted: "rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition"
The first time I choose a value in the listbox I get no errors, and the value is fed to the AnimalCondition table, but if I make a multi select, or the next time I choose something i get this error.

Daniel
 
I made a completly new database, and this time it found lstHealthIssues.
Anyhow, I got more errors. Run-time error '3464' (I got this one in norwegian, and in english it would be something like) The field data types do not correspond with this condition. When I clicked on 'Debug' this was highlighted: "rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition"
Click to expand...

That sounds like you might have used a Text datatype for your AnimalID
field. My database's AnimalID is a Long Integer.
The first time I choose a value in the listbox I get no errors, and the value is fed to the AnimalCondition table, but if I make a multi select, or the next time I choose something i get this error.
Click to expand...

Very odd! I'm not sure what is going on... anyone?

By the way: AccessMonster seems to post text without word wrap; your
message scrolled across parts of three screens. Any chance you could
gripe to the system administrators about "playing nice" with other
newsreader software?

John W. Vinson[MVP]
 
By the way: AccessMonster seems to post text without word wrap; your
message scrolled across parts of three screens. Any chance you could
gripe to the system administrators about "playing nice" with other
newsreader software?

I don't understand. Do you want med to use a newsgroup software? What is the newsgroup address?
That sounds like you might have used a Text datatype for your AnimalID
Click to expand...
field. My database's AnimalID is a Long Integer.

My AnimalID field is an AutoNumber data type.
I'm close to giving up this project if I don't make some progress soon.

Daniel
 
Back
Top