Help with Update routine Please

[cvegas]

I am trying to update records in my table that are marked "Completed" prior
to running an Invoice Report
I have been working on this all day and could use any help you might want to
give.
Hopefully I have given you enough info to figure out what it is I am trying
to accomplish

'Select Records whose status is marked as "Completed" and order by the
OrderId number

SELECT '<-- This part is red in the VB window and appears not to be correct?
dbo_FDRShipments.OrderID , dbo_FDRShipments.OrderDate,
dbo_FDRShipments.Status_
dbo_FDRShipments.InvoiceNumber , dbo_FDRShipments.InvoiceDate_
dbo_FDRShipments.InvoiceAmount , dbo_FDRShipments.QuoteToCustomer_
FROM dbo_FDRShipments
WHERE (((dbo_FDRShipments.Status) = "Completed"))
ORDER BY dbo_FDRShipments.OrderID;'<-- This part is red in the VB window

'Determine the last Invoice Number Part that was used
'There is always only 1 record in this table which has 2 fields
[ID],[InvNumPart]
Dim lastnumber As Variant
lastnumber = DLookup("[InvNumPart]", "dbo_InvNumPart") + 1

'Go into the records that were selected and insert the proper information
into each record

'Need additional help here with syntax. Not sure how to insert data only
into the
'selected records.
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("dbo_FDRShipments") '??Dont know if this is
correct?

'Need to go to the 1st record in the selected records and insert the
following info
rst.Edit
rst![InvoiceNumber] = lastnumber
rst![InvoiceDate] = Date
rst![Status] = "Invoiced"
rst.Update
'Increase the lastnumber by 1 before going to the next record
lastnumber = lastnumber + 1
'Move to the next record that meets the select criteria and do the same as
above

'Once all records matching the select critera are updated
'Update the data in the table InvNumPart
Set rst = db.OpenRecordset("dbo_invnumpart")
rst.MoveFirst
rst.Edit
rst![InvNumPart] = lastnumber
rst.Update
rst.Close
Set rst = Nothing
Set db = Nothing

 

[Ken Snell]

Let's see if I can help....comments inline..

--
Ken Snell
<MS ACCESS MVP>

I am trying to update records in my table that are marked "Completed" prior
to running an Invoice Report
I have been working on this all day and could use any help you might want to
give.
Hopefully I have given you enough info to figure out what it is I am trying
to accomplish

'Select Records whose status is marked as "Completed" and order by the
OrderId number

SELECT '<-- This part is red in the VB window and appears not to be correct?
dbo_FDRShipments.OrderID , dbo_FDRShipments.OrderDate,
dbo_FDRShipments.Status_
dbo_FDRShipments.InvoiceNumber , dbo_FDRShipments.InvoiceDate_
dbo_FDRShipments.InvoiceAmount , dbo_FDRShipments.QuoteToCustomer_
FROM dbo_FDRShipments
WHERE (((dbo_FDRShipments.Status) = "Completed"))
ORDER BY dbo_FDRShipments.OrderID;'<-- This part is red in the VB window

The above SQL statement needs to be made into a text string and set into a
variable so that you can use it later when opening the recordset. Try this:

Dim strSQL As String
strSQL = "SELECT dbo_FDRShipments.OrderID , " & _
"dbo_FDRShipments.OrderDate, dbo_FDRShipments.Status, " & _
"dbo_FDRShipments.InvoiceNumber , " & _
"dbo_FDRShipments.InvoiceDate, " & _
"dbo_FDRShipments.InvoiceAmount, " & _
"dbo_FDRShipments.QuoteToCustomer FROM " & _
"dbo_FDRShipments WHERE dbo_FDRShipments.Status) " & _
"= 'Completed' ORDER BY dbo_FDRShipments.OrderID;"

'Determine the last Invoice Number Part that was used
'There is always only 1 record in this table which has 2 fields
[ID],[InvNumPart]
Dim lastnumber As Variant
lastnumber = DLookup("[InvNumPart]", "dbo_InvNumPart") + 1

'Go into the records that were selected and insert the proper information
into each record

'Need additional help here with syntax. Not sure how to insert data only
into the
'selected records.
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("dbo_FDRShipments") '??Dont know if this is
correct?

Now use the strSQL variable for this recordset's SQL:
Set rst = db.OpenRecordset(strSQL)

Then insert right after it the beginning of a Do loop so that you can move
through all the records:
Do While rst.EOF = False

'Need to go to the 1st record in the selected records and insert the
following info
rst.Edit
rst![InvoiceNumber] = lastnumber
rst![InvoiceDate] = Date
rst![Status] = "Invoiced"
rst.Update
'Increase the lastnumber by 1 before going to the next record
lastnumber = lastnumber + 1
'Move to the next record that meets the select criteria and do the same as
above

You need to insert more steps here:
rst.MoveNext
Loop
rst.Close

'Once all records matching the select critera are updated
'Update the data in the table InvNumPart
Set rst = db.OpenRecordset("dbo_invnumpart")
rst.MoveFirst
rst.Edit
rst![InvNumPart] = lastnumber
rst.Update
rst.Close
Set rst = Nothing
Set db = Nothing

 

[Gary Miller]

I do see a few things that could be creating problems for
you. I am torn whether to address those or address what I
see as a bigger issue first. I will go with your error
issues first as I think that will be valuable as well.

Where you say that you have red (yellow on mine) in the VB
window is where VB can't understand what you mean. I think
that it is bombing on your whole SQL statement which looks
99% correct.

I see a few things that I suspect are problems.

1) You seem to construct the statement, but then do nothing
with it.

2) You should assign the SQL statement to a string variable
so that you can do something with it. When you do this, you
will need to surround it with quotes as basically it is
nothing more than a text string. Ex:...

Dim strSQL as String

strSQL = "SELECT dbo_FDRShipments.OrderID ,
dbo_FDRShipments.OrderDate, " & _
"dbo_FDRShipments.Status, " & _ 'etc....

You used the _ at the end of some lines and not others (
could be copy and paste problem with the email), but you did
not use the quotes & ampersand or assign the whole deal to a
variable. "& _" is the proper line continuation method that
is most universal.

3) When revise it to the above, you will need to change your
full quotes to singles around 'Completed.

4) This line...

Set rst = db.OpenRecordset("dbo_FDRShipments")

should now be the following to use the SQL that you created
and only open it to the records you want...

Set rst = db.OpenRecordset(strSQL)

****
The Important Part!
****

I don't quite understand why you are waiting until multiple
Invoices have been completed before you assign them a
InvoiceNumber. Invoices usually have many 'InvoiceItems'
that would need to know this number before they are created
so that they no who their 'Parent' is, or what Invoice they
belong to. If you are using normal Form/Subform modeling to
enter the child records (InvoiceItems) how would you relate
the two without the InvoiceNumber already being known? The
standard method automatically takes care of trying to update
the Child table that you are trying to do manually.

It is also much better to use AutoNumbering to generate your
numbers rather than trying to handle it manually. If you
need to start them at 10,000 or 10143 instead of 1, there
are methods to do this as well.

Gary Miller
Sisters, OR

I am trying to update records in my table that are marked "Completed" prior
to running an Invoice Report
I have been working on this all day and could use any help you might want to
give.
Hopefully I have given you enough info to figure out what it is I am trying
to accomplish

'Select Records whose status is marked as "Completed" and order by the
OrderId number

SELECT '<-- This part is red in the VB window and appears not to be correct?
dbo_FDRShipments.OrderID , dbo_FDRShipments.OrderDate,
dbo_FDRShipments.Status_
dbo_FDRShipments.InvoiceNumber , dbo_FDRShipments.InvoiceDate_
dbo_FDRShipments.InvoiceAmount , dbo_FDRShipments.QuoteToCustomer_
FROM dbo_FDRShipments
WHERE (((dbo_FDRShipments.Status) = "Completed"))
ORDER BY dbo_FDRShipments.OrderID;'<-- This part is red in the VB window

'Determine the last Invoice Number Part that was used
'There is always only 1 record in this table which has 2 fields
[ID],[InvNumPart]
Dim lastnumber As Variant
lastnumber = DLookup("[InvNumPart]", "dbo_InvNumPart") + 1

'Go into the records that were selected and insert the proper information
into each record

'Need additional help here with syntax. Not sure how to insert data only
into the
'selected records.
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("dbo_FDRShipments") '??Dont know if this is
correct?

'Need to go to the 1st record in the selected records and insert the
following info
rst.Edit
rst![InvoiceNumber] = lastnumber
rst![InvoiceDate] = Date
rst![Status] = "Invoiced"
rst.Update
'Increase the lastnumber by 1 before going to the next record
lastnumber = lastnumber + 1
'Move to the next record that meets the select criteria and do the same as
above

'Once all records matching the select critera are updated
'Update the data in the table InvNumPart
Set rst = db.OpenRecordset("dbo_invnumpart")
rst.MoveFirst
rst.Edit
rst![InvNumPart] = lastnumber
rst.Update
rst.Close
Set rst = Nothing
Set db = Nothing

 

[cvegas]

Getting much closer thanks to your help.

I am getting an error

Runtime error '3035
Extra) in query expression 'dbo_FDRShipments.Status)='Completed".

Any ideas?

Thanks for your help so far. It is really appreciated.

 

[Gary Miller]

If you look closely at the line creating the error...

WHERE (((dbo_FDRShipments.Status) = "Completed"))

you will notice that you have three "(" on one side and only
two on the right side. This is one of MS$'s errors that was
saying what it meant <gr>. Also remember, that if you are
doing what Ken and I suggested in assigning it to a variable
and wrapping the whole thing in quotes, you will need to
reduce the qoutes around 'Completed' to be single quotes as
I just did, otherwise it will break the master string.

Gary Miller

Getting much closer thanks to your help.

I am getting an error

Runtime error '3035
Extra) in query expression 'dbo_FDRShipments.Status)='Completed".

Any ideas?

Thanks for your help so far. It is really appreciated.

I am trying to update records in my table that are

marked "Completed"
prior

to running an Invoice Report
I have been working on this all day and could use any

help you might want
to

give.
Hopefully I have given you enough info to figure out

what it is I am
trying

to accomplish

'Select Records whose status is marked as "Completed" and order by the
OrderId number

SELECT '<-- This part is red in the VB window and

appears not to be
correct?

dbo_FDRShipments.OrderID , dbo_FDRShipments.OrderDate,
dbo_FDRShipments.Status_
dbo_FDRShipments.InvoiceNumber , dbo_FDRShipments.InvoiceDate_
dbo_FDRShipments.InvoiceAmount , dbo_FDRShipments.QuoteToCustomer_
FROM dbo_FDRShipments
WHERE (((dbo_FDRShipments.Status) = "Completed"))
ORDER BY dbo_FDRShipments.OrderID;'<-- This part is red in the VB window

'Determine the last Invoice Number Part that was used
'There is always only 1 record in this table which has 2 fields
[ID],[InvNumPart]
Dim lastnumber As Variant
lastnumber = DLookup("[InvNumPart]", "dbo_InvNumPart") + 1

'Go into the records that were selected and insert the proper information
into each record

'Need additional help here with syntax. Not sure how to insert data only
into the
'selected records.
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("dbo_FDRShipments") '??Dont know if this is
correct?

'Need to go to the 1st record in the selected records and insert the
following info
rst.Edit
rst![InvoiceNumber] = lastnumber
rst![InvoiceDate] = Date
rst![Status] = "Invoiced"
rst.Update
'Increase the lastnumber by 1 before going to the next record
lastnumber = lastnumber + 1
'Move to the next record that meets the select criteria and do the same as
above

'Once all records matching the select critera are updated
'Update the data in the table InvNumPart
Set rst = db.OpenRecordset("dbo_invnumpart")
rst.MoveFirst
rst.Edit
rst![InvNumPart] = lastnumber
rst.Update
rst.Close
Set rst = Nothing
Set db = Nothing

 

[cvegas]

Gary thanks for your input.

Thanks to you and Ken I am close to contstructing a correct sql statement.

With this customer I want to make sure that only one record (shipment) is
assigned to an invoice.

If I have multiple shipments, on one invoice, ( and the have a problem with
a particular shipment) they will hold payment on the complete invoice until
the issue is resolved.

Once I have assigned an invoice number to each shipment I will fax them a
copy for payment. Hopefully this routine will help with the cash flow
problems we experience from time to time with this one customer.

Again thanks for your input.

I do see a few things that could be creating problems for
you. I am torn whether to address those or address what I
see as a bigger issue first. I will go with your error
issues first as I think that will be valuable as well.

Where you say that you have red (yellow on mine) in the VB
window is where VB can't understand what you mean. I think
that it is bombing on your whole SQL statement which looks
99% correct.

I see a few things that I suspect are problems.

1) You seem to construct the statement, but then do nothing
with it.

2) You should assign the SQL statement to a string variable
so that you can do something with it. When you do this, you
will need to surround it with quotes as basically it is
nothing more than a text string. Ex:...

Dim strSQL as String

strSQL = "SELECT dbo_FDRShipments.OrderID ,
dbo_FDRShipments.OrderDate, " & _
"dbo_FDRShipments.Status, " & _ 'etc....

You used the _ at the end of some lines and not others (
could be copy and paste problem with the email), but you did
not use the quotes & ampersand or assign the whole deal to a
variable. "& _" is the proper line continuation method that
is most universal.

3) When revise it to the above, you will need to change your
full quotes to singles around 'Completed.

4) This line...

Set rst = db.OpenRecordset("dbo_FDRShipments")

should now be the following to use the SQL that you created
and only open it to the records you want...

Set rst = db.OpenRecordset(strSQL)

****
The Important Part!
****

I don't quite understand why you are waiting until multiple
Invoices have been completed before you assign them a
InvoiceNumber. Invoices usually have many 'InvoiceItems'
that would need to know this number before they are created
so that they no who their 'Parent' is, or what Invoice they
belong to. If you are using normal Form/Subform modeling to
enter the child records (InvoiceItems) how would you relate
the two without the InvoiceNumber already being known? The
standard method automatically takes care of trying to update
the Child table that you are trying to do manually.

It is also much better to use AutoNumbering to generate your
numbers rather than trying to handle it manually. If you
need to start them at 10,000 or 10143 instead of 1, there
are methods to do this as well.

Gary Miller
Sisters, OR

I am trying to update records in my table that are marked "Completed" prior
to running an Invoice Report
I have been working on this all day and could use any help you might want to
give.
Hopefully I have given you enough info to figure out what it is I am trying
to accomplish

'Select Records whose status is marked as "Completed" and order by the
OrderId number

SELECT '<-- This part is red in the VB window and appears not to be correct?
dbo_FDRShipments.OrderID , dbo_FDRShipments.OrderDate,
dbo_FDRShipments.Status_
dbo_FDRShipments.InvoiceNumber , dbo_FDRShipments.InvoiceDate_
dbo_FDRShipments.InvoiceAmount , dbo_FDRShipments.QuoteToCustomer_
FROM dbo_FDRShipments
WHERE (((dbo_FDRShipments.Status) = "Completed"))
ORDER BY dbo_FDRShipments.OrderID;'<-- This part is red in the VB window

'Determine the last Invoice Number Part that was used
'There is always only 1 record in this table which has 2 fields
[ID],[InvNumPart]
Dim lastnumber As Variant
lastnumber = DLookup("[InvNumPart]", "dbo_InvNumPart") + 1

'Go into the records that were selected and insert the proper information
into each record

'Need additional help here with syntax. Not sure how to insert data only
into the
'selected records.
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("dbo_FDRShipments") '??Dont know if this is
correct?

'Need to go to the 1st record in the selected records and insert the
following info
rst.Edit
rst![InvoiceNumber] = lastnumber
rst![InvoiceDate] = Date
rst![Status] = "Invoiced"
rst.Update
'Increase the lastnumber by 1 before going to the next record
lastnumber = lastnumber + 1
'Move to the next record that meets the select criteria and do the same as
above

'Once all records matching the select critera are updated
'Update the data in the table InvNumPart
Set rst = db.OpenRecordset("dbo_invnumpart")
rst.MoveFirst
rst.Edit
rst![InvNumPart] = lastnumber
rst.Update
rst.Close
Set rst = Nothing
Set db = Nothing

 

[cvegas]

Getting much closer thanks to your help.

I am getting an error

Runtime error '3035
Extra) in query expression 'dbo_FDRShipments.Status)='Completed".

Any ideas?

Thanks for your help so far. It is really appreciated.

Let's see if I can help....comments inline..

--
Ken Snell
<MS ACCESS MVP>

I am trying to update records in my table that are marked "Completed" prior
to running an Invoice Report
I have been working on this all day and could use any help you might

want

to
give.
Hopefully I have given you enough info to figure out what it is I am trying
to accomplish

'Select Records whose status is marked as "Completed" and order by the
OrderId number

SELECT '<-- This part is red in the VB window and appears not to be correct?
dbo_FDRShipments.OrderID , dbo_FDRShipments.OrderDate,
dbo_FDRShipments.Status_
dbo_FDRShipments.InvoiceNumber , dbo_FDRShipments.InvoiceDate_
dbo_FDRShipments.InvoiceAmount , dbo_FDRShipments.QuoteToCustomer_
FROM dbo_FDRShipments
WHERE (((dbo_FDRShipments.Status) = "Completed"))
ORDER BY dbo_FDRShipments.OrderID;'<-- This part is red in the VB window

The above SQL statement needs to be made into a text string and set into a
variable so that you can use it later when opening the recordset. Try this:

Dim strSQL As String
strSQL = "SELECT dbo_FDRShipments.OrderID , " & _
"dbo_FDRShipments.OrderDate, dbo_FDRShipments.Status, " & _
"dbo_FDRShipments.InvoiceNumber , " & _
"dbo_FDRShipments.InvoiceDate, " & _
"dbo_FDRShipments.InvoiceAmount, " & _
"dbo_FDRShipments.QuoteToCustomer FROM " & _
"dbo_FDRShipments WHERE dbo_FDRShipments.Status) " & _
"= 'Completed' ORDER BY dbo_FDRShipments.OrderID;"

'Determine the last Invoice Number Part that was used
'There is always only 1 record in this table which has 2 fields
[ID],[InvNumPart]
Dim lastnumber As Variant
lastnumber = DLookup("[InvNumPart]", "dbo_InvNumPart") + 1

'Go into the records that were selected and insert the proper information
into each record

'Need additional help here with syntax. Not sure how to insert data only
into the
'selected records.
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("dbo_FDRShipments") '??Dont know if this is
correct?

Now use the strSQL variable for this recordset's SQL:
Set rst = db.OpenRecordset(strSQL)

Then insert right after it the beginning of a Do loop so that you can move
through all the records:
Do While rst.EOF = False

'Need to go to the 1st record in the selected records and insert the
following info
rst.Edit
rst![InvoiceNumber] = lastnumber
rst![InvoiceDate] = Date
rst![Status] = "Invoiced"
rst.Update
'Increase the lastnumber by 1 before going to the next record
lastnumber = lastnumber + 1
'Move to the next record that meets the select criteria and do the same as
above

You need to insert more steps here:
rst.MoveNext
Loop
rst.Close

'Once all records matching the select critera are updated
'Update the data in the table InvNumPart
Set rst = db.OpenRecordset("dbo_invnumpart")
rst.MoveFirst
rst.Edit
rst![InvNumPart] = lastnumber
rst.Update
rst.Close
Set rst = Nothing
Set db = Nothing

 

[cvegas]

Gary

I just pasted the sql statement from a select query that I created with the
query wizard to start with.

It was that sql statement which had the ((( on one side and )) on the other.

I removed that syntax and pasted your sql string syntax into the onclick
event of a cmdbutton where the original statement was.

I don't know where the code would have the ((( )) unless its from the
original query that my form is based on?

I am displaying the selected records on my form so that I can see if they
update properly after I run the onclick event of a cmdbutton that I am
putting this code on.

If you look closely at the line creating the error...

WHERE (((dbo_FDRShipments.Status) = "Completed"))

you will notice that you have three "(" on one side and only
two on the right side. This is one of MS$'s errors that was
saying what it meant <gr>. Also remember, that if you are
doing what Ken and I suggested in assigning it to a variable
and wrapping the whole thing in quotes, you will need to
reduce the qoutes around 'Completed' to be single quotes as
I just did, otherwise it will break the master string.

Gary Miller

Getting much closer thanks to your help.

I am getting an error

Runtime error '3035
Extra) in query expression 'dbo_FDRShipments.Status)='Completed".

Any ideas?

Thanks for your help so far. It is really appreciated.

I am trying to update records in my table that are

marked "Completed"
prior

to running an Invoice Report
I have been working on this all day and could use any

help you might want
to

give.
Hopefully I have given you enough info to figure out

what it is I am
trying

to accomplish

'Select Records whose status is marked as "Completed" and order by the
OrderId number

SELECT '<-- This part is red in the VB window and

appears not to be
correct?

dbo_FDRShipments.OrderID , dbo_FDRShipments.OrderDate,
dbo_FDRShipments.Status_
dbo_FDRShipments.InvoiceNumber , dbo_FDRShipments.InvoiceDate_
dbo_FDRShipments.InvoiceAmount , dbo_FDRShipments.QuoteToCustomer_
FROM dbo_FDRShipments
WHERE (((dbo_FDRShipments.Status) = "Completed"))
ORDER BY dbo_FDRShipments.OrderID;'<-- This part is red in the VB window

'Determine the last Invoice Number Part that was used
'There is always only 1 record in this table which has 2 fields
[ID],[InvNumPart]
Dim lastnumber As Variant
lastnumber = DLookup("[InvNumPart]", "dbo_InvNumPart") + 1

'Go into the records that were selected and insert the proper information
into each record

'Need additional help here with syntax. Not sure how to insert data only
into the
'selected records.
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("dbo_FDRShipments") '??Dont know if this is
correct?

'Need to go to the 1st record in the selected records and insert the
following info
rst.Edit
rst![InvoiceNumber] = lastnumber
rst![InvoiceDate] = Date
rst![Status] = "Invoiced"
rst.Update
'Increase the lastnumber by 1 before going to the next record
lastnumber = lastnumber + 1
'Move to the next record that meets the select criteria and do the same as
above

'Once all records matching the select critera are updated
'Update the data in the table InvNumPart
Set rst = db.OpenRecordset("dbo_invnumpart")
rst.MoveFirst
rst.Edit
rst![InvNumPart] = lastnumber
rst.Update
rst.Close
Set rst = Nothing
Set db = Nothing

 

[cvegas]

This is the code I pasted in after removing the original syntax I sent in
the first messge that had the ((( ))

Dim strSQL As String
strSQL = "SELECT dbo_FDRShipments.OrderID , " & _
"dbo_FDRShipments.OrderDate, dbo_FDRShipments.Status, " & _
"dbo_FDRShipments.InvoiceNumber , " & _
"dbo_FDRShipments.InvoiceDate, " & _
"dbo_FDRShipments.InvoiceAmount, " & _
"dbo_FDRShipments.QuoteToCustomer FROM " & _
"dbo_FDRShipments WHERE dbo_FDRShipments.Status) " & _
"= 'Completed' ORDER BY dbo_FDRShipments.OrderID;"

This is the line the program is giving the reported error on

Set rst = db.OpenRecordset(strSQL)

If you look closely at the line creating the error...

WHERE (((dbo_FDRShipments.Status) = "Completed"))

you will notice that you have three "(" on one side and only
two on the right side. This is one of MS$'s errors that was
saying what it meant <gr>. Also remember, that if you are
doing what Ken and I suggested in assigning it to a variable
and wrapping the whole thing in quotes, you will need to
reduce the qoutes around 'Completed' to be single quotes as
I just did, otherwise it will break the master string.

Gary Miller

Getting much closer thanks to your help.

I am getting an error

Runtime error '3035
Extra) in query expression 'dbo_FDRShipments.Status)='Completed".

Any ideas?

Thanks for your help so far. It is really appreciated.

I am trying to update records in my table that are

marked "Completed"
prior

to running an Invoice Report
I have been working on this all day and could use any

help you might want
to

give.
Hopefully I have given you enough info to figure out

what it is I am
trying

to accomplish

'Select Records whose status is marked as "Completed" and order by the
OrderId number

SELECT '<-- This part is red in the VB window and

appears not to be
correct?

dbo_FDRShipments.OrderID , dbo_FDRShipments.OrderDate,
dbo_FDRShipments.Status_
dbo_FDRShipments.InvoiceNumber , dbo_FDRShipments.InvoiceDate_
dbo_FDRShipments.InvoiceAmount , dbo_FDRShipments.QuoteToCustomer_
FROM dbo_FDRShipments
WHERE (((dbo_FDRShipments.Status) = "Completed"))
ORDER BY dbo_FDRShipments.OrderID;'<-- This part is red in the VB window

'Determine the last Invoice Number Part that was used
'There is always only 1 record in this table which has 2 fields
[ID],[InvNumPart]
Dim lastnumber As Variant
lastnumber = DLookup("[InvNumPart]", "dbo_InvNumPart") + 1

'Go into the records that were selected and insert the proper information
into each record

'Need additional help here with syntax. Not sure how to insert data only
into the
'selected records.
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("dbo_FDRShipments") '??Dont know if this is
correct?

'Need to go to the 1st record in the selected records and insert the
following info
rst.Edit
rst![InvoiceNumber] = lastnumber
rst![InvoiceDate] = Date
rst![Status] = "Invoiced"
rst.Update
'Increase the lastnumber by 1 before going to the next record
lastnumber = lastnumber + 1
'Move to the next record that meets the select criteria and do the same as
above

'Once all records matching the select critera are updated
'Update the data in the table InvNumPart
Set rst = db.OpenRecordset("dbo_invnumpart")
rst.MoveFirst
rst.Edit
rst![InvNumPart] = lastnumber
rst.Update
rst.Close
Set rst = Nothing
Set db = Nothing

 

[Larry Linson]

It does appear to have a closing parenthesis unpaired with an opening
parenthesis in the quote below. If that is part of a larger expression, go
over it thoroughly and carefully.

Getting much closer thanks to your help.

I am getting an error

Runtime error '3035
Extra) in query expression 'dbo_FDRShipments.Status)='Completed".

Any ideas?

Thanks for your help so far. It is really appreciated.

Let's see if I can help....comments inline..

--
Ken Snell
<MS ACCESS MVP>

I am trying to update records in my table that are marked "Completed" prior
to running an Invoice Report
I have been working on this all day and could use any help you might

want

to
give.
Hopefully I have given you enough info to figure out what it is I am trying
to accomplish

'Select Records whose status is marked as "Completed" and order by the
OrderId number

SELECT '<-- This part is red in the VB window and appears not to be correct?
dbo_FDRShipments.OrderID , dbo_FDRShipments.OrderDate,
dbo_FDRShipments.Status_
dbo_FDRShipments.InvoiceNumber , dbo_FDRShipments.InvoiceDate_
dbo_FDRShipments.InvoiceAmount , dbo_FDRShipments.QuoteToCustomer_
FROM dbo_FDRShipments
WHERE (((dbo_FDRShipments.Status) = "Completed"))
ORDER BY dbo_FDRShipments.OrderID;'<-- This part is red in the VB

window

The above SQL statement needs to be made into a text string and set into a
variable so that you can use it later when opening the recordset. Try this:

Dim strSQL As String
strSQL = "SELECT dbo_FDRShipments.OrderID , " & _
"dbo_FDRShipments.OrderDate, dbo_FDRShipments.Status, " & _
"dbo_FDRShipments.InvoiceNumber , " & _
"dbo_FDRShipments.InvoiceDate, " & _
"dbo_FDRShipments.InvoiceAmount, " & _
"dbo_FDRShipments.QuoteToCustomer FROM " & _
"dbo_FDRShipments WHERE dbo_FDRShipments.Status) " & _
"= 'Completed' ORDER BY dbo_FDRShipments.OrderID;"

'Determine the last Invoice Number Part that was used
'There is always only 1 record in this table which has 2 fields
[ID],[InvNumPart]
Dim lastnumber As Variant
lastnumber = DLookup("[InvNumPart]", "dbo_InvNumPart") + 1

'Go into the records that were selected and insert the proper information
into each record

'Need additional help here with syntax. Not sure how to insert data only
into the
'selected records.
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("dbo_FDRShipments") '??Dont know if this is
correct?

Now use the strSQL variable for this recordset's SQL:
Set rst = db.OpenRecordset(strSQL)

Then insert right after it the beginning of a Do loop so that you can move
through all the records:
Do While rst.EOF = False

'Need to go to the 1st record in the selected records and insert the
following info
rst.Edit
rst![InvoiceNumber] = lastnumber
rst![InvoiceDate] = Date
rst![Status] = "Invoiced"
rst.Update
'Increase the lastnumber by 1 before going to the next record
lastnumber = lastnumber + 1
'Move to the next record that meets the select criteria and do the

same

as

above

You need to insert more steps here:
rst.MoveNext
Loop
rst.Close

'Once all records matching the select critera are updated
'Update the data in the table InvNumPart
Set rst = db.OpenRecordset("dbo_invnumpart")
rst.MoveFirst
rst.Edit
rst![InvNumPart] = lastnumber
rst.Update
rst.Close
Set rst = Nothing
Set db = Nothing

 

[John Vinson]

Dim strSQL As String
strSQL = "SELECT dbo_FDRShipments.OrderID , " & _
"dbo_FDRShipments.OrderDate, dbo_FDRShipments.Status, " & _
"dbo_FDRShipments.InvoiceNumber , " & _
"dbo_FDRShipments.InvoiceDate, " & _
"dbo_FDRShipments.InvoiceAmount, " & _
"dbo_FDRShipments.QuoteToCustomer FROM " & _
"dbo_FDRShipments WHERE dbo_FDRShipments.Status) " & _
"= 'Completed' ORDER BY dbo_FDRShipments.OrderID;"

You've got an extra ) in the next to last line.

 

[Larry Linson]

This is the code I pasted in after
removing the original syntax I sent in
the first messge that had the ((( ))

Dim strSQL As String
strSQL = "SELECT dbo_FDRShipments.OrderID , " & _
"dbo_FDRShipments.OrderDate, dbo_FDRShipments.Status, " & _
"dbo_FDRShipments.InvoiceNumber , " & _
"dbo_FDRShipments.InvoiceDate, " & _
"dbo_FDRShipments.InvoiceAmount, " & _
"dbo_FDRShipments.QuoteToCustomer FROM " & _
"dbo_FDRShipments WHERE dbo_FDRShipments.Status) " & _

The line immediately above has a ) for which there is no corresponding (
anywhere in the statement.

"= 'Completed' ORDER BY dbo_FDRShipments.OrderID;"

This is the line the program is giving the reported error on

Set rst = db.OpenRecordset(strSQL)

If you look closely at the line creating the error...

WHERE (((dbo_FDRShipments.Status) = "Completed"))

you will notice that you have three "(" on one side and only
two on the right side. This is one of MS$'s errors that was
saying what it meant <gr>. Also remember, that if you are
doing what Ken and I suggested in assigning it to a variable
and wrapping the whole thing in quotes, you will need to
reduce the qoutes around 'Completed' to be single quotes as
I just did, otherwise it will break the master string.

Gary Miller

Getting much closer thanks to your help.

I am getting an error

Runtime error '3035
Extra) in query expression 'dbo_FDRShipments.Status)='Completed".

Any ideas?

Thanks for your help so far. It is really appreciated.




I am trying to update records in my table that are marked "Completed"
prior
to running an Invoice Report
I have been working on this all day and could use any help you might want
to
give.
Hopefully I have given you enough info to figure out what it is I am
trying
to accomplish

'Select Records whose status is marked as "Completed" and order by the
OrderId number

SELECT '<-- This part is red in the VB window and appears not to be
correct?
dbo_FDRShipments.OrderID , dbo_FDRShipments.OrderDate,
dbo_FDRShipments.Status_
dbo_FDRShipments.InvoiceNumber , dbo_FDRShipments.InvoiceDate_
dbo_FDRShipments.InvoiceAmount , dbo_FDRShipments.QuoteToCustomer_
FROM dbo_FDRShipments
WHERE (((dbo_FDRShipments.Status) = "Completed"))
ORDER BY dbo_FDRShipments.OrderID;'<-- This part is red in the VB window

'Determine the last Invoice Number Part that was used
'There is always only 1 record in this table which has 2 fields
[ID],[InvNumPart]
Dim lastnumber As Variant
lastnumber = DLookup("[InvNumPart]", "dbo_InvNumPart") + 1

'Go into the records that were selected and insert the proper information
into each record

'Need additional help here with syntax. Not sure how to insert data only
into the
'selected records.
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("dbo_FDRShipments") '??Dont know if this is
correct?

'Need to go to the 1st record in the selected records and insert the
following info
rst.Edit
rst![InvoiceNumber] = lastnumber
rst![InvoiceDate] = Date
rst![Status] = "Invoiced"
rst.Update
'Increase the lastnumber by 1 before going to the next record
lastnumber = lastnumber + 1
'Move to the next record that meets the select criteria and do the same as
above

'Once all records matching the select critera are updated
'Update the data in the table InvNumPart
Set rst = db.OpenRecordset("dbo_invnumpart")
rst.MoveFirst
rst.Edit
rst![InvNumPart] = lastnumber
rst.Update
rst.Close
Set rst = Nothing
Set db = Nothing

 

[Gary Miller]

John and Larry nailed that problem. I looked back at your
original code and it looks like you missed that one when you
stripped the others.

Should work now.

Gary Miller

This is the code I pasted in after removing the original syntax I sent in
the first messge that had the ((( ))

Dim strSQL As String
strSQL = "SELECT dbo_FDRShipments.OrderID , " & _
"dbo_FDRShipments.OrderDate, dbo_FDRShipments.Status, " & _
"dbo_FDRShipments.InvoiceNumber , " & _
"dbo_FDRShipments.InvoiceDate, " & _
"dbo_FDRShipments.InvoiceAmount, " & _
"dbo_FDRShipments.QuoteToCustomer FROM " & _
"dbo_FDRShipments WHERE dbo_FDRShipments.Status) " & _
"= 'Completed' ORDER BY dbo_FDRShipments.OrderID;"

This is the line the program is giving the reported error on

Set rst = db.OpenRecordset(strSQL)

If you look closely at the line creating the error...

WHERE (((dbo_FDRShipments.Status) = "Completed"))

you will notice that you have three "(" on one side and only
two on the right side. This is one of MS$'s errors that was
saying what it meant <gr>. Also remember, that if you are
doing what Ken and I suggested in assigning it to a variable
and wrapping the whole thing in quotes, you will need to
reduce the qoutes around 'Completed' to be single quotes as
I just did, otherwise it will break the master string.

Gary Miller

Getting much closer thanks to your help.

I am getting an error

Runtime error '3035
Extra) in query expression 'dbo_FDRShipments.Status)='Completed".

Any ideas?

Thanks for your help so far. It is really appreciated.




I am trying to update records in my table that are marked "Completed"
prior
to running an Invoice Report
I have been working on this all day and could use

any
help you might want

to
give.
Hopefully I have given you enough info to figure out what it is I am
trying
to accomplish

'Select Records whose status is marked as

"Completed"
and order by the

OrderId number

SELECT '<-- This part is red in the VB window and appears not to be
correct?
dbo_FDRShipments.OrderID , dbo_FDRShipments.OrderDate,
dbo_FDRShipments.Status_
dbo_FDRShipments.InvoiceNumber , dbo_FDRShipments.InvoiceDate_
dbo_FDRShipments.InvoiceAmount , dbo_FDRShipments.QuoteToCustomer_
FROM dbo_FDRShipments
WHERE (((dbo_FDRShipments.Status) = "Completed"))
ORDER BY dbo_FDRShipments.OrderID;'<-- This part is

red
in the VB window

'Determine the last Invoice Number Part that was used
'There is always only 1 record in this table which

has 2
fields

[ID],[InvNumPart]
Dim lastnumber As Variant
lastnumber = DLookup("[InvNumPart]",

"dbo_InvNumPart") +
1

'Go into the records that were selected and insert

the
proper information

into each record

'Need additional help here with syntax. Not sure

how to
insert data only

into the
'selected records.
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("dbo_FDRShipments")

'??Dont
know if this is

correct?

'Need to go to the 1st record in the selected

records
and insert the

following info
rst.Edit
rst![InvoiceNumber] = lastnumber
rst![InvoiceDate] = Date
rst![Status] = "Invoiced"
rst.Update
'Increase the lastnumber by 1 before going to the

next
record

lastnumber = lastnumber + 1
'Move to the next record that meets the select

criteria
and do the same as

above

'Once all records matching the select critera are updated
'Update the data in the table InvNumPart
Set rst = db.OpenRecordset("dbo_invnumpart")
rst.MoveFirst
rst.Edit
rst![InvNumPart] = lastnumber
rst.Update
rst.Close
Set rst = Nothing
Set db = Nothing

 

[Ken Snell]

Sorry - I left in an inadvertent ). Here is the correct SQL:

strSQL = "SELECT dbo_FDRShipments.OrderID , " & _
"dbo_FDRShipments.OrderDate, dbo_FDRShipments.Status, " & _
"dbo_FDRShipments.InvoiceNumber , " & _
"dbo_FDRShipments.InvoiceDate, " & _
"dbo_FDRShipments.InvoiceAmount, " & _
"dbo_FDRShipments.QuoteToCustomer FROM " & _
"dbo_FDRShipments WHERE dbo_FDRShipments.Status " & _
"= 'Completed' ORDER BY dbo_FDRShipments.OrderID;"


--
Ken Snell
<MS ACCESS MVP>

Getting much closer thanks to your help.

I am getting an error

Runtime error '3035
Extra) in query expression 'dbo_FDRShipments.Status)='Completed".

Any ideas?

Thanks for your help so far. It is really appreciated.

Let's see if I can help....comments inline..

--
Ken Snell
<MS ACCESS MVP>

I am trying to update records in my table that are marked "Completed" prior
to running an Invoice Report
I have been working on this all day and could use any help you might

want

to
give.
Hopefully I have given you enough info to figure out what it is I am trying
to accomplish

'Select Records whose status is marked as "Completed" and order by the
OrderId number

SELECT '<-- This part is red in the VB window and appears not to be correct?
dbo_FDRShipments.OrderID , dbo_FDRShipments.OrderDate,
dbo_FDRShipments.Status_
dbo_FDRShipments.InvoiceNumber , dbo_FDRShipments.InvoiceDate_
dbo_FDRShipments.InvoiceAmount , dbo_FDRShipments.QuoteToCustomer_
FROM dbo_FDRShipments
WHERE (((dbo_FDRShipments.Status) = "Completed"))
ORDER BY dbo_FDRShipments.OrderID;'<-- This part is red in the VB

window

The above SQL statement needs to be made into a text string and set into a
variable so that you can use it later when opening the recordset. Try this:

Dim strSQL As String
strSQL = "SELECT dbo_FDRShipments.OrderID , " & _
"dbo_FDRShipments.OrderDate, dbo_FDRShipments.Status, " & _
"dbo_FDRShipments.InvoiceNumber , " & _
"dbo_FDRShipments.InvoiceDate, " & _
"dbo_FDRShipments.InvoiceAmount, " & _
"dbo_FDRShipments.QuoteToCustomer FROM " & _
"dbo_FDRShipments WHERE dbo_FDRShipments.Status) " & _
"= 'Completed' ORDER BY dbo_FDRShipments.OrderID;"

'Determine the last Invoice Number Part that was used
'There is always only 1 record in this table which has 2 fields
[ID],[InvNumPart]
Dim lastnumber As Variant
lastnumber = DLookup("[InvNumPart]", "dbo_InvNumPart") + 1

'Go into the records that were selected and insert the proper information
into each record

'Need additional help here with syntax. Not sure how to insert data only
into the
'selected records.
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("dbo_FDRShipments") '??Dont know if this is
correct?

Now use the strSQL variable for this recordset's SQL:
Set rst = db.OpenRecordset(strSQL)

Then insert right after it the beginning of a Do loop so that you can move
through all the records:
Do While rst.EOF = False

'Need to go to the 1st record in the selected records and insert the
following info
rst.Edit
rst![InvoiceNumber] = lastnumber
rst![InvoiceDate] = Date
rst![Status] = "Invoiced"
rst.Update
'Increase the lastnumber by 1 before going to the next record
lastnumber = lastnumber + 1
'Move to the next record that meets the select criteria and do the

same

as

above

You need to insert more steps here:
rst.MoveNext
Loop
rst.Close

'Once all records matching the select critera are updated
'Update the data in the table InvNumPart
Set rst = db.OpenRecordset("dbo_invnumpart")
rst.MoveFirst
rst.Edit
rst![InvNumPart] = lastnumber
rst.Update
rst.Close
Set rst = Nothing
Set db = Nothing