salimshahzad@gmail.com wrote:
[color=blue]
> dear respected sir,
>
> i have so much strange behavior of access queries using through VBA
> codes
>
> 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 table.now
> 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
> this...as 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
> ================================================== =============================
> rgds
> shahzad
>[/color]
Some things to consider.
You can substitue lines like DoCmd.DoMenuItem acFormBar.... with
Docmd.Runcmd acCmd...
Using RunCmd is much more expressive. There are many constants, but if
you want to copy, paste, etc they are easy enough to find in the list.
You may also want to look at Docmd.GoToRecord. Look at GoToRecord in help.
Another thing you may want to look at is VBA. Here is some example code
to add a record to a table and then move to that record in a form.
In this example, I'll add a name to a table called Test and move to that
record.
Sub AddRec
Dim rst As DAO.Recordset
set rst = Me.Recordsetclone
'add record
rst.AddNew
rst.FirstName = "Joe"
rst.LastName = "Blow"
rst.Update
rst.Bookmark = rst.LastModified
Me.Bookmark = rst.Bookmark
End Sub
I know I didn't understand what you wanted to accomplish but some of
these things may help you out.
