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 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. 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.
dear sir,
the issue with details table neither on form..i wanted to do via vba
codes can any one assist this
rgds
shahzad 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.*
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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 =...
|
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
|
by: write2ashokkumar |
last post by:
hi...
i have the table like this,
Table Name : sample
Total Records : 500000 (Consider like this)
Sample Records:
----------------
|
by: write2ashokkumar |
last post by:
hi...
i have the table like this,
Table Name : sample
Total Records : 500000 (Consider like this)
Sample Records:
----------------
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |