AddNew Overwriting Existing Record

[DMBLuva]

Hi all,
I'm sure this is something simple, but I just can't pinpoint it. I've searched the boards, googled to death and I don't see anything that stands out causing my rst.addnew to overwrite a random record while putting in the new record.
Let me explain. I have a multiselect listbox that adds records into a table for those selected. It's adding the records, but not before replacing the LogID and blanking out a grantID for a random existing record in the table.

Here is my code:

Dim db As dao.Database
Dim rst As dao.Recordset
Dim var As Variant
Dim intLogID As Integer
Dim strInactive As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblgrantlog_join", dbOpenDynaset)

If Me.Inactive.Value = False Then
strInactive = False
Else
strInactive = True
End If


intLogID = Me.txtLogID.Value

'check that listbox is not null
If Me.lstGrants.ItemsSelected.Count = 0 Then
msgbox "Please select at least one grant to add to the log", vbOKOnly
Exit Sub
End If

For Each var In Me.lstGrants.ItemsSelected
rst.AddNew
rst!LogID = intLogID
rst!GrantID = Me.lstGrants.Column(0, var)
rst!Inactive = Me.Inactive.Value
rst!PAAD = Me.chkPAAD.Value
rst!AAAD = Me.chkAAAD.Value
rst!datEofchange = Date
rst.update
Next var
'close recordset
rst.Close

'requery main log form
Forms!frmaccess.lstGrants.Requery
'requery listbox
Me.lstGrants.Requery

Set rst = Nothing
Set db = Nothing

Me.chkAAAD.Value = False
Me.chkPAAD.Value = False
Me.Inactive.Value = False


Any assistance would be appreciated!

Sarah

 

[dhookom]

I would set a breakpoint to step through the code so I could see what was happening. Also I would Dim strInactive as boolean, not string since this code creates a datatype mismatch:

If Me.Inactive.Value = False Then
    strInactive = False
Else
    strInactive = True
End If

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Why even have strInactive? It is not used anywhere.

If you AddNew record, how come you expect to overwrite existing record? To do that, use UPDATE statement and Update existing record, or Delete existing record before adding a new one.


Have fun.

---- Andy

 

[DMBLuva]

Thanks for the replies guys.
Andy-I didn't notice I wasn't using strinactive, but I don't think it's the problem. Also, I'm not trying to overwrite any record. I'm trying to add to the table, but it is overwriting a record then adding the record in a new row.

Duane-As always you are so willing to help! I think you have been the first to reply to a couple of my posts now, so thank you.
Anyway, funny thing is I did do a break and all of the data is correctly being read, but I don't know how to see what or why the row is being updated. Anything you can suggest to see that? I changed the boolean as well, I forget about that data type heh.

Thanks again.

 

[Andrzejek]

Is it possibile that your LogID is a Primary Key in your table? If so, you may not introduce another LogID with the same value. That may explain overwriting records...

BTW, if your String strInactive is now a Boolean blnInactive, you may simply do:

blnInactive = Me.Inactive.Value

Have fun.

---- Andy

 

[DMBLuva]

Hi Andy,

Ok so, the table that I'm putting this data in is relational to the LOG table that has LOGID as it's Primary Key. So the table I'm inserting this data into, LOGID is actually the foreign key for this table. Does that make sense? The LogID in THIS table could already be in there several times over, but oddly enough, it's not targeting the row with the same LogID. It's more random, at least from what I can see. But I can double check that. Thanks for the tip on the Boolean, but I'm wondering if it's necessary at all or if the actual control will be good enough to reference. I haven't tested that yet.

My tables look like this:

tblLog
(PK) LogID

tblGrantLog_Join
(PK) GrantLogID
(FK) LogID

Thanks so much for your continued help!

 

[Andrzejek]

So let me understand what you are trying to do here:
[tt]
If the record (LogID) already is in your table
Add New Record into the table
Else [green]'LogID is not there[/green]
Insert a new record
End If
[/tt]
Is that what you want?

Have fun.

---- Andy

 

[DMBLuva]

Sorry I don't mean to be confusing.
So the table it's going into is a join table. A Log can have many grants and a grant can have many logs so this table I'm dumping this data into is a many to many table.
So really it doesn't matter if the Log ID is in there because I'm just trying join another grant to an existing log.

I should have mentioned that - I don't know why I didn't include that in my last post. I messed that up in my last post, I'm sorry.

My table set up should be:
Log Table
(PK) LogID

Grant Table
(PK)GrantID

GrantLog_Join
(PK)GrantLogID
(FK)LogID
(FK)GrantID

I've been looking at it so long I forget the important details apparently. I appreciate you sticking with me lol. I'm starting to hope my data isn't corrupt. I've been having lots of problems with Access crashing, sigh, but really that's nothing new.

 

[Andrzejek]

I see. It is not what I thought, but that's fine.

I would put a break in the loop and see line by line what's going on. Especially what is going on in the table Before and After rst.AddNew and rst.update


Have fun.

---- Andy

 

[MazeWorX]

so adding an auto number as the primary key and writing the foreign key isn't an option.

Easy solution create a primary key(auto number) that will be distinct by setting the properties to no Duplicates the write the foreign key as many times as you like

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[dhookom]

I don't see any issue with the code or logic that would create the issue. I would test the results to confirm the perceived errors. You could create SQL insert statements rather than using recordsets.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

To All . . .

I can't help but agree with [blue]dhookom[/blue] ... the op is adding the [blue]foreignkeys[/blue] of a child table of a many to many relationship. The op can [blue]add[/blue] as necessary as long as the Id's [blue]rst!LogID[/blue] & [blue]rst!GrantID[/blue] already exist! ... and since these ID's come from the listbox for previous values, I see no problem with the ops code.

[blue]DMBLuva[/blue] . . .
You need to track the source of the ID's in the many to many link table ... namely ... [blue]rst!LogID[/blue] & [blue] rst!GrantID[/blue].

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

Agreed there is nothing wrong with the code it should ONLY add new so that prompts several questions.

Something is editing existing records. Supposedly when this event fires.

1- Is this ALL of the code?
2- Is there any other code that modifies records attached to the form?

I would think that if the answer is yes to 1 and no to 2 then this is a corruption of some sort. Either table, form, module, or access its self

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[JoeAtWork]

Are your tables actually linked to some database server, such as SQL Server or Oracle? If so, the updates may be caused by triggers that exist in the database.

I agree with Duane's suggestion of using SQL INSERT statement instead of recordsets. I gave up using recordsets for anything other than data retrieval/display years ago. This allows me to control directly how and which records are updated. Also, you are currently opening the entire tblGrantLog_Join table but since you only need to add records it is unnecessary. Using an INSERT statement would avoid this inefficiency.

But I agree with the others that you are looking in the wrong place. I would step through the code, and just before the Update check for any changed records, and then just after the Update check again. This would confirm your theory that your AddNew is actually causing the changed record.

I would also want to see the context of your code - what happens before and/or after it. You haven't indicated if your code is in a subroutine or an event. Does some subroutine get called immediately before or after this one, that maybe is the real cause of your problem?