0

So here's my problem I can get my subform to requery once but that is it I can't get it to requery after new information is entered. Can someone please help! I have tried using RecordsetClone and that didn't work as well as other suggestions and I haven't been able to get anything to work

Private Sub cmdAdd_Click()

CurrentDb.Execute "INSERT INTO tbl_GroupVolunteers (GroupName, Leader,  
VolunteerName, Email, Phone, EmergencyContact, EmergencyNumber, 
NumberofVolunteers, RegistrationNumber)" & _
"VALUES ('" & Me.txtGroupName & "','" & Me.cboLeader & "','" & 
Me.txtVolunteerName & "','" & Me.txtEmail & "','" & Me.txtPhone & "','" & 
Me.txtEmergencyContact & _
"','" & Me.txtEmergencyNumber & "','" & Me.txtNumberofVolunteers & "','" & 
Me.txtRegistrationNumber & "')"

'refresh data
Me!subformGroupVolunteers.Form.Requery
'Clear form
cmdClear_Click

End Sub
Smandoli
  • 6,919
  • 3
  • 49
  • 83
anecessa
  • 1
  • 1
  • 4
  • Possible duplicate of [Compile Error: Method not found](http://stackoverflow.com/questions/33838422/compile-error-method-not-found) – Gustav Nov 24 '15 at 14:33
  • Looks to be a different issue, Gustav. anecessa - When you say you "can't get it to refresh", what do you mean? Is the code running? Is it breaking? Is it just not doing anything? Because from the code posted it looks like it should refresh every time the cmdAdd button is pressed. – Johnny Bones Nov 24 '15 at 15:29
  • 1
    @JohnnyBones it will refresh after one record is entered but it won't refresh after multiple adds. The code does run however, I am noticing that not all are being added to the table like they should be – anecessa Nov 25 '15 at 15:49
  • @anecessa -- this information is critical. I suggest you add it to the post. Welcome to StackOverflow! – Smandoli Nov 25 '15 at 16:50

1 Answers1

0

Put a break in your code and step through it to see where it's failing. I'd suggest separating the Execute function. Something like this:

Dim sqlStr as String

sqlStr = "INSERT INTO tbl_GroupVolunteers (GroupName, Leader,  
VolunteerName, Email, Phone, EmergencyContact, EmergencyNumber, 
NumberofVolunteers, RegistrationNumber)" & _
"VALUES ('" & Me.txtGroupName & "','" & Me.cboLeader & "','" & 
Me.txtVolunteerName & "','" & Me.txtEmail & "','" & Me.txtPhone & "','" & 
Me.txtEmergencyContact & _
"','" & Me.txtEmergencyNumber & "','" & Me.txtNumberofVolunteers & "','" & 
Me.txtRegistrationNumber & "')"

CurrentDb.Execute sqlStr

'refresh data
Me!subformGroupVolunteers.Form.Requery
'Clear form
cmdClear_Click

Or instead of stepping through it, you can add a messagebox like this:

Dim sqlStr as String

sqlStr = "INSERT INTO tbl_GroupVolunteers (GroupName, Leader,  
VolunteerName, Email, Phone, EmergencyContact, EmergencyNumber, 
NumberofVolunteers, RegistrationNumber)" & _
"VALUES ('" & Me.txtGroupName & "','" & Me.cboLeader & "','" & 
Me.txtVolunteerName & "','" & Me.txtEmail & "','" & Me.txtPhone & "','" & 
Me.txtEmergencyContact & _
"','" & Me.txtEmergencyNumber & "','" & Me.txtNumberofVolunteers & "','" & 
Me.txtRegistrationNumber & "')"

Msgbox sqlStr 'Display the string

CurrentDb.Execute sqlStr

'refresh data
Me!subformGroupVolunteers.Form.Requery
'Clear form
cmdClear_Click

This way, you can step through the variable and make sure the string that gets created is functioning properly. If records aren't being added correctly, then it's probably because that INSERT INTO statement isn't pulling in the data properly.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • I suggest you as this expert says, but take strSQL (if you can't grab it off the message box, take care of this by using `Debug.Print` to make it show in the Immediate Window) and run it as an ordinary Access Query. Do this by creating a query, go to SQL View, and paste. http://stackoverflow.com/a/1099570/122139 – Smandoli Nov 25 '15 at 16:49
  • @JohnnyBones neither of these solve the problem I have having but thank you for your suggestions! – anecessa Nov 29 '15 at 19:38
  • @johnnyBones these will insert into the table and display in the subform however they won't work after multiple times – anecessa Nov 29 '15 at 20:31
  • What code is in cmdClear_Click? Could the issue lie there rather than in this snippet? – GavinP Dec 04 '15 at 15:55