I'm Getting Postal

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Guys or Girls,

I'm trying to add a new record to current db from a list box. The help
section examples require to much modification and I'm not good at that.

Anyway, I have a listbox named "lstAdmin". All I want to do is add whatever
is selected in "lstAdmin" as a new record to the current database.

Can anyone provide a coding example for me?

-Simon
 
A "database" contains tables, queries, reports, forms,... Apparently you are
attempting to add a record to a table. The table must have a field that you
want to contain the value of the selection in the list box control. You can
use code in the after update event of the list box like:

DoCmd.RunSQL "Insert into tblAdmins ([AdminField]) Values (""" & Me.lstAdmin
& """)"

I have no idea why you would want to do this but the code should work in you
substitute your table and field names and the field is text.
--
Duane Hookom
MS Access MVP


"S
 
Simon,

Was this list box working before or is it something you are trying to add?

I will assume that your are adding the list box. If I have interpreted your
request correctly, you can simply use the list wizard. With your form in the
design view make sure your toolbox is open. If not, you can open the toolbox
by the menu; "View" then click "Toolbox". Once your toolbox is open, make
sure your control wizard’s button is depressed. Then select the list box
tool. Drag your mouse depressing the right mouse button on the location you
want your list box and release the button. Follow the wizard’s instructions.
If you have a table that has the desired values you want in the combo menu
select I want to look up the values in a table or query. If not you can
choose I will type the values myself. To choose the values yourself just type
them in the order you want them to appear on your list and them choose the
field in your table you want the value entered to.

If you use the "I want to look up the values in a table or query" this is a
good selection especially if you have say a customer table with a CustomerID
field as your primary key (Unique value) and another field with the customers
name. You can choose both fields in the combo wizard and choose which column
is visible i.e. customer name and which column is hidden and records in your
table i.e., CustomerID (storing customer names takes more data storage space
than a CustomerID and numbers process faster in access than names/text). If
you have 7,000 records and you place a customer name in each record, and the
customer names are repeated many times in your table, your database grows to
larger size than if you have one Customer table with say ten customer names
and you use a CustomerID from that table to post the value in the table in
your original question you will both save space and make queries quicker for
reporting or other uses.

Next name the label or title of your list box and its done. Check the list
box properties out, noting where and how the wizard entered your information
in the properties to see how to do it without the wizard.

Dennis
 
Duane and Don,

Yes, I'm trying to add records from the Admin table to the Main table via
the List Box. Here are specs below:


Form Name = TrainingDay
Record Source: Main

List Box Name = lstAdmin
Control Source: none
RowSource Type: Table/Query
Row Source: SELECT Admin.DocNumber, Admin.DocTitle FROM Admin;
Column Count: 2
Column Widths: 1";1"
Bound Column: 1


Can someone please show me what the code is to save the records. I will be
most grateful to you for your help with this.

-Simon
 
Guys,

This may be helpful too:

Fields in table Main:
MainID, DocTitle, DocNumber

Fields in table Admin:
AdminID, DocTitle, DocNumber
 
Guys or Girls,

I'm trying to add a new record to current db from a list box. The help
section examples require to much modification and I'm not good at that.

Anyway, I have a listbox named "lstAdmin". All I want to do is add whatever
is selected in "lstAdmin" as a new record to the current database.

Can anyone provide a coding example for me?

-Simon
Click to expand...

Typically a new record has multiple fields; a listbox has only one
value. What table are you adding the new record TO? What information
needs to be in the record - just a single field selected from the
listbox?

Bear in mind that a Listbox DOES NOT CONTAIN ANY DATA. It's a display
and editing tool; the data resides in a Table, or (sometimes) a list
of values, which constitute the RowSource of the listbox. If you're
working on the assumption that a listbox is a data repository, that
might be the source of your problems!

John W. Vinson[MVP]
 
Hi John,

Yes, I was thinking that the List Box was a data repository. John, can you
help me with this?

I'm trying to display a table named Admin and have the user select from the
choices listed. Once they choose them I want to be able to store whatever
their choices were inside an existing table called Main. Can this be done
John?

-Simon
 
John,

What needs to be added to the table called Main is the data in two fields
from the table called Admin:

DocNumber and DocTitle

-Simon
 
Simon,

If I'm not mistaken you need "Admin.DocNumber" in the control source field
of the properties assuming you want to capture DocNumber in your Table.
Control source is the field where you want to store the value of your Select
query. Bound column 1 is the first column in your Select query which in the
case of your supplied data is DocNumber.

Dennis
 
Dennis,

I created a button and in it's OnClick event put this code segment:

Dim itm as Integer
For Each itm In Me.lstAdmin.ItemsSelected
Debug.Print lstAdmin.Column(0, itm) & " " & lstAdmin.Column(1, itm)
Next

Afterwards, I selected two rows in the List Box and clicked the button I
created. Looking in the Immediate Window I saw the following:

A-FA-001 Wastewater from Laboratories
A-FA-002 HVAC Control System

A-FA-001 and A-FA-002 are from the DocNumber field in the Admin table. The
two descriptive lines are from the DocTitle field in the Admin table. So, I
know the data can be extracted from the List Box. What I don't know is how to
put it in a different table after I extract it.

I know it's not a difficult procedure. It's cool though. Eventually I'll
figure out a way to do it.

Thanks though for your reply.

-Simon
 
To All Who Responded,

Thanks to all who tried helping me solve my question. Your replies as a
group helped me make sense of this scenario. I have things working now with
help from many people.

Here's the code segment that I used:

Private Sub lblAdd_Click()

If lstAdmin.ItemsSelected.Count = 0 Then
MsgBox "No items selected.", vbExclamation, "Can't add records"
Exit Sub
End If

Dim rs As Recordset, db As Database, sql As String
Dim SelectedItem As Variant
sql = "Select * From Main"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenDynaset)

For Each SelectedItem In lstAdmin.ItemsSelected
rs.AddNew
rs("DocNumber") = Me.lstAdmin.Column(0, SelectedItem)
rs("DocTitle") = Me.lstAdmin.Column(1, SelectedItem)
rs.Update
Next

rs.Close
Set db = Nothing
Set rs = Nothing

Me.sfrmAdmin.Requery
End Sub

-Simon
 
What needs to be added to the table called Main is the data in two fields
from the table called Admin:

DocNumber and DocTitle
Click to expand...

I'm very dubious.

If you have the relationship between DocNumber and DocTitle defined in
Admin, then it is

VERY BAD DESIGN

to store the DocTitle redundantly in Main. You could end up with a
situation where DocNumber 1234 has one title in Admin, and a different
title in Main (quite apart from the wasted space).

I would suggest removing the DocTitle field from Main altogether. If
you want to see the title in conjunction with the other fields in
Main, use a Combo Box (storing the DocNumber and displaying the
DocTitle), or a Query joining the two tables.

John W. Vinson[MVP]
 
John,

Dude, knowing that you even noticed that irregularity tells me you are
extremely good at what you do. If Access was more like "C++" I could just
store a pointer to DocTitle and save that space, but you're absolutely right
about the poor design.

Since I'm a newby at Access, I didn't know how else to approach it. The way
I have the tables split up is probably more to blame for that blunder.

Right now I have 5 tables of training manuals that are grouped according to
department with unique department numbers. Each manual has a short
description attached. So, when a user selects one or more training manuals
from a List Box, those selections get stored in the main table.

After selections have been saved, a subform gets updated which shows the
user what they have selected so far. The Subform has a date field that gets
filled in when an employee has completed his/her training in the selected
catagories.

Of course, the DocTitles don't really need to be saved with the DocNumber
since they're already visible in the List Box. So thank you John for steering
me back to that oversight.

I'm going to remove those DocTitles from the main table dude.

John Vinson, U R definitely D Man :-)

-Simon
 
John,

Dude, knowing that you even noticed that irregularity tells me you are
extremely good at what you do. If Access was more like "C++" I could just
store a pointer to DocTitle and save that space, but you're absolutely right
about the poor design.

Since I'm a newby at Access, I didn't know how else to approach it. The way
I have the tables split up is probably more to blame for that blunder.
Click to expand...

Relational design is NOT intuitive. I've been doing it for years and
still sometimes get "normalization headaches"!
Right now I have 5 tables of training manuals that are grouped according to
department with unique department numbers. Each manual has a short
description attached. So, when a user selects one or more training manuals
from a List Box, those selections get stored in the main table.
Click to expand...

Well... let's go a bit further then. Storing these manuals in five
tables is, again, less than ideal design. If your five tables have the
same general structure, then consider storing them all in ONE table,
with a DepartmentNumber field. If you want to see all the manuals for
one department, use a Query selecting that department. You gain
nothing but hassles by having five different tables for the same
"type" of data!
After selections have been saved, a subform gets updated which shows the
user what they have selected so far. The Subform has a date field that gets
filled in when an employee has completed his/her training in the selected
catagories.

Of course, the DocTitles don't really need to be saved with the DocNumber
since they're already visible in the List Box. So thank you John for steering
me back to that oversight.

I'm going to remove those DocTitles from the main table dude.

John Vinson, U R definitely D Man :-)
Click to expand...

<blush> Thanks!

John W. Vinson[MVP]
 
Hi John,

Dude, I've done a revamp of the database since we last communicated. Last
night I realized all I need to have in the main table are Foreign Keys to the
other tables and just relate them all back to Main. I kept looking at your
posts and a light came on in my head and it all made sense.

Dude, I think you are a genie or something. I have another genie friend in
the forum named "PC Datasheet". He/She had the same impact in helping me see
things more clearly.

I thank God for people like you and others like Duane Hookum, Lynn Trapp,
Stephen Lebans and many others who are champions in their field.

When I grow up I want to be just like all of you :-) Thanks for everything
John.

-Simon
 
Back
Top