Solved

Access 2007 Adding to db through listbox

Posted on 2011-03-22
3
304 Views
Last Modified: 2012-05-11
Here's what I got going on.  I have an Access database with a table called TA.  I am making a form to add records to this table.  We have a scanner to scan serial numbers in to prevent typo's.  I came up with a form to use to support the scanner. Here is what we want to add

All serial numbers in list box will have the same PO #
(txtPObulk) I have a text box asking what the PO is
(lstSystemTag) I have a list box that we put all the serial numbers in.

I want to add all the serials numbers with the PO that was entered into the table TA

I was able to come up with a loop to cycle through the list, but I can't figure out how to add them to the table.  

I appreciate the help.
Private Sub Command31_Click()
Dim j As Long
With Me.lstSystemTag
    For j = 0 To .ListCount - 1
               'MsgBox Me.lstSystemTag.Column(0, j), vbOKOnly, "Item number:" & j + 1
    Next j
End With
End Sub

Open in new window

0
Comment
Question by:inimicaljords
  • 2
3 Comments
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 35193454
I normally handle this with an append query
Example:
Private Sub Command31_Click()  
Dim j As Long  
With Me.lstSystemTag  
    For j = 0 To .ListCount - 1  
               'MsgBox Me.lstSystemTag.Column(0, j), vbOKOnly, "Item number:" & j + 1  
              CurrentDB.Execute "Insert into TA (field1, field2) Values( " & Me.Field1 & ", " & Me.lstSystemTag.Column(0, j)  & ")" 
    Next j  
End With  

End Sub

Open in new window


*** AIR CODE - Example for syntax
*** You will need to substiture the actual field names and form control names.

0
 
LVL 1

Author Comment

by:inimicaljords
ID: 35194767
I don't quite understand what to put in your line of code.  I tried inputting field names but its not working.  I am not real sure on the syntax.  I am a bit rusty on this stuff.

only 2 fields I am changing is PO  and Systemtag, both are in a table called TA.  There are other fields in this table but this is all I want to input.

I am wanting the PO to be the same for all systemtag that are in the list.  So I only have to type the PO once and then I can scan the serial numbers into the list.
0
 
LVL 1

Author Comment

by:inimicaljords
ID: 35198081
I got it to work with the following code.  Had to change it a little but figured it out.  Thanks!
CurrentDb.Execute "INSERT INTO TA (PO, SystemTag) VALUES ('" & Me.txtPObulk & "', '" & Me.lstSystemTag.Column(0, j) & "')"

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now