vba compare and update

[Locopete99]

Hi Guys,

I'm self taught on VBA and access and I have an issue that I cant figure out.

I have 2 tables, one with the current progress of a user through a learning module.

The second is a results table to show how many sections of that module the user has completed.

I'll then join this to another table later for further use.

So both tables are the same and have the same fields:

ID
MODULE 1
MODULE 2
MODULE 3

(But with actual module names.)

What I want to do is run an update on this. However I dont want a blanket update, I only want to update fields where the progress table is higher then the results table. (If you didn't work on a module that day it will show a 0 on the export/ import which would overwrite the current progress.)

I've tried many ways to do this, my latest is below. Can someone help?

Private Sub Label0_Click()
Dim BEG As String

Dim LastID As String
With CurrentDb
Set rsnew = CurrentDb.OpenRecordset("Tbl_progress", dbOpenDynaset)

If rsnew.RecordCount > 0 Then
    rsnew.MoveFirst
 Do Until rsnew.EOF
 

BEG = DLookup("Beginner", "Tbl_Results", tbl_results.ID2 = Tbl_progress.ID)


 
 
With rsnew

        If BEG > [Beginner] Then
        ![Beginner] = BEG
        Else
       End If
    
End Sub

I know this code isn't complete. I just wanted to give you an idea of what i'm trying.

Thanks

 

[June7]

Not understanding why you have 2 identical tables. Provide sample data for each table.

Are the ID fields both autonumber type? If they are then there should not be a relationship based on those fields.

 

[Minty]

I think you need to show us the actual tables and fields. This should be achievable in one query, possibly with a sub query, depending on how your data is stored.

Show us the tables, and some sample data from both and the desired result. We'll need enough data to cover all outcomes.

 

[Locopete99]

Please see attached

 

[Minty]

Your data isn't normalised.
Your result table should simply store the module ID, user ID , Result and possibly result date.

Then your query becomes simple. As it is with your spreadsheet style table it would be almost impossible to query, and if you add a module you have to add a field to your table , which is a big no no in design terms.