Update field in separate table for new (unique) records

[Oliversmo]

What is the easiest way to add a field from a current record to another table in the same database through form entry. I only want new (unique) records to be added. (Also, the field is created through a 'hold table' that stores a 2 digit yy reference and a 4 digit numeric incrementing string.)

 

[kathryn]

Can you clarify a bit? You only want new (unique) records added; do you mean unique to the table that the form is based on or unique to the table you want them added to?

Could you give us table names? It will make it easier to help you. Also, what do you mean by the field being created through a 'hold table'? Do you run some code to populate the field? Kathryn

 

[Oliversmo]

The tables are Projects and Testing and the field name for both is ProjectNo. The reason I used the term unique is because I only want new ProjectNo(s) added to the Testing table from the form Project that feeds the table Project. I do not want an entry when the form is merely editing (updating) an existing record. (I was thinking of using the "after insert"?)

 

[kathryn]

You could use the after update event of the field and first make sure that the value for ProjectNo does not exist in the testing file.

Something like this pseudo code:

Sub ProjectNo_AfterUpdate()

dim rst as recordset
dim strSQL as string

strSQL = "Select * from Testing where ProjNo= " & me!ProjNo & ";"
set rst = currentdb.openrecordset(strsql)

If rst.bof and rst.eof then
strSQL = "INSERT INTO testing (ProjNo) VALUES (Me!projNo);"
docmd.runsql strSQL
end if



This assumes that ProjNo is a number field. If it is a text field you need to add single quotes around it in the first SQL statement.

This should get you going.
Kathryn

 

[Oliversmo]

With a few minor tweaks....that fixed it, but how do I turn off the "you are about to append 1 record" warning?

 

[kathryn]

Add the following before you do the append:

docmd.setwarnings false


Don't forget to turn warnings back on after the append:

docmd.setwarnings true Kathryn

 

[Oliversmo]

That worked for the warning, but now there is a new issue. The following code:
Private Sub Form_AfterUpdate()

Dim rst As Recordset
Dim strSQL As String

DoCmd.SetWarnings False

strSQL = "Select * from Projects where ProjectNo= '" & Me!ProjectNo & "';"
Set rst = CurrentDb.OpenRecordset(strSQL)

If rst.BOF And rst.EOF Then
strSQL = "INSERT INTO Testing (projectNo) VALUES (" & Me!ProjectNo & &quot ;"
DoCmd.RunSQL (strSQL)
End If

strSQL = "Select * from Projects where ProjectNo= '" & Me!ProjectNo & "';"
Set rst = CurrentDb.OpenRecordset(strSQL)

If rst.BOF And rst.EOF Then
strSQL = "INSERT INTO [Development Schedule] (projectNo) VALUES (" & Me!ProjectNo & &quot ;"
DoCmd.RunSQL (strSQL)

DoCmd.SetWarnings True

End If

End Sub

Places an entry in both of the new tables, but rather than placing the entry 01-0204 (as an example) it enters 203. It seems as if it is performing subtraction before posting the entry. How can I make it place the literal string 01-0204 in the field(s)?

 

[kathryn]

strSQL = "INSERT INTO [Development Schedule] (projectNo) VALUES ('" & Me!ProjectNo & "') ;"

I added single quotes before the doublequotes. The result will be something like

INSERT INTO [Development Schedule] (projectNo) VALUES ('01-0204') ;

The only other thing to check is that the field is defined to be a text field in the table. Kathryn

 

[Oliversmo]

What changes would need to be implemented to move two fields from the form over to another table. For instance, what if I wanted to move the ProjectNo and System fields from the Project form to the testing table? The above code that I am using is only moving the ProjectNo field from the Projects form/table to the testing table.