Adding data to table from VBA

[xavier]

I am currently able to use the microsoft comm control 6 to get data from an
external device. Using the MSComm1_OnComm() Private Sub saving the data to a
buffer then from the buffer to a record in the table. The problem that I am
incountering is that it puts the data in as a new record everytime. My
question is how would I get the next comm event to save in a different field
of the same record Below is a bit of the code that I am using. Any advise or
guidance is appreciated.


Private Sub MSComm1_OnComm()
Dim EVMsg$
Dim ERMsg$
Text9.SetFocus
'Branch according to the CommEvent property.
Select Case MSComm1.CommEvent
'Event messages.
Case comEvReceive
Dim Buffer As Variant
Buffer = MSComm1.Input
Text9.Text = Buffer
'handling return character at end of string
Buffer = Left(Buffer, Len(Buffer) - 1)
'Setting to ignore Validation of add new input
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO EmployeeControlNumber (ControlNumber)
Values('" & Buffer & "')"
'Setting back to normal
DoCmd.SetWarnings True
Case comEvSend
Case comEvCTS
EVMsg$ = "Change in CTS Detected"
End Select
End Sub

 

[xavier]

I forgot to mention that I am using Access 2000

 

[Ron Weiner]

Xavier

Sounds like you need to change the logic a little so that you buffer up all
of the data that makes a complete record in a big string, or string array,
or byte array, or whatever other container you are comfortable with. Then
when you have validated that you have enough data to comprise a good record,
process the big string, or string array, or byte array, or whatever and
insert the whole thing using one Sql update query as you are doing now.
Looks to me like you are 90% there.

Ron W