VBA code to loop append query fails

[Furquhart]

Microsoft Server 2008
DuckRace database

Main table is Contestant with PK ContestantID
Child table is Purchases with PK PurchaseID and FK is ContestantID
I have an append query that runs from a command button and adds new purchases
to the Child table.

The following code picks up the number of ducks to be purchased from the main
form and pulls it into the IntDucks variable.

The Append Query then loops until the correct number of new records (ducks)
have been added to the child table.

If the parent record is a new record the Append query fails with the
following Error message

Microsoft Office Access can't append all the records in the append query....
MSOffice Access set 0 fields to Null due to a type conversion failur, and it
didn't add 1 record to the table due to key violations, 0 records due to loc
violations and 0 records due to validation rule violations. The primary key
for both tables is integar with no duplicates set so it is a true primary key.


I can't find anything wrong. But, if I add more ducks to an existing record
in the (Child) Purchase table it all works perfect.

Private Sub Cmd_PurchaseNewDucks_Click()
Dim IntDucks As Integer
Dim stDocName As String

IntDucks = Forms!Frm_Contestants!Ducks
stDocName = "Qry_PurchaseDucks"

counter = 0
myNum = 0
DoCmd.SetWarnings False
Do
myNum = myNum + 1
counter = counter + 1
DoCmd.OpenQuery stDocName
Loop Until myNum = IntDucks
DoCmd.SetWarnings True
DoCmd.Requery
RunCommand acCmdRecordsGoToLast
End Sub

Any suggestions?

Fred
furquhart AT hospiceanswers dot Not-For_Profit

 

[John Spencer]

I suspect that the problem is that the NEW record has not been saved.
Since it has not been saved it does not exist and if you have relational
integrity set up, there is no record in the Contestant table that the
new record in the Purchases table can relate to.

I also suspect there is a much better way to do this, but if this works
for you there is no reason to muck it up trying to make it more efficient.

Private Sub Cmd_PurchaseNewDucks_Click()
Dim IntDucks As Integer
Dim stDocName As String

'This will force the new record to be saved.
If Me.Dirty = True Then Me.Dirty = False

IntDucks = Forms!Frm_Contestants!Ducks
stDocName = "Qry_PurchaseDucks"

counter = 0
myNum = 0
DoCmd.SetWarnings False
Do
myNum = myNum + 1
counter = counter + 1
DoCmd.OpenQuery stDocName
Loop Until myNum = IntDucks
DoCmd.SetWarnings True
DoCmd.Requery
RunCommand acCmdRecordsGoToLast
End Sub

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Furquhart]

John:

That did the trick.

Thank you very much. If you're willing to share a better, more efficient way,
I'm willing to listen.

With appreciation,

Fred

I suspect that the problem is that the NEW record has not been saved.
Since it has not been saved it does not exist and if you have relational
integrity set up, there is no record in the Contestant table that the
new record in the Purchases table can relate to.

I also suspect there is a much better way to do this, but if this works
for you there is no reason to muck it up trying to make it more efficient.

Private Sub Cmd_PurchaseNewDucks_Click()
Dim IntDucks As Integer
Dim stDocName As String

'This will force the new record to be saved.
If Me.Dirty = True Then Me.Dirty = False

IntDucks = Forms!Frm_Contestants!Ducks
stDocName = "Qry_PurchaseDucks"

counter = 0
myNum = 0
DoCmd.SetWarnings False
Do
myNum = myNum + 1
counter = counter + 1
DoCmd.OpenQuery stDocName
Loop Until myNum = IntDucks
DoCmd.SetWarnings True
DoCmd.Requery
RunCommand acCmdRecordsGoToLast
End Sub

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Microsoft Server 2008
DuckRace database

[quoted text clipped - 46 lines]

Fred
furquhart AT hospiceanswers dot Not-For_Profit

 

[John Spencer]

Post the SQL of qry_PurchaseDucks if you need detailed help. One idea
is outlined below.

I would add a small table to your table structure with one field fldInt
that contains records with the numbers 1 to the highest number of
records you intend to add at one time.

Then the Append query might look something like

INSERT INTO Purchases (ContestantID, DuckNo)
SELECT ContestantID, NumberTable.fldInt
FROM Contestants, NumberTable
WHERE NumberTable.fldInt <=Forms!Frm_Contestants!Ducks
AND Contestants.ContestantID = Forms!Frm_Contestants!ContestantID

You would Execute this query once instead of looping through the query
and running it many times.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John:

That did the trick.

Thank you very much. If you're willing to share a better, more efficient way,
I'm willing to listen.

With appreciation,

Fred

I suspect that the problem is that the NEW record has not been saved.
Since it has not been saved it does not exist and if you have relational
integrity set up, there is no record in the Contestant table that the
new record in the Purchases table can relate to.

I also suspect there is a much better way to do this, but if this works
for you there is no reason to muck it up trying to make it more efficient.

Private Sub Cmd_PurchaseNewDucks_Click()
Dim IntDucks As Integer
Dim stDocName As String

'This will force the new record to be saved.
If Me.Dirty = True Then Me.Dirty = False

IntDucks = Forms!Frm_Contestants!Ducks
stDocName = "Qry_PurchaseDucks"

counter = 0
myNum = 0
DoCmd.SetWarnings False
Do
myNum = myNum + 1
counter = counter + 1
DoCmd.OpenQuery stDocName
Loop Until myNum = IntDucks
DoCmd.SetWarnings True
DoCmd.Requery
RunCommand acCmdRecordsGoToLast
End Sub

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Microsoft Server 2008
DuckRace database

[quoted text clipped - 46 lines]

Fred
furquhart AT hospiceanswers dot Not-For_Profit