Thanks for all your assistance I am still having trouble getting the Module to run. I can run it fine in the VB command but when I try adding it a a command button on my form it will not run... Any Ideas?
I think I understand but not sure how to accomplish the Menu form and button. I have tried several differetn combinations but my button will not command the run.
In the button Click event property, select [Event Procedure]. Click the ellipsis (...) to open the VBA editor at that procedure. Type or copy/paste code within the Click event procedure. This code can be the full procedure or can call the procedure in the general module.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
High June I have ran into a small issue with the counting of records and deviding them mathing the last with the first and so on... When the records get to the Middle two it is duplicating only two records in the Temp table and misses two records.... Really wierd thing.
RidersID Rating Full_Name Rank HorseName Class 246 1 Jamilyn Weigand AM Fabie Jr Horse 254 1 Deanna Armstrong AM Color Me Paint Jr Horse 241 1 Jordan Lesh PRO After shocks lady lena Jr Horse 243 1 Bill Womack AM Carry Lynx Jr Horse 255 2 Shelley Fitzgerald AM Badges Smart Chica Jr Horse 253 2 Colleen Boyer AM Beckys Pepto Jr Horse 249 2 JT Powell AM Indian Oak Jr Horse 256 2 Colleen Bowman AM Back For Cash Jr Horse 238 2 Pete Maragos AM 2 Socks Jr Horse 244 2 Kelly Downs AM Dakota Jr Horse 240 3 Brian McClain NP Haven Jr Horse 248 3 Jessica Burk NP Haidas Classie Chick Jr Horse 251 3 Dan Weigand NP Kates Smokin Gun Jr Horse 252 4 Elaine Jackson NP Little Girl Jr Horse 239 4 Rick Gaston PRO A Happenin Bet Jr Horse 247 4 Brian Kruger NP Genuine Skyline Jr Horse
After I run the Module for I get this returned It duplicates the two middle records..
RecNum1 RecNum2 RecNum3 RecNum4 RecNum5 Data1 Data2 Data3 Data4 Data5 246 247 Jamilyn Weigand Brian Kruger 1 4 AM NP Fabie Genuine Skyline 254 239 Deanna Armstrong Rick Gaston 1 4 AM PRO Color Me Paint A Happenin Bet 241 252 Jordan Lesh Elaine Jackson 1 4 PRO NP After shocks lady lena Little Girl 243 240 Bill Womack Brian McClain 1 3 AM NP Carry Lynx Haven 255 248 Shelley Fitzgerald Jessica Burk 2 3 AM NP Badges Smart Chica Haidas Classie Chick 253 251 Colleen Boyer Dan Weigand 2 3 AM NP Beckys Pepto Kates Smokin Gun 249 249 JT Powell JT Powell 2 2 AM AM Indian Oak Indian Oak 256 256 Colleen Bowman Colleen Bowman 2 2 AM AM Back For Cash Back For Cash
I cannot replicate the issue.
Post the actual code you are using or even provide the db. Follow instructions at bottom of my post.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Here is the Code I am using....
Here is the actual Module Code I am working with...
Sub MatchRecs()
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim i As Integer
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM DrawJrHorseQuery ORDER BY Rating ASC;", dbOpenSnapshot)
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM DrawJrHorseQuery ORDER BY Rating DESC;", dbOpenSnapshot)
CurrentDb.Execute "DELETE FROM TempTable"
If rs1.RecordCount > 0 Then
If rs1.RecordCount Mod 2 <> 0 Then
MsgBox "Odd number of records. Procedure canceled."
Else
For i = 1 To rs1.RecordCount / 2
CurrentDb.Execute "INSERT INTO TempTable(RecNum1, RecNum2, RecNum3, RecNum4, RecNum5, Data1, Data2, Data3, Data4, Data5) VALUES(" & _
rs1!Rating & ", " & rs2!Rating & ", '" & rs1!Full_Name & "', '" & rs2!Full_Name & "', '" & rs1!Rank & "', '" & rs2!Rank & "', '" & rs1!RidersID & "', '" & rs2!RidersID & "', '" & rs1!HorseName & "', '" & rs2!HorseName & "')"
rs1.MoveNext
rs2.MoveNext
Next
End If
End If
End Sub
I can't see anything wrong with the code. I still can't replicate the issue with the data sample you posted even using your expanded INSERT sql.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
It seems that everything works ok until i have all 2 in the rating column then it duplicated some of the records.... and I must run the module with the rating because it is critical could it be because I have the rating beinf ASC and DESC causing the issue.
I did not notice that. Yes, that would be an issue. My suggested code orders by unique identifier.
Try:
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM Table1 ORDER BY Rating, ID;", dbOpenSnapshot)
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM Table1 ORDER BY Rating DESC, ID DESC;", dbOpenSnapshot)
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
If I change these to lines of the Module it does not duplicate the records....
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM DrawJrHorseQuery ORDER BY RidersID ASC;", dbOpenSnapshot)
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM DrawJrHorseQuery ORDER BY RidersID DESC;", dbOpenSnapshot)
It appears that when it is counting and matching the records using the rating thats when it gets to the middle records and it skips and is random and then pulls duplicates.. it seems to be something with the duplicate numbers in the rating field.......
This method requires use of a unique record identifier so cannot use any field alone that has duplicate identifiers.
If RidersID is a unique record identifier, it should work.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.