query to self table

[salimshahzad]

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.DoMenuIte m acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuIte m acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuIte m 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(policy no) 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_In sert(strNewPoli cy As String,
strOldPolicy As String)

strSQL = "INSERT INTO [tbldetails] (
POLICYNO,PlateN o,InsuredAmt,Pr emium" & _
" SELECT " & _
" '" & strNewPolicy & "',PlateNo,Insu redAmt,Premium" & _
" FROM tbldetails " & _
" WHERE ((([tblpolicy.POLIC YNO)='" & strOldPolicy & "')); "

DoCmd.RunSQL strSQL
End Function
=============== =============== =============== =============== =============== ====
rgds
shahzad

[Salad]

sa**********@gm ail.com wrote:

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.DoMenuIte m acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuIte m acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuIte m 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(policy no) 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_In sert(strNewPoli cy As String,
strOldPolicy As String)

strSQL = "INSERT INTO [tbldetails] (
POLICYNO,PlateN o,InsuredAmt,Pr emium" & _
" SELECT " & _
" '" & strNewPolicy & "',PlateNo,Insu redAmt,Premium" & _
" FROM tbldetails " & _
" WHERE ((([tblpolicy.POLIC YNO)='" & strOldPolicy & "')); "

DoCmd.RunSQL strSQL
End Function
=============== =============== =============== =============== =============== ====
rgds
shahzad

Some things to consider.

You can substitue lines like DoCmd.DoMenuIte m 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.GoToRecor d. 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.Recordsetclo ne

'add record
rst.AddNew
rst.FirstName = "Joe"
rst.LastName = "Blow"
rst.Update
rst.Bookmark = rst.LastModifie d

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.

[Salad]

sa**********@gm ail.com wrote:

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.DoMenuIte m acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuIte m acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuIte m 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(policy no) 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_In sert(strNewPoli cy As String,
strOldPolicy As String)

strSQL = "INSERT INTO [tbldetails] (
POLICYNO,PlateN o,InsuredAmt,Pr emium" & _
" SELECT " & _
" '" & strNewPolicy & "',PlateNo,Insu redAmt,Premium" & _
" FROM tbldetails " & _
" WHERE ((([tblpolicy.POLIC YNO)='" & strOldPolicy & "')); "

DoCmd.RunSQL strSQL
End Function
=============== =============== =============== =============== =============== ====
rgds
shahzad

Some things to consider.

You can substitue lines like DoCmd.DoMenuIte m 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.GoToRecor d. 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.Recordsetclo ne

'add record
rst.AddNew
rst.FirstName = "Joe"
rst.LastName = "Blow"
rst.Update
rst.Bookmark = rst.LastModifie d

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.

[salimshahzad]

dear sir,

the issue with details table neither on form..i wanted to do via vba
codes can any one assist this

rgds
shahzad

[Salad]

sa**********@gm ail.com wrote:

dear sir,

the issue with details table neither on form..i wanted to do via vba
codes can any one assist this

rgds
shahzad

Your English is very difficult to understand. Very few people, if you
are lucky there may be one, will understand your statement "the issue
with details table neither on form".

Are there newsgroups regarding Access in your native language?
Microsoft has its own newsgroups. Check for them under microsoft.publi c.*

[Bernard Peek]

In message <I%************ ******@newsread 1.news.pas.eart hlink.net>,
Salad <oi*@vinegar.co m> writes

sa**********@g mail.com wrote:

dear sir,
the issue with details table neither on form..i wanted to do via vba
codes can any one assist this
rgds
shahzad

Your English is very difficult to understand. Very few people, if you
are lucky there may be one, will understand your statement "the issue
with details table neither on form".

Are there newsgroups regarding Access in your native language?
Microsoft has its own newsgroups. Check for them under
microsoft.publ ic.*

The comp.databases. ms-access newsgroup charter doesn't forbid foreign
language posts. They may not get many replies but there is no harm in
trying. With any luck someone else will be able to handle the
translations.
--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.