[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Access 2007 Adding to db through listbox

Posted on 2011-03-22
3
Medium Priority
?
354 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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