By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,742 Members | 793 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,742 IT Pros & Developers. It's quick & easy.

query to self table

P: n/a
dear respected sir,

i have so much strange behavior of access queries using through VBA

here is the structure of tables
- tblMaster(where PK is PolicNo)
- tblDetails

so there is 1-TO-many relation between above tables. when the policy
issues, user has to enter details of vehciles under details
every end of year we have to renew policies of clients, so master
policies i renew using deuplicate record method

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

so what happend i take input user to renew(re-issue) with current
details of new policy and keep stores as new record. now the question
raised how to renew details. as details can't renew all in one shot
with above codes...

so i write a query that inster record via variable(policyno) and select
from previous policy(as variable) and insert into it.

now the above relation is cascade-update/intergrity enforce...if i keep
this relation it never works, and if i removed it start working can any
one put highlight and share ideas of self query/co-query how to do i try 100 of methods never success in this

i do have integrity issues, however b4 this i never faced this, can any
one tell with aliases explample if the same table is select and same
table v hve to insert/append what is the best practise method

================================================== ============================
Public Function Motor_Policy_Insert(strNewPolicy As String,
strOldPolicy As String)

strSQL = "INSERT INTO [tbldetails] (
POLICYNO,PlateNo,InsuredAmt,Premium" & _
" SELECT " & _
" '" & strNewPolicy & "',PlateNo,InsuredAmt,Premium" & _
" FROM tbldetails " & _
" WHERE ((([tblpolicy.POLICYNO)='" & strOldPolicy & "')); "

DoCmd.RunSQL strSQL
End Function
================================================== =============================

Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.