Add record using VBA code

[nick.h]

Hey guys,



I have a report that displays data from three different tables, and I would like to avoid having it ever show up blank (due to no data in one table or another). The best way that I was able to come up with solving that problem is to have some logic that:

1) checks to see if there is a record in the second and third tables with the current FK in it (PK on table 1 is a foreign key in tables 2 and 3). The FK is unique in the other tables

2) If there is no record with the current FK then add a record with the FK in it.

3) If there is a record with this FK then do nothing.

Is there a different way to go about this? The big thing is I don't ever want to have this report show up completely blank! Let me know if you have any questions about any of this.

Thanks for your time!
Nick

[hertfordkc]

The following link is a concise description of working with recordsets using VBA.
http://allenbrowne.com/ser-29.html
Item 8 is of particular interest to you.

[nick.h]

Still having issues. Here is the code that I'm trying to use to add a recrod, but to no avail. What is the problem with it?

Set db = CurrentDb
Set rs = db.OpenRecordset("GKInfo")
DoCmd.Save , "frmBusinessMain"
If Not rs.BOF Then
    Do Until rs.EOF
        If Me!BID = rs("BID") Then
        Exit Sub
        Else: With rs
                .AddNew
                !BID = Me!BID
                !AddedBy = StrUserName
                !MyDSC = StrMyDSC
                .Update
                .Close
            End With
            Exit Do
        End If
        rs.MoveNext
        Loop
    End If

[RuralGuy]

Do not mix single line and multi-line code. Change this line: Else: With rs

[nick.h]

I'm sorry, but I don't understand. What should I change it to? I've been teaching myself (rather poorly it seems!) and I'm not sure what you mean by single line and multi line

[hertfordkc]

Still having issues. Here is the code that I'm trying to use to add a recrod, but to no avail. What is the problem with it?

Set db = CurrentDb
Set rs = db.OpenRecordset("GKInfo")
DoCmd.Save , "frmBusinessMain"
If Not rs.BOF Then
    Do Until rs.EOF
        If Me!BID = rs("BID") Then  'I'm not familar with rs("Bid") syntax, if it works...great
        Exit Sub
         'Else: With rs
;should be 
  Else:
  with rs
                 .AddNew
                !BID = Me!BID
                !AddedBy = StrUserName
                !MyDSC = StrMyDSC
                .Update
                .Close
            End With
            Exit Do
        End If
        rs.MoveNext
        Loop
    End If

You used a loop, but my first take on the code is that only one record at most will be added????

[nick.h]

Yep only one record. I am trying to get it to look and find out if there is a record that contains the unique FK "BID", and if there is a record with BID then just end the routine. If there is NOT a record with that BID I want it to create a record.

Yes, only one record at most.

[hertfordkc]

Set db = CurrentDbSet rs = db.OpenRecordset("GKInfo")
DoCmd.Save , "frmBusinessMain"
' If you need to read through the recordset, then I would change the next line to : rs.movefirst
and remove the if NOT.....

If Not rs.BOF Then

Do Until rs.EOF
If Me!BID = rs("BID") Then
'I'm not familar with rs("Bid") syntax, if it works...great
' However, I suspect you want: = rs.[BID]

Exit Sub
'Else: With rs
'should be
Else:
with rs
.AddNew
!BID = Me!BID
!AddedBy = StrUserName

!MyDSC = StrMyDSC
' What are StrUserName and StrMyDSC? If they are fields, they need brackets around them.

.Update
.Close
End With
Exit Do
End If
rs.MoveNext
Loop
End If ' may be unneeded.

VBA has requirements for referencing fields, variables, and controls. You have to follow them.
See this link for example of field references:
http://www.access-programmers.co.uk/...d.php?t=128854

[nick.h]

It adds the record now, which is a huge step forward! The problem is that if there is already a record with a matching BID in GKInfo then it displays the following error:
The list item could not be inserted or updated because duplicate values were found for one or more fileds in the list.

I have BID set to unique since it is a foreign key, and should never have more than one record. Why doesn't it end the routine when it finds a matching BID? I feel like with the code I had above it should Exit Sub gracefully!

Thanks for your help
Nick

[orange]

To check for a duplicate you can use DCOUNT()

This will get count from a particular table and can include a where clause.

If Dcount("RECId","MyTable", "RecId =" & "MyProposedRecId") > 0 Then
'if the count is not 0 then by pass adding this "duplicate" attempt
goto BypassTheUpdate
else
"Update code would go here"
endif

ByPassTheUpdate:

see http://www.techonthenet.com/access/f...ain/dcount.php