looking for and adding records that don't exist

[Guest]

I have a question (probably a simple one) that I need help on. I am working
with testing trays in my laboratory work, each of which has a unique name and
72 wells (12 rows and 6 columns) each well can contain a patient specimen
or a control or be blank. In the data base, I have created a table
[Prelim180] that contains patient information for each well, the primary key
consists of [TrayNm], [RowNbr], [ColNbr]. The patient data is
programmatically imported from a CSV file using docmd.transfertext.
However, only patient data comes over. Control or empty wells are just
missing records.

After importing patient data to the [Prelim180] table, I examine the
imported record set, looking for wells that do not have patient data (i.e.
missing records). When I find a missing record, I want to programatically
ask the user for what control to place in that well and then add that record
to the [Prelim180] table. I am using the Dcount function and for next loops
to determine if the record exists, but I don't know how to then create a new
record in VB. Docmd.runSQL? what SQL string do I use to create a new record?

Thanks in advance for any help.

Scott D. Collins CHS(ABHI)
Laboratories At Bonfils
Denver, CO
(e-mail address removed)
(e-mail address removed)

 

[John Nurick]

Hi Scott,

To append a single record, you need to assemble a SQL statement
something like this in a string variable (which i'll call strSQL):

INSERT INTO Prelim180 (TrayNm, RowNbr, ColNbr, ControlID)
VALUES ('AB23', 4, 5, 99);

with your actual values from the recordset and user input in place of
the examples I've used, which would create a record for Tray AB23, Row
4, Column 5, with ControlID=99. Include other field names and
corresponding values as needed.

To execute it, usually it's preferable to create an object reference to
the current database with something like

Dim dbD as DAO.Database
Set dbD = DBEngine.Workspaces(0).Databases(0)
'or Set dbD = CurrentDB(), the differences between them
'get really arcane

and then use

dbd.Execute strSQL, dbFailOnError

I have a question (probably a simple one) that I need help on. I am working
with testing trays in my laboratory work, each of which has a unique name and
72 wells (12 rows and 6 columns) each well can contain a patient specimen
or a control or be blank. In the data base, I have created a table
[Prelim180] that contains patient information for each well, the primary key
consists of [TrayNm], [RowNbr], [ColNbr]. The patient data is
programmatically imported from a CSV file using docmd.transfertext.
However, only patient data comes over. Control or empty wells are just
missing records.

After importing patient data to the [Prelim180] table, I examine the
imported record set, looking for wells that do not have patient data (i.e.
missing records). When I find a missing record, I want to programatically
ask the user for what control to place in that well and then add that record
to the [Prelim180] table. I am using the Dcount function and for next loops
to determine if the record exists, but I don't know how to then create a new
record in VB. Docmd.runSQL? what SQL string do I use to create a new record?

Thanks in advance for any help.

Scott D. Collins CHS(ABHI)
Laboratories At Bonfils
Denver, CO
(e-mail address removed)
(e-mail address removed)

 

[Guest]

John,

Thank you very much for your assistance. Thats what I was looking for.

Sincerely,

Scott D. Collins, CHS(ABHI)
Laboratories At Bonfils
Denver, CO

Hi Scott,

To append a single record, you need to assemble a SQL statement
something like this in a string variable (which i'll call strSQL):

INSERT INTO Prelim180 (TrayNm, RowNbr, ColNbr, ControlID)
VALUES ('AB23', 4, 5, 99);

with your actual values from the recordset and user input in place of
the examples I've used, which would create a record for Tray AB23, Row
4, Column 5, with ControlID=99. Include other field names and
corresponding values as needed.

To execute it, usually it's preferable to create an object reference to
the current database with something like

Dim dbD as DAO.Database
Set dbD = DBEngine.Workspaces(0).Databases(0)
'or Set dbD = CurrentDB(), the differences between them
'get really arcane

and then use

dbd.Execute strSQL, dbFailOnError

I have a question (probably a simple one) that I need help on. I am working
with testing trays in my laboratory work, each of which has a unique name and
72 wells (12 rows and 6 columns) each well can contain a patient specimen
or a control or be blank. In the data base, I have created a table
[Prelim180] that contains patient information for each well, the primary key
consists of [TrayNm], [RowNbr], [ColNbr]. The patient data is
programmatically imported from a CSV file using docmd.transfertext.
However, only patient data comes over. Control or empty wells are just
missing records.

After importing patient data to the [Prelim180] table, I examine the
imported record set, looking for wells that do not have patient data (i.e.
missing records). When I find a missing record, I want to programatically
ask the user for what control to place in that well and then add that record
to the [Prelim180] table. I am using the Dcount function and for next loops
to determine if the record exists, but I don't know how to then create a new
record in VB. Docmd.runSQL? what SQL string do I use to create a new record?

Thanks in advance for any help.

Scott D. Collins CHS(ABHI)
Laboratories At Bonfils
Denver, CO
(e-mail address removed)
(e-mail address removed)