Nearly there just one line of code needed... (1 Viewer)

[SDB]

Hi All

I have the following code and wish to change the Weights.Collected field to True from false in the new query (expr3: ) in the new query.

Thanks



Private Sub cmdBuildQuery_Click()
On Error GoTo Err_BuildQry
Dim strSQL As String
Dim qdf As DAO.querydef
strSQL = "SELECT TOP "
strSQL = strSQL & Me.txtNumberToGet
strSQL = strSQL & " weights.Weights, Weights.DocketNo, Weights.Collected, Weights.UKBulk, Weights.weighttime FROM CollectionQry "
strSQL = strSQL & "ORDER BY ([id]);"

DoCmd.DeleteObject acQuery, "qryCollectYorks"

CurrentDb.CreateQueryDef "qryCollectYorks", strSQL

DoCmd.OpenQuery "appendUkBulk"
DoCmd.OpenQuery "qryCollectYorks"
Exit_BuildQry:
Exit Sub
Err_BuildQry:
If Err.Number = 7874 Then
Resume Next
Else
MsgBox Err.Number & " - " & Err.Description
Resume Exit_BuildQry
End If
End Sub

 

[namliam]

NO need to do
DoCmd.DeleteObject acQuery, "qryCollectYorks"

CurrentDb.CreateQueryDef "qryCollectYorks", strSQL

you can just do
currentdb.querydef("name").sql = strsql

Other than that I do not understand your question

 

[SDB]

the database is used to keep a track off all the royal mail sacks that are collected throughout the day by the post office. each time they collect x amount of yorks( nine sacks to a york) they have to sign to say they have collected x amount at x amount weight. at the end of the day the lot is totaled up and thats what you are charged for.
the problem is they don't take them as they are weighed, eg they normally take eight at a time but it may be the first 8 or 1-6 and no 11 and no 16 it doesn't matter along as they sign for the correct amount of yorks they take and the weights at the end of the day totals up.

I have the weights recorded in a seperate table, when they collect "8 yorks" the user enters in the amount taken pushes a button to move those "8" but this is the issue I need to set the eight taken as collected so as not to use them again.

sorry about the long post hope it makes sense...

 

[namliam]

So you have 3 entries with 8 sacks and want to total them to nines?? Or something like that??

Your post wasnt that long... I have made and seen much longer ones...
A more detailed example with actual rows perhaps??

 

[SDB]

Morning,

We have a soratation machine that holds 120 sacks, when each sack is full its taken off the machine and placed into a york. once the york has nine sacks in it, it is then weighed and put to one side awaiting collection. The post office arrive and collect eight yorks and sign for them. what they are signing for is the eight yorks, (72 sacks) weighing in total X amount.

What i'm trying to do is create the query with the first eight york weights and mark them as collected so I don't weight them again.

Sorry I can't post an example I'm at work on a citrix system..

Cheers

 

[namliam]

Ah yes, now I get it...

You cannot use "Top" in combination with an update query
But you can do 2 things...
1) 2 queries
Query1
Your original query
Query2
Update query that updates on query1 without limitation (should result in 9 records beeing updated)

2) 1 query
Using a subquery to limit your updates
UPDATE CollectionQry SET CollectionQry.Collected= True
WHERE CollectionQry.ID In (SELECT TOP 9 CollectionQry.ID
FROM CollectionQry
WHERE CollectionQry.Field2=No
ORDER BY CollectionQry.ID);

Hope you get my drift...

 

[SDB]

Thanks namliam, Thats just what i needed.

Again thanks for you help, been on this for four days on and off..

 

[namliam]

Ah great! Happy to help