473,327 Members | 2,016 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,327 software developers and data experts.

add records to a table from another table in form operation afterupdate event

Hi all,

i have a db where i maintain document controlling. In the main form i am updating all the relevant fields(by qry- two tables i to many). for certain reason after updating a particual combobox i want to add 5 fields from this qry to a n another table.

for Eg: 5 fiels of total 45 fiels from two tabels namely docno text 25, ircno text 25, rev text 10, client text 255, comments text 255 to another tblsummary afterupdating field ircno from form.

i have tried many ways but each giving some errors. when tried insert and select mode error message - case requied

Help required very badly

regards
rammudali
Aug 19 '10 #1
5 3082
Delerna
1,134 Expert 1GB
sorry rammundali! I am sure you know what you are saying but I can't make head nor tail of it.

Perhaps if you post what you have tried and what the error is
Aug 20 '10 #2
thanks for quick response

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblreport ( REV, IRCNO, DUEDT, [ACTION] )
  2.     SELECT transmital.REV, irc.IRCNO, irc.DUEDT, irc.ACTION
  3.     FROM irc INNER JOIN transmital ON irc.TRANSNO = transmital.transmittalID
  4.     GROUP BY transmital.REV, irc.IRCNO
Aug 20 '10 #3
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo20_AfterUpdate()
  2.  
  3. INSERT INTO tblreport ( REV, IRCNO, DUEDT, [ACTION] )
  4. SELECT transmital.REV, irc.IRCNO, irc.DUEDT, irc.ACTION
  5. FROM irc INNER JOIN transmital ON irc.TRANSNO = transmital.transmittalID
  6. ORDER BY irc.IRCNO;
  7.  
  8. End Sub
it is giving compile error : syntax error
Aug 20 '10 #4
NeoPa
32,556 Expert Mod 16PB
You're trying to put SQL code directly within a VBA procedure. Only VBA code can go there.
Aug 20 '10 #5
NeoPa
32,556 Expert Mod 16PB
You would need something like the following instead :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo20_AfterUpdate()
  2.     Dim strSQL As String
  3.  
  4.     strSQL = "INSERT INTO tblreport ( REV, IRCNO, DUEDT, [ACTION] ) " & _
  5.              "SELECT transmital.REV, irc.IRCNO, irc.DUEDT, irc.ACTION " & _
  6.              "FROM irc INNER JOIN transmital " & _
  7.              "ON irc.TRANSNO = transmital.transmittalID" & _
  8.              "ORDER BY irc.IRCNO"
  9.     Call CurrentDB.Execute(strSQL)
  10.  
  11. End Sub
Aug 20 '10 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Tony Williams | last post by:
I have two tables one lists the names of committees and the other is a list of documents they generate. I have a form based on the documents table which gives details of the document including...
6
by: Sven Pran | last post by:
Probably the answer is there just in front of me only awaiting me to discover it, but: 1: I want to build a query that returns all records in one table for which there is no successful "join"...
2
by: vorley99 | last post by:
I am trying to write some VB to copy the field names from one table in a db to a field in another table in the same database i.e., so they appear as a set of records. Any clue how to achieve...
1
by: Bijoy Naick | last post by:
I need to be able issue a query, then insert each record in the reader into another table. But, when I issue the myCommand.ExecuteNonQuery call, I get an error telling me that a reader is...
6
by: Dale | last post by:
I'm not sure I'm even thinking about this the right way, but here goes: I have a table of users. Each one of these users may be associated with none, one, or many records in another table I call...
3
by: wvmbark | last post by:
First time poster... I just found this forum and it appears there's plenty of people here that could make short work of problem that's been driving me absolutely bonkers for months. Every day we...
2
MatthewML
by: MatthewML | last post by:
I am attempting to insert a custom AfterUpdate Event Procedure into a text box on a form that I am designing in MS Access 2000. This text box contains the e-mail address of the referenced contact,...
6
markrawlingson
by: markrawlingson | last post by:
Hopefully someone can help me out with this, it's driving me nuts... I have two tables - We'll call them table1 and table2. Table1 holds event information, and table2 holds user registration data...
5
by: trixb | last post by:
Hello all, Here is what I need to do and need help with: I have a table that is feeding a chart in a report in Access. If this table has more than 50 records, the graph gets messy, and this is...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
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...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
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: 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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.