Add New SubRecord if one doesn't exist.

[Guest]

Another basic question ... and my brain is fried... Please help.
Two tables are linked by RecID (one to many with referential integrity
added). The 1st table has RecID entered by AutoNumber upon the addition of
new records. The 2nd table doesn't have the RecID (number field) filled in
until a new record is added by clicking a button from the first table's form.
I want the "onclick" to FIRST look to see if the record is there and then
move to that record. If it is not there, then it needs to add the new record
and assign the correct RecID to the new record in the 2nd table. I'm so
close, but .... how...??

 

[Tim Ferguson]

I want the "onclick" to FIRST look to see if the record is there and
then move to that record. If it is not there, then it needs to add the
new record and assign the correct RecID to the new record in the 2nd
table. I'm so close, but .... how...??

' look to see if it's there already
if 0 = dcount("*", "MyTable2", "IDNumber = " & dwCurrentID) Then

' not there; put it there
strSQL = "insert into MyTable2 (IDNumber) " & vbnewline & _
"values (" & dwCurrentID & ")"

' and run it. The ADODB version is a bit different but you get
' the picture
db.Execute strSQL, dbFailOnError

' you need to force the form to see it
Forms("MyForm2").Refresh

end if

' we need to synchronise the form
with forms("MyForm2").recorsetclone

' go seek the new (or found) record
.findfirst "IDNumber = " & dwcurrentID
' and use the bookmark to place it
forms("MyForm2").bookmark = .bookmark

End with


Hope that helps


Tim F

 

[Steve Schapel]

Cydney,

One way this type of situation is often handled is to put a continuous
view form, based on 2nd table, as a subform on the 1st table form. Is
this an option for you? If so, I would expect your problem disappears.

Otherwise, I am not 100% clear in the interpretation of your question.
I assume you mean that at the moment clicking the button opens a
separate form for 2nd table records? And I assume you mean you want the
procedure to add a new table 2 record if there are *no* corresponding
table 2 records? If so, I suppose your Click event code could include
something like this...

If DCount("*","2nd table","[RecID]=" & Me.RecID)=0 Then
CurrentDb.Execute "INSERT INTO [2nd table] ( RecID ) VALUES ( " &
Me.RecID & " )"
End If

 

[Guest]

This is great. Both of your answers were helpful. Thank you.
However... and remember my brain was fried.... I resolved the issue myself.
Going back again and looking at the 2nd form (based on a table) I noticed
that the form was set to not allow new records added. Therefore it didn't
allow the referential integrity to take place as it naturally would. Anyway..
resolved. Thanks!

Cydney,

One way this type of situation is often handled is to put a continuous
view form, based on 2nd table, as a subform on the 1st table form. Is
this an option for you? If so, I would expect your problem disappears.

Otherwise, I am not 100% clear in the interpretation of your question.
I assume you mean that at the moment clicking the button opens a
separate form for 2nd table records? And I assume you mean you want the
procedure to add a new table 2 record if there are *no* corresponding
table 2 records? If so, I suppose your Click event code could include
something like this...

If DCount("*","2nd table","[RecID]=" & Me.RecID)=0 Then
CurrentDb.Execute "INSERT INTO [2nd table] ( RecID ) VALUES ( " &
Me.RecID & " )"
End If

--
Steve Schapel, Microsoft Access MVP

Another basic question ... and my brain is fried... Please help.
Two tables are linked by RecID (one to many with referential integrity
added). The 1st table has RecID entered by AutoNumber upon the addition of
new records. The 2nd table doesn't have the RecID (number field) filled in
until a new record is added by clicking a button from the first table's form.
I want the "onclick" to FIRST look to see if the record is there and then
move to that record. If it is not there, then it needs to add the new record
and assign the correct RecID to the new record in the 2nd table. I'm so
close, but .... how...??

 

[Steve Schapel]

Cydney,

Very pleased to know that it is working for you now. However, it may be
worth pointing out that Referential Integrity has got nothing to do with
whether the form allows additions or not, and in fact would not be
related at all to the question. The only way that Referential Integrity
would come into play in this situation would be if you tried to enter a
record on the 2nd table form with a RecID that does not exist in the 1st
table.