Combo Box

[Ruth]

Hi again

I am trying to get a combo box (called "Combo119)in a form called Trip
Entry, with 2 columns called "Customer Name" and "Customer Code" to save both
in a Table called Tracking Table. Customer Code is a primary key. I went to
the propery box under events, in the after update I entered the following:

Private Sub Combo119_AfterUpdate()

Me.Tracking_Table.Column(2).Value = Me.Combo119.Column(1)
Me.Tracking_Table.Column(1).Value = Me.Combo119.Column(2)

Please let me know what is wrong with it.

 

[Jeff Boyce]

Ruth

"...to save both..." implies you want to save both the Customer Name and the
Customer Code. Why?!

Access is a relational database. If you save a [CustomerID], you can use a
query to connect that CustomerID back to Name and Code and (any other
customer-specific data). You don't want to (and only very rarely need to
replicate data in more than one table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Douglas J. Steele]

You cannot use Column with tables. Not only that, but you can't write to
tables in that manner.

For the sake of argument, I'll assume that the fields in Tracking_Table are
named CustomerCode and CustomerName (it's a bad idea, by the way, to put
spaces in field or table names). If the form is bound to Tracking_Table (or
a query based on it), you can use

Me.CustomerCode = Me.Combo119.Column(0)
Me.CustomerName = Me.Combo119.Column(1)

(Note that the Column collection starts numbering at 0, so that the first
column is 0, the second column is 1)

If your form is not bound to Tracking_Table, you'll have to use SQL to
update it.

DIm strSQL As String

strSQL = "INSERT INTO Tracking_Table " & _
"(CustomerCode, CustomerName) " & _
"VALUES (" & Me.Combo119.Column(0) & ", " & _
Chr$(34) & Me.Combo119.Column(1) & Chr$(34) & ")"
CurrentDb.Execute strSQL, dbFailOnError

I've assumed that CustomerCode is a numeric field. If it's text, you'll need
quotes around that value as well (Chr$(34) is a double quote):

strSQL = "INSERT INTO Tracking_Table " & _
"(CustomerCode, CustomerName) " & _
"VALUES (" & Chr$(34) & Me.Combo119.Column(0) & Chr$(34) & ", " & _
Chr$(34) & Me.Combo119.Column(1) & Chr$(34) & ")"
CurrentDb.Execute strSQL, dbFailOnError

 

[Ruth]

Thank-you very much. It worked. I have never done coding before, so I quite
happy with it, thanks again!

Ruth
--
Thank-you!
Ruth

You cannot use Column with tables. Not only that, but you can't write to
tables in that manner.

For the sake of argument, I'll assume that the fields in Tracking_Table are
named CustomerCode and CustomerName (it's a bad idea, by the way, to put
spaces in field or table names). If the form is bound to Tracking_Table (or
a query based on it), you can use

Me.CustomerCode = Me.Combo119.Column(0)
Me.CustomerName = Me.Combo119.Column(1)

(Note that the Column collection starts numbering at 0, so that the first
column is 0, the second column is 1)

If your form is not bound to Tracking_Table, you'll have to use SQL to
update it.

DIm strSQL As String

strSQL = "INSERT INTO Tracking_Table " & _
"(CustomerCode, CustomerName) " & _
"VALUES (" & Me.Combo119.Column(0) & ", " & _
Chr$(34) & Me.Combo119.Column(1) & Chr$(34) & ")"
CurrentDb.Execute strSQL, dbFailOnError

I've assumed that CustomerCode is a numeric field. If it's text, you'll need
quotes around that value as well (Chr$(34) is a double quote):

strSQL = "INSERT INTO Tracking_Table " & _
"(CustomerCode, CustomerName) " & _
"VALUES (" & Chr$(34) & Me.Combo119.Column(0) & Chr$(34) & ", " & _
Chr$(34) & Me.Combo119.Column(1) & Chr$(34) & ")"
CurrentDb.Execute strSQL, dbFailOnError