By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,501 Members | 2,850 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,501 IT Pros & Developers. It's quick & easy.

Updating one record at a time

P: 8
I am trying to add the first field from Table1 to Table2. Using this record in Table2, I am running a set of queries to get my end result. Once the queries are completed for the first record, I want the queries to run for my next record until EOF. Ofcourse, I want this automated using a command button on a form.
Any ideas ? Any help is appreciated.
Nov 22 '06 #1
Share this Question
Share on Google+
20 Replies


Expert 5K+
P: 8,435
I am trying to add the first field from Table1 to Table2. Using this record in Table2, I am running a set of queries to get my end result. Once the queries are completed for the first record, I want the queries to run for my next record until EOF. Ofcourse, I want this automated using a command button on a form.
Any ideas ? Any help is appreciated.
Can I just clarify something? Did you actually mean the first field, or first record?
Nov 22 '06 #2

P: 8
its actually the 2nd feild in the first record.

Thanks.
Nov 22 '06 #3

Expert 5K+
P: 8,435
its actually the 2nd feild in the first record.
Um... sorry, maybe it's just me, but I'm having trouble understanding the process you're trying to perform.

As best I can work out, when you click a button on your form, you want Access to run a bunch of queries (details not provided) for each record in Table1. And basically, one of those queries will be an action query which will copy (or move?) the record to Table2.

Does this sound about right? If so, is each of these queries supposed to provide output to the user, or perform some update, or what?
Nov 22 '06 #4

P: 8
Sorry...Let me go into the details.

I want to set up a command button on my form that

1. Writes the first record of Field #2 (Primary Field) from Table #1 to Field #1 in Table#2
2. Using only this one record in Table #2, I want it to run a set of queries, and finally a report is generated (steps already completed)
3. Then I want to delete the records (should have only one) in table#2 by replacing it with the second record from Table #1
4. Rerun the queries and generate reports for each one seperately until EOF in Table #1
Nov 22 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry...Let me go into the details.

I want to set up a command button on my form that

1. Writes the first record of Field #2 (Primary Field) from Table #1 to Field #1 in Table#2
2. Using only this one record in Table #2, I want it to run a set of queries, and finally a report is generated (steps already completed)
3. Then I want to delete the records (should have only one) in table#2 by replacing it with the second record from Table #1
4. Rerun the queries and generate reports for each one seperately until EOF in Table #1
What is the relationship between table1 and table2.

What do you mean by 'Using only this one record in Table #2, I want it to run a set of queries'. This doesn't make any sense. How can you run a query on one record. What queries are you running. If you posted a sample of the query SQL we might be better able to understand what you are trying to do.

My best guess from what you've said is you actually want to run through the records in table1 and based on the value in the primary key you want to run a report based on some query for each record in the table.

If this is the case you would do it by using recordsets and passing the value in this field as criteria to the report in a loop until all records had been gone through. There is no need for a second table.

If this is what you want we can provide the code for your command button.

Mary
Nov 22 '06 #6

P: 8
>>>My best guess from what you've said is you actually want to run through the records in table1 and based on the value in the primary key you want to run a report based on some query for each record in the table.

That is correct.

>>>>If this is the case you would do it by using recordsets and passing the value in this field as criteria to the report in a loop until all records had been gone through. There is no need for a second table.

Can you please post the code. Thanks for your help in advance.
Nov 22 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
>>>My best guess from what you've said is you actually want to run through the records in table1 and based on the value in the primary key you want to run a report based on some query for each record in the table.

That is correct.

>>>>If this is the case you would do it by using recordsets and passing the value in this field as criteria to the report in a loop until all records had been gone through. There is no need for a second table.

Can you please post the code. Thanks for your help in advance.
OK in the click event behind your command button (cmdPrint for this example). BTW, make sure the DAO library is selected in the references list (Tools --> References).

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdPrint_Click()
  3. Dim db As Database
  4. Dim rs As Recordset
  5. Dim strCriteria As String
  6.  
  7.   Set db = CurrentDb
  8.   Set rs = db.OpenRecordset(TableName) ' The aforementioned Table1
  9.  
  10.   rs.MoveFirst
  11.   Do Until rs.EOF
  12.     strCriteria = "[FieldID]=" & rs![FieldID] ' put the name of the relevant field here, assumes a number datatype
  13.     DoCmd.OpenReport "ReportName", , , strCriteria 'Opens the report
  14.     DoCmd.PrintOut ' Prints the Report
  15.     DoCmd.Close acReport, "ReportName" ' Closes the report
  16.     rs.MoveNext
  17.   Loop
  18.  
  19.   rs.Close
  20.   Set rs = Nothing
  21.   Set db = Nothing
  22.  
  23. End Sub
  24.  
Nov 22 '06 #8

Expert 5K+
P: 8,435
...BTW, make sure the DAO library is selected in the references list (Tools --> References).
That's something I don't understand. Why should a program which is running in Access need to reference an external library to use the database? Weird...
Nov 22 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
That's something I don't understand. Why should a program which is running in Access need to reference an external library to use the database? Weird...
It's Access, stuuuupid ;)
Nov 22 '06 #10

P: 8
It's Access, stuuuupid ;)
Thanks for the help. When I run this with the filter option, where Filter = name of query, I am getting a popo-up message asking me to enter the parameter value.

DoCmd.OpenReport "R001-TEST REPORT", , "Q045-TEST REPORT", strCriteria

Here's my code

[html]
Private Sub Command2_Click()
Dim db As Database
Dim rs As Recordset
Dim strCriteria As String

Set db = CurrentDb
Set rs = db.OpenRecordset("T007-CPT SUMMARY") ' The aforementioned Table1

rs.MoveFirst
Do Until rs.EOF
strCriteria = "[CPT CATALOG NUMBER]=" & rs![CPT CATALOG NUMBER] ' put the name of the relevant field here, assumes a number datatype"


'DoCmd.OpenReport "R002-ORIGINAL COMPONENTS TABLE", , strCriteria, strCriteria
'DoCmd.OpenReport "R003-LOW USAGE FOR REPORT", , , strCriteria
'DoCmd.Close acReport, "R003-LOW USAGE FOR REPORT"
'DoCmd.OpenReport "R004-LESS THAN 10 COMPONENTS FOR REPORT", , , strCriteria
'DoCmd.Close acReport, "R004-LESS THAN 10 COMPONENTS FOR REPORT"
'DoCmd.OpenReport "R005-PREFERRED 2 FOR REPORT", , , strCriteria
'DoCmd.Close acReport, "R005-PREFERRED 2 FOR REPORT"
DoCmd.OpenReport "R001-TEST REPORT", , "Q045-TEST REPORT", strCriteria 'Opens the report
DoCmd.OutputTo acOutputReport, "R001-TEST REPORT", , , NO
' Prints the Report
'DoCmd.Close acReport, "R001-TEST REPORT" ' Closes the report
' DoCmd.OutputTo acOutputReport, "R002-ORIGINAL COMPONENTS TABLE", , , NO
'DoCmd.Close acReport, "R002-ORIGINAL COMPONENTS TABLE"
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

[/html]
Nov 22 '06 #11

NeoPa
Expert Mod 15k+
P: 31,186
It might save some time and effort if you posted the message asking for 'the parameter value'.
Nov 22 '06 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
It might save some time and effort if you posted the message asking for 'the parameter value'.
Is [CPT CATALOG NUMBER] a field in the table?

Has [CPT CATALOG NUMBER] got a text or number data type?
Nov 22 '06 #13

P: 8
Is [CPT CATALOG NUMBER] a field in the table?

Yes

Has [CPT CATALOG NUMBER] got a text or number data type?
Text

Thanks.
Nov 22 '06 #14

NeoPa
Expert Mod 15k+
P: 31,186
It might save some time and effort if you posted the message asking for 'the parameter value'.
If you don't respond to requests for information in your own thread, you can expect fewer experts prepared to respond!
Nov 22 '06 #15

P: 8
If you don't respond to requests for information in your own thread, you can expect fewer experts prepared to respond!
when I run the code, It just comes up with a dialog box with and says parameter value (ok and cancel are the options).

It also send the file to my printer.
Nov 22 '06 #16

NeoPa
Expert Mod 15k+
P: 31,186
when I run the code, It just comes up with a dialog box with and says parameter value (ok and cancel are the options).

It also send the file to my printer.
And you can't tell us what's in the dialog box message (exactly)?
What I need to know before trawling through your code is exactly what the parameter is that it's complaining about.
I can't search for 'Some Unspecified Parameter Value' :S.
Nov 22 '06 #17

P: 8
And you can't tell us what's in the dialog box message (exactly)?
What I need to know before trawling through your code is exactly what the parameter is that it's complaining about.
I can't search for 'Some Unspecified Parameter Value' :S.
There is no "error" message as such.
It comes up with a interactive message box, with the catalalog number on the top and requires the user to enter a value in the parameter field.

Thanks.
Nov 22 '06 #18

Expert 5K+
P: 8,435
It comes up with a interactive message box, with the catalalog number on the top and requires the user to enter a value in the parameter field.
So the parameter it's asking for is catalog number?

It's just that these "enter parameter" type things often mean that a field name or something has been mis-spelled or used incorrectly, and it's definitely a case where the niggly little details are paramount.
Nov 22 '06 #19

NeoPa
Expert Mod 15k+
P: 31,186
There is no "error" message as such.
It comes up with a interactive message box, with the catalalog number on the top and requires the user to enter a value in the parameter field.

Thanks.
Never mind :(.
Nov 22 '06 #20

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry for the delay, I had to go out ...

Replace

strCriteria = "[CPT CATALOG NUMBER]=" & rs![CPT CATALOG NUMBER]

with

strCriteria = "[CPT CATALOG NUMBER]='" & rs![CPT CATALOG NUMBER] & "'"

and it should work fine. For passing text values you always have to surround with single quotes.

Mary
Nov 22 '06 #21

Post your reply

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