473,320 Members | 1,724 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

query to self table

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

Nov 13 '05 #1
5 1986
sa**********@gmail.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.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


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.


Nov 13 '05 #2
sa**********@gmail.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.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


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.


Nov 13 '05 #3
dear sir,

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

rgds
shahzad

Nov 13 '05 #4
sa**********@gmail.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.public.*

Nov 13 '05 #5
In message <I%******************@newsread1.news.pas.earthlink .net>,
Salad <oi*@vinegar.com> writes
sa**********@gmail.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.public.*


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.

Nov 13 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: sreddy | last post by:
I am trying to write a sql query on self referencing table. Just to brief ..Database is related to a Hiring department of the Qwest company. I need to generate a Report used by in HR...
4
by: Orion | last post by:
Hi, This is kind of last minute, I have a day and a half left to figure this out. I'm working on a project using ms-sqlserver. We are creating a ticket sales system, as part of the system, I...
4
by: Shahzad | last post by:
dear respected gurus, I would like to knew how to apply append,insert query for a self table where no primary keys issues. i do have problem say there are 5 rows of single record, this is data...
4
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET...
7
by: John Salerno | last post by:
Hopefully this is enough code to reveal the problem. When I run the program, there are no error messages produced, it's just that the values I enter don't seem to get put into the database, even...
17
by: erikcw | last post by:
Hi all, I'm trying to run the following query: amember_db = MySQLdb.connect(host="localhost", user="**********", passwd="*****", db="*******") # create a cursor self.amember_cursor =...
1
by: write2ashokkumar | last post by:
hi... i have the table like this, Table Name : sample Total Records : 500000 (Consider like this) Sample Records: id ------------ name
1
by: write2ashokkumar | last post by:
hi... i have the table like this, Table Name : sample Total Records : 500000 (Consider like this) Sample Records: ----------------
1
by: write2ashokkumar | last post by:
hi... i have the table like this, Table Name : sample Total Records : 500000 (Consider like this) Sample Records: ----------------
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.