Ignore Access index warning if inserting records from parent to child table

[kingcarol]

In Access, I am able to set warnings to false when running an Insert query that bumps into a duplicate index warning. When I do that, the insert query will go ahead and insert records that do not violate the index and just ignore the ones that do. When attempting this same thing from Alpha, the query will not execute because my database sends back the error message with the index warning and I don't know how to tell it to do it anyway.

When running an insert query in Access that has index key violations, the warning will read 'it didn't add 46 records due to key violations...Do you want to run the action query anyway?' . If you click Yes, the query will run and all records that did NOT violate the index WILL be inserted (which is what I want). As I said, in my Access app code, I set that warning to false so that the user does not see the warning. By setting it to false, it is the same as clicking Yes on the warning message, so the non violating records do get inserted.

In Alpha, here is the error message sent back from Access: http://screencast.com/t/kagJIldpJfMQ and I don't know how to tell it to run the insert query anyway.

Reason for this: If there are new records added to a parent table, I often want to make sure that a child table has a record for every new record in the parent before opening the child table. For example, I have a parent table that is a set of Cost Codes. Then I have a child table that is a Job Budget table made up of records that have a Job Code and a Cost Code with an index that prevents a duplicate record with same Job Code and Cost Code. Before opening a job budget, I want to make sure any new Cost Codes have been added to that budget, so in Access I run an insert query that tries to append all Cost Codes to the child budget table with the appropriate Job Code. I expect that it will produce the duplicate index error, but am able to get around it in Access, and insert only records that do not violate the unique index (ie, the new cost codes). I do not want to insert records to the budget table for all jobs at the time a new cost code is set up because some jobs may not have budgets yet, and I don't want to create budget records for those jobs yet (since creating a new job budget is done based on additional calculated criteria gathered from the Jobs table).

Well, this is probably too complex or confusing, but just thought I'd throw this out there in case there are any Access to Alpha gurus that have dealt with this before. Thanks for any advice.

[kingcarol]

Re: Ignore Access index warning if inserting records from parent to child table

P.S. The error message in Alpha is a result of the following block of Xbasic code. I tried commenting this block out and executing the query without the flag to see if it would go ahead and insert the new cost codes, but it didn't. Hmmmmm, I wonder if there is any way to write an insert query that has something like WHERE THERE ARE NOT DUPLICATE INDEX VALUES (psuedo code).

'The connection is opened earlier in this function...
'TblJobData is the child job budgets table.  TblJobSubCodesAndMaster is the parent Cost Codes table.

dim jobCode as c 
jobCode = e._currentRowDataNew.JOB_CODE

dim sql as c
dim args as sql::arguments
args.add("JOB_CODE",jobCode)

sql = "INSERT INTO TblJobData ( SUB_CODE, JOB_CODE, DESCRIPTION, AREA_CODE, UNIT_MEAS, QTY, QTY_PERCENT, UNIT_PRICE, MARKUP, PERCENT_COMPLETE, TEMPLATE, REVISED_EST, BUYER_SALES_TAX, SHOW, PERCENT_CMPLT, CUSTOMER_NOTES ) SELECT SUB_CODE, :JOB_CODE AS JOB_CODE, DESCRIPTION, 0 AS AREA_CODE, UNIT_MEAS, 0 AS QTY, QTY_PERCENT, UNIT_PRICE, MARKUP_DEFAULT AS MARKUP, 0 AS PERCENT_COMPLETE, 'Yes' AS TEMPLATE, 0 AS REVISED_EST, 0 AS BUYER_SALES_TAX, 0 AS SHOW, 0 AS PERCENT_CMPLT, BUDGET_CUSTOMER_NOTES AS CUSTOMER_NOTES FROM TblJobSubCodesAndMaster WHERE SUB_CODE >0"

dim flag as l
flag = cn.Execute(sql,args)

'if append query could not execute, give the user a message and exit the function.
if flag = .f. then 
	'there was an error - close the connection and exit
	dim msg as c 
	msg = "Could not execute insert query. Please contact your program administrator for help.  Error reported was: " + cn.CallResult.text
	msg = js_escape(msg)
	dim jscmd as c 
	jscmd = "alert('" + msg + "');"
	cn.Close()
	AppendCostCodesAsNoShowToThisBudget = jscmd
	exit function 
end if

[kingcarol]

Re: Ignore Access index warning if inserting records from parent to child table

I bet I'll have to write a While loop to go through the Cost Codes table, then check to see if each Cost Code (SUB_CODE) is in the child table where the JOB_CODE = jobCode and THEN if not, execute an insert query for that cost code.... LOTS more coding than I had to do in Access. Sigh...

[kingcarol]

Re: Ignore Access index warning if inserting records from parent to child table

So... I'm just entertaining myself here by answering myself, but I have solved my issue. In case anyone else stumbles across this thread, I'll go ahead and post my solution here. I did end up doing a While loop to accomplish this as follows. (I'm sure I have extra curricular code that a more seasoned Xbasic developer would not use, but I still have to go step by step and put comments to explain things to myself.)

function AppendCostCodesAsNoShowToThisBudget as c (e as p)
'PURPOSE:  Make sure that all new cost codes from the Cost Codes Master table have been added as No Show budget lines
'so that they will be available in grid that will pop up.  The grid that pops up will show all budget lines where
'SHOW = 0 (or no), so we want be sure all lines are available.  This function will do a While loop that loops through
'all cost codes in the TblJobSubCodesAndMaster (Cost Codes Master), and checks each one to see if there is a child
'record for the current job and each cost code in TblJobData.  Budget records will be inserted into the TblJobData for any cost
'codes not found in the job budget.

dim cn as sql::connection
dim cs as c 
cs = your connection string

dim flag as l 
flag = cn.open(cs)
if flag = .f. then 
	dim msg as c 
	msg = "Could not connect to database. Error reported was: " + cn.CallResult.text
	msg = js_escape(msg)
	dim jscmd as c 
	jscmd = "alert('" + msg + "');"
	AppendCostCodesAsNoShowToThisBudget = jscmd
	exit function 
end if
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
dim jobCode as c 
jobCode = e._currentRowDataNew.JOB_CODE

dim args1 as sql::arguments
dim rs as sql::ResultSet
dim rs1 as sql::resultset
dim recordCount as n
dim args2 as sql::arguments
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Set initial value of cost code variable that will be used to establish result from select query.  
'Value will be changing as a result of the While loop the occurs below.
dim vCostCode as N
vCostCode = 0
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'query to get the result set.  Result set to update from must be ONLY records that have SUB_CODE greater than zero.
	
sqlCount = "Select * from TblJobSubCodesAndMaster where SUB_CODE >0"
		
cn.execute(sqlCount)
rs = cn.ResultSet
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Move to the first row in the result set.  
'This actually moves to the first row in the ResultSet, NOT the second row as you would expect
flag = rs.nextRow()

'We are now positioned on the first row of the result set and can do our While loop to loop through the records
'and and set the vCostCode variable value for each Cost Code.
while flag 
    'when there are no more records in the ResultSet, executing .nextRow()
    'will return .f., so flag will be .f. and the while loop will end
        
	args1.add("whatSubCode",rs.data("SUB_CODE"))
	args1.add("whatJob",jobCode)
	
    sqlCommand = "Select count (*) from TblJobData where JOB_CODE = :whatJob and SUB_CODE = :whatSubCode"
	cn.execute(sqlCommand,args1)
		
	rs1 = cn.ResultSet
	
	'assign the resulting count number, rs1.data(1), to a numeric variable named recordCount
	recordCount = rs1.data(1)
	
	if recordCount >0 then 'there is already a child record, so go to next parent record
		flag = rs.nextRow() 'exit the loop and go to next row
	end if
	
	'if we get to here, no record found in child table so insert a record to the child table
	
	vCostCode = rs.data("SUB_CODE")
		
	args2.add("SUB_CODE",vCostCode)
	args2.add("JOB_CODE",jobCode)
	
	sqlInsert = "INSERT INTO TblJobData ( SUB_CODE, JOB_CODE, DESCRIPTION, AREA_CODE, UNIT_MEAS, QTY, QTY_PERCENT, UNIT_PRICE, MARKUP, PERCENT_COMPLETE, TEMPLATE, REVISED_EST, BUYER_SALES_TAX, SHOW, PERCENT_CMPLT, CUSTOMER_NOTES ) SELECT :SUB_CODE AS SUB_CODE, :JOB_CODE AS JOB_CODE, DESCRIPTION, 0 AS AREA_CODE, UNIT_MEAS, 0 AS QTY, 1 AS QTY_PERCENT, 0 AS UNIT_PRICE, MARKUP, 0 AS PERCENT_COMPLETE, 'Yes' AS TEMPLATE, 0 AS REVISED_EST, BUYER_SALES_TAX, 0 AS SHOW, 0 AS PERCENT_CMPLT, BUDGET_CUSTOMER_NOTES AS CUSTOMER_NOTES FROM TblJobSubCodesAndMaster WHERE SUB_CODE = :SUB_CODE"
	cn.execute(sqlInsert,args2)
			
    flag = rs.nextRow()
end while

cn.close()

AppendCostCodesAsNoShowToThisBudget = "alert('The function is done.');"   'or whatever function needs to do next

end function