insert into sql statement not working.. help!

[rsbutterfly16]

hi guys i have a form that comes from a query with a parent and child table. Main table pk is pricingid and then the foreign key in tblHistory is pricingid.

I am trying to do an append table but i keep getting an error message
Pricing ID is the pk autonumber in tblPricing (parent table)
historyid is the pk in tblHistory and pricingid the fk(child table)
oldprice should be a currency value
the other fields are text.

this is my code:

strSQL = "Insert into tblHistory (DateChanged, Edit_UserName, OldPrice) Values(#" & now() & "#, '" & getusername() &"', " & Me.txtPrice.OldValue & ")


but i get the error message


You cannot or change a record because a related record is required in table tblPricing


any advise... i know i need to select somehow the pricingId from the parenttable, but how do i do that?

 

[Keith Nichols]

It's probably a typo, but your code is missing the final quotation I think (").

Without thinking about this too much, I suggest you open tblhistory and attempt to add the a record exactly as it would be generated from your insert SQL. My guess is that you would get the same error suggesting that the code is not the problem as such.

If you do get the error that way, then work out which other field in the table is required to be filled before a record can be saved. messing about directly in the table is the easient way to do this sometimes. From there, you can decide to either populate this field or change it so that it is not required.

Hopethis helps and doesn't divert you from a solution as I really am in the foothills of Access.

 

[MarkK]

You're getting this error becuase you have created a relationship between the tables in which referential integrity is enforced. In this case, the database engine will not allow you to add a record to the child table without specifying a valid foreign key ID that exists in the parent table.

strSQL = _
  "INSERT INTO tblHistory " & _
    "( DateChanged, Edit_UserName, OldPrice, [COLOR="DarkRed"]PricingID[/COLOR] ) " & _
  "VALUES ( " & _
    "#" & now() & "#, " & _
    "'" & getusername() & "', " & _
    Me.txtPrice.OldValue & ", " & _
    [COLOR="DarkRed"]pID[/COLOR] & " );"

 

[KeithG]

It sounds like you have a one to many relationship from tblPricing to table history, is this correct? And it sound like this row viloates the referential integrity on the relationship.

 

[rsbutterfly16]

yes you are right there is a one to many relationship from tblPricing and tblhistory.. from PricingID . I need to somehow select the pricingid in the form and insert those fields for that pricing id fk in tblhistory.


labbolt thank you so much for your code, it works wonderfully... then only thing instead of adding one row is adding 5 rows in tblhistory with the same information... any ideas why this is happening?

 

[MarkK]

Cheers Butterfly:
I'd look for unexpected loops or cascading events, 'cause my guess is your INSERT code is running more than once.
Set a breakpoint in the line of code that executes the SQL. Run the routine. Execution pauses at that line. In 'Menu->View->Call Stack' you can now view a list of routines that have called the current one. Close 'Call Stack', hit F5 and see if execution pauses again on your breakpoint. If so, view 'Call Stack'. This offers you lots of info about what routines are calling what routines, and you can find loops you might not expect were happening.

 

[rsbutterfly16]

thanks i did not know that... i got it!!!! i had the code in the price change event.. so it was adding every time i would add a number... now i changed it to the save event and it works perfectly... yayy!!! thank you so much for helping me.

 

[Keith Nichols]

Cheers Butterfly:
I'd look for unexpected loops or cascading events, 'cause my guess is your INSERT code is running more than once.
Set a breakpoint in the line of code that executes the SQL. Run the routine. Execution pauses at that line. In 'Menu->View->Call Stack' you can now view a list of routines that have called the current one. Close 'Call Stack', hit F5 and see if execution pauses again on your breakpoint. If so, view 'Call Stack'. This offers you lots of info about what routines are calling what routines, and you can find loops you might not expect were happening.

Lagbolt,

That sounds like a cracking tip. I think this could have saved me many hours and reduced the number of circles I ran round in the development of my db. I will be saving this for later.

Cheers.

 

[MarkK]

Keith:
Ya, the call stack window is essential. Double click an item in there and you can view the code in the state it was when it made a call. Handy if you use recursion or class modules where the same code might contain different data depending how deep in the call stack it is. Use in conjuction with the Locals Window and you can view the value of each member of a class module and navigate around your objects sort of frozen in time.
Cheers,
Mark