Updating one record at a time

[sganesan]

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.

[Killer42]

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?

[sganesan]

its actually the 2nd feild in the first record.

Thanks.

[Killer42]

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?

[sganesan]

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

[MMcCarthy]

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

[sganesan]

>>>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.

[MMcCarthy]

>>>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).

 
Private Sub cmdPrint_Click()
Dim db As Database
Dim rs As Recordset
Dim strCriteria As String
 
  Set db = CurrentDb
  Set rs = db.OpenRecordset(TableName) ' The aforementioned Table1
 
  rs.MoveFirst
  Do Until rs.EOF
    strCriteria = "[FieldID]=" & rs![FieldID] ' put the name of the relevant field here, assumes a number datatype
    DoCmd.OpenReport "ReportName", , , strCriteria 'Opens the report
    DoCmd.PrintOut ' Prints the Report
    DoCmd.Close acReport, "ReportName" ' Closes the report
    rs.MoveNext
  Loop
 
  rs.Close
  Set rs = Nothing
  Set db = Nothing
 
End Sub
 

[Killer42]

...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...

[MMcCarthy]

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 ;)

[sganesan]

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]

[NeoPa]

It might save some time and effort if you posted the message asking for 'the parameter value'.

[MMcCarthy]

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?

[sganesan]

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

Yes

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

Text

Thanks.

[NeoPa]

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!

[sganesan]

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.

[NeoPa]

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.

[sganesan]

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.

[Killer42]

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.

[NeoPa]

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 :(.

[MMcCarthy]

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