Hi Guys I need Help to turn this query Formula into a function that saves the results to a table.
The table name is ZHTCalc and the field is SeqID.
The formula is
Code:
SeqID: DCount("*","ZHTCalc","CoverID=" & [CoverID] & " AND SortID<" & [MovieID])+1
Delimiter problem using DCount inside the update query
Try this instead:
Code:
Dim N As Integer
N = DCount("*","ZHTCalc","CoverID=" & [CoverID] & " AND SortID<" & [MovieID])+1
CurrentDb.Execute "UPDATE ZHTCalc SET ZHTCalc.SeqID = N;"
In the table ZHTCalc are Primary key SortID and CoverID, and the One thats empty SeqId are all set as numbers
MovieID is taken from the Movie table and is Primary key set as number.
They are used for creating a cross tab query with SeqID as the row heading
CoverId as the Column heading and the value is any field I want to Pivot.
So essentially the result is SortId is the number of rows which is say 3000
MovieID is the rows broken into groups of 10 so numbered 1 to 10 repeated for 30 rows
SeqID is MovieID broken down to 10 number 1s in first group 10 Number 2s in second and so on.
In a query the above formula works fine I just need the results written to a table like the function for MovieID is.
Thanks Wayne but I need to just turn the formula in the query to vba so it writes the results to a table, similar to this one by Uncle Gizmo for the coverID
Code:
Public Function RebuildCoverid()
Dim curDB As DAO.Database
Dim CIDSequence As DAO.Recordset
Set curDB = CurrentDb
Dim strSQL_RSL As String
strSQL_RSL = "SELECT MovieID, CoverId FROM ZHPRebuild ORDER BY MovieID"
Dim X As Integer
Dim S As Integer
S = 15
Set CIDSequence = curDB.OpenRecordset(strSQL_RSL) ', dbOpenForwardOnly)
Do Until CIDSequence.EOF
X = X + 1
If X = S Then Let X = 1
CIDSequence.Edit
CIDSequence!CoverID = X
CIDSequence.Update
CIDSequence.MoveNext
Loop
End Function
I didn't reply earlier as I thought it was the middle of the night in West Oz
Your last response to Wayne shows a recordset loop.
Perhaps this issue is more complex than your OP indicated
Also, Wayne mentioned a utility by Allen Browne to convert VBA to SQL
In case you aren't aware I have extended this idea to work in both directions.
This is available in the sample databases area of this forum: https://www.access-programmers.co.uk/forums/showthread.php?t=293372
Anyway, I had assumed you are running this code from a command button on a form
I tested the code I posted in #4 from a form & it works.
However, it does depend on the controls CoverID & MovieID being 'available' to use in the update code
This suggests you don't have a control/field on your form called CoverID!
Try adding add a debug line to my previous code:
Code:
Dim N As Integer
N = DCount("*","ZHTCalc","CoverID=" & [CoverID] & " AND SortID<" & [MovieID])+1
[COLOR="Red"]Debug.Print N[/COLOR]
CurrentDb.Execute "UPDATE ZHTCalc SET ZHTCalc.SeqID = N;"
Does it give a value for N?
If not, suggest you assign variables intCover & intMovie & use them in the update query in place of the field names
ok I posted 2 pix of the Query Im using that works fine but I need the Seqid dcount formula to write the results into ZHTCalc table field Seqid .
the second pix shows the THCalc table or the querys datasheet view but the Seqid field isnt physically written into the field just held in memory.
where the function by uncle gizmo as posted above is written to the table which is the CoverID column.
So I need the formula turned into a function that writes the results to the ZHTCalc table.field SeqID that I can call in the query eg: Test:updateSeqID ()
the same as the AHCF:RebuildCoverid()
Ah!
Why didn't you say earlier that this was based on 2 tables?
Add the table names to the field names as shown below:
Code:
Dim N As Integer
N = DCount("*","ZHTCalc","CoverID=" & [ZHTCalc].[CoverID] & " AND SortID<" & [ZHTData].[MovieID])+1
Debug.Print N
CurrentDb.Execute "UPDATE ZHTCalc SET ZHTCalc.SeqID = N;"
That should now work!
...but it would have also done so using variables as suggested in last post
ok tried that still no luck ,
got external name not defined
so added " " around both and It still hung on the last movieID
so I changed the first table to the query name and that got passed it to the last line which gave too few parameters expected 2.
anyway im posting a cut down version so you can see for yourself.
If you delete the contents of ZHTCalc table and look at datasheet view of ZHPRebuild1 you will see it works, but of course it isnt written to the physical table.
Ps did mention a 2nd table in 3rd post but called it Movie Table instead of ZHTData which is its linked name, sorry my bad.
I do appreciate your help in this matter.
You didn't have a field SeqID in the table ZHTData so it was never going to work!
I've added the field and created an update query qryUpdateZHTSeqID to populate this field
The SQL for the query is:
Code:
UPDATE ZHTData LEFT JOIN ZHTCalc ON ZHTData.MovieID = ZHTCalc.SortID SET ZHTCalc.SeqID = DCount("*","ZHTCalc","CoverID=" & [CoverID] & " AND SortID<" & [MovieID])+1;
You may remember that code from my first reply - its what I said originally!
The code you had in a module was also never going to work as you should have been using your query ZHPRebuild.
If you want to use the module, just run the query from there
Code:
Public Function SeqRebuild()
'old code replaced with query
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdateZHTSeqID"
DoCmd.SetWarnings True
End Function
Thank you for that
After I delete all the data from the ZHTCalc I still need to run my query before yours for it to work but it works.
Before I posted the Thread I had the same update query as yours but with the function RebuildCoverid in it as the SeqID wont work without those fields.
The problem was it pasted the functions results but not the formulas hence the reason for the thread. I attached the Db with my original update query if your interested,
I must of missed something simple because its the same as yours plus 3 more fields, maybe they cant work in the same update query like they do in a normal query.
