Solved Access 2010 How to use Append query in VBA to update a table to match number of records in another table? (1 Viewer)

[SteveStew444]

Hello AW!

I hope Everyone is well.

Table A has 100 records with Key AutoNumber field named DecisionID.
Table B has 60 records with Key AutoNumber field named BetID.

When a form opens I would like the Event Procedure to run some code that Adds 40 more blank records to Table B to match the number of records in Table A. The code will need to check if the Tables already contain the same number of records and then nothing needs to happen.

Thank you in advance for your help!

 

[arnelgp]

why?
anyway you can do it on the Form's Open event:

Private Sub Form_Open(Cancel As Integer)
Dim nTableARecordCount As Long
Dim nTableBRecordCount As Long
Dim i As Long
Dim db As DAO.Database
nTableARecordCount = DCount("1", "TableA")
nTableBRecordCount = DCount("1", "TableB")
If nTableARecordCount <> 0 And nTableARecordCount > nTableBRecordCount Then
    Set db = Currentdb
     With db.CreateQueryDefs("", "Insert Into TableB (FieldNameHere) Values ([p1]);"
         For i = 1 to nTableARecordCount - nTableBRecordCount
             .Parameters(0) = Null
             .Execute
         Next
     End With
End If
Set db = Nothing
End Sub

 

[plog]

This just doesn't seem correct. I don't know if helping you do this is truly helping you.

So, can you better explain your setup? Give us a paragraph about what this database does.

Why do you need to do this? Why do you need blank records in a table? That seems more like a spreadsheet procedure than a database procedure. Very suspect.

Why do you need to program this into form? This makes it seem like its not a one time event but something users will perform regularly. Doing this once is very odd in a database, but coding it such that users can do it repeatedly is another red flag onto the laundry pile. Also, if it is to be done multiple times that means your table counts will drift in and out of sync. Does that mean there is a complementary DELETE query you need as well?

Again, give us the big picture because the whole issue seems like the wrong way to handle whatever the true issue you are trying to deal with.

 

[SteveStew444]

why?
anyway you can do it on the Form's Open event:

Private Sub Form_Open(Cancel As Integer)
Dim nTableARecordCount As Long
Dim nTableBRecordCount As Long
Dim i As Long
Dim db As DAO.Database
nTableARecordCount = DCount("1", "TableA")
nTableBRecordCount = DCount("1", "TableB")
If nTableARecordCount <> 0 And nTableARecordCount > nTableBRecordCount Then
    Set db = Currentdb
     With db.CreateQueryDefs("", "Insert Into TableB (FieldNameHere) Values ([p1]);"
         For i = 1 to nTableARecordCount - nTableBRecordCount
             .Parameters(0) = Null
             .Execute
         Next
     End With
End If
Set db = Nothing
End Sub

Thank you very much! I'll try this code

 

[SteveStew444]

This just doesn't seem correct. I don't know if helping you do this is truly helping you.

So, can you better explain your setup? Give us a paragraph about what this database does.

Why do you need to do this? Why do you need blank records in a table? That seems more like a spreadsheet procedure than a database procedure. Very suspect.

Why do you need to program this into form? This makes it seem like its not a one time event but something users will perform regularly. Doing this once is very odd in a database, but coding it such that users can do it repeatedly is another red flag onto the laundry pile. Also, if it is to be done multiple times that means your table counts will drift in and out of sync. Does that mean there is a complementary DELETE query you need as well?

Again, give us the big picture because the whole issue seems like the wrong way to handle whatever the true issue you are trying to deal with.

Thank you for your reply.

The small piece of code analyzes a table of baccarat decisions based on a betting strategy. Table A imports thousands of decisions where 1 = a Banker Win and 0 = a Player Win. The code needs Table B to track the status of the betting strategy where it is constantly updating to Table B. I received an error that Table B did not have a record that matched table A. So I figured that an easy fix would be to check if the tables match the number of records before the code runs and the error goes away. The error went away when I manually added the blank records, now I would like it to happen automatically. Hope this helps!

 

[Pat Hartman]

I received an error that Table B did not have a record that matched table A.

It is far better strategy to figure out what caused an error and fix that error than to mask the error. We still don't have enough information to understand what you are doing or why a problem occurred with it. How can you rely on your analysis if the underlying procedure generates errors?

 

[SteveStew444]

It is far better strategy to figure out what caused an error and fix that error than to mask the error. We still don't have enough information to understand what you are doing or why a problem occurred with it. How can you rely on your analysis if the underlying procedure generates errors?

That is a fair point! In fact, I ran into a more complex error. A query works fine until around record 114. I'll post the code here to give a better idea of what I'm trying to accomplish.

 

[SteveStew444]

That is a fair point! In fact, I ran into a more complex error. A query works fine until around record 114. I'll post the code here to give a better idea of what I'm trying to accomplish.

Hi Pat, thank you again.

Thank you for your reply.

The small piece of code analyzes a table of baccarat decisions based on a betting strategy. Table A imports thousands of decisions where 1 = a Banker Win and 0 = a Player Win. The code needs Table B to track the status of the betting strategy where it is constantly updating to Table B. I received an error that Table B did not have a record that matched table A. So I figured that an easy fix would be to check if the tables match the number of records before the code runs and the error goes away. The error went away when I manually added the blank records, now I would like it to happen automatically. Hope this helps!

Thank you Plog! It is working now. Cheers mate!

 

[SteveStew444]

That is a fair point! In fact, I ran into a more complex error. A query works fine until around record 114. I'll post the code here to give a better idea of what I'm trying to accomplish.

Hi Pat! Thank you for your post. It is all working now and the other error was because I did not sort properly. All good now, thanks mate!

 

[SteveStew444]

Thank you very much! I'll try this code

Hi arnelgp,

I am adapting your code to run in the Public Sub instead Form_Open.
I am having trouble with the line of code below: Error says "Compile error Method or data member not found"
With db.CreateQueryDefs("", "Insert Into TableB (FieldNameHere) Values ([p1]);")

What does "Values ([p1])" mean?
I need to only create a blank record with its new AutoNumber, if that helps.

Thank you very much!

 

[arnelgp]

What does "Values ([p1])" mean?

on the code, "FieldNameHere" is the fieldname on your table (not the Autonumber field) that can accept Null (blank).
"Values ([p1])" is a placeholder, so looking down the code, .Parameter(0) (can also be .Parameter("p1")) is assigned with a
Null value. So in short, it is adding a record on TableB but with blank (Null) value on "FieldNameHere" field.

alternately, you can use this code:

Private Sub Form_Open(Cancel As Integer)
Dim nTableARecordCount As Long
Dim nTableBRecordCount As Long
Dim db As DAO.Database
nTableARecordCount = DCount("1", "TableA")
nTableBRecordCount = DCount("1", "TableB")
If nTableARecordCount <> 0 And nTableARecordCount > nTableBRecordCount Then
    Set db = Currentdb
    db.Execute "Insert Into TableB (FieldNameHere) Select Top " &  nTableARecordCount - nTableBRecordCount & " Null From TableA;"
End If
Set db = Nothing
End Sub

 

[DHookom]

Hi arnelgp,

I am adapting your code to run in the Public Sub instead Form_Open.
I am having trouble with the line of code below: Error says "Compile error Method or data member not found"
With db.CreateQueryDefs("", "Insert Into TableB (FieldNameHere) Values ([p1]);")

What does "Values ([p1])" mean?
I need to only create a blank record with its new AutoNumber, if that helps.

Thank you very much!

There is no db.CreateQueryDefs, it should be db.CreateQueryDef.

 

[SteveStew444]

There is no db.CreateQueryDefs, it should be db.CreateQueryDef.

Thank you DHookom

 

[SteveStew444]

on the code, "FieldNameHere" is the fieldname on your table (not the Autonumber field) that can accept Null (blank).
"Values ([p1])" is a placeholder, so looking down the code, .Parameter(0) (can also be .Parameter("p1")) is assigned with a
Null value. So in short, it is adding a record on TableB but with blank (Null) value on "FieldNameHere" field.

alternately, you can use this code:

Private Sub Form_Open(Cancel As Integer)
Dim nTableARecordCount As Long
Dim nTableBRecordCount As Long
Dim db As DAO.Database
nTableARecordCount = DCount("1", "TableA")
nTableBRecordCount = DCount("1", "TableB")
If nTableARecordCount <> 0 And nTableARecordCount > nTableBRecordCount Then
    Set db = Currentdb
    db.Execute "Insert Into TableB (FieldNameHere) Select Top " &  nTableARecordCount - nTableBRecordCount & " Null From TableA;"
End If
Set db = Nothing
End Sub

Thanks again arnelgp for your response!

I adapted the code to my field names:

Dim TableDecCount As Long
Dim TableBetCount As Long
Dim db As DAO.Database

TableDecCount= DCount("1", "tblDecisions")
TableBetCount = DCount("1", "tblBets")
If TableDecCount <> 0 And TableDecCount > TableBetCount Then
Set db = Currentdb
db.Execute "Insert Into tblBets(PlayerBet) Select Top " & TableDecCount - TableBetCount & " Null From tblDecisions;"
End If
Set db = Nothing

Nothing happens.
I placed this code to run at the very start of the same Public Sub.
Originally I said I wanted it to run on the Form Open, but I was wrong and that will not work for what I need.
I need it to run every time before the code runs.

Any thoughts on why nothing happens?
Thank you in advance for your help.

 

[arnelgp]

do you need to run it everytime you Open the db?
first, you create a Public function and put the code to it (first image).
then create a macro (2nd image). save the macro with the name "autoexec" (without quote).
you can also run the function on the Open event of a form (form1 on demo, see Open event).

 

[SteveStew444]

do you need to run it everytime you Open the db?
first, you create a Public function and put the code to it (first image).
then create a macro (2nd image). save the macro with the name "autoexec" (without quote).
you can also run the function on the Open event of a form (form1 on demo, see Open event).View attachment 116764


View attachment 116765

Thank you my friend! I will give it a go. I appreciate the help.

 

[cheekybuddha]

Cross-posted at UA