VBA Doesnt add content of subform to table

**shabbaranks** · 12-13-2011, 05:22 AM

Hi All,

I was wondering if someone could help please... I have the code below which on click adds content of the form to a subform table. Once the user is ready to submit to the main table they click the submit which adds the content to a main table.

The problem is - and Im not sure how to change it, all the fields apart from the description are mandatory. But if they description field is left blank the content doesnt get added to the main table as sql doesnt like empty fields. How would I change the code to compensate for this?

Code:

Private Sub AddToSheet_Click()
Dim sActivity As String
Dim sProject As String
Dim sHours As Double
Dim sDescrip As String
Dim Mysql As String
Dim sLogUser As String
Dim sDate As Date
Dim sCostCode As String
If Me.Activity = "" Then
    MsgBox "You must enter activity before continuing", vbInformation
    Me.Activity.SetFocus
    Exit Sub
End If
If Me.Project = "" Then
    MsgBox "You must enter a Project before continuing", vbInformation
    Me.Project.SetFocus
    Exit Sub
End If
If Me.LoggedInUser = "" Then
    MsgBox "You must enter activity before continuing", vbInformation
    Me.LoggedInUser.SetFocus
    Exit Sub
End If
sCostCode = Me.CostCode
sActivity = Me.Activity.Column(0)
sProject = Me.Project
sHours = Nz(Me.Hour, 0)
sDescript = Me.Description
sLogUser = Me.LoggedInUser
sDate = Me.DatePicker
Mysql = "INSERT INTO TimesheetTableTemp (sCostCOde, Activity, Project, Hours, Description, suser, [task date]) Values "
Mysql = Mysql & "('" & sCostCode & "', '" & sActivity & "', '" & sProject & "', " & sHours & ", '" & sDescript & "', '" & sLogUser & "', '" + Format(sDate) + "')"
DoCmd.SetWarnings False
DoCmd.RunSQL Mysql
DoCmd.SetWarnings True
Me!TimesheetTableSubform.Form.Requery
ClearField
End Sub
Private Sub submit_Click()
  DoCmd.SetWarnings False
  DoCmd.RunSQL "INSERT INTO TimesheetTable " & _
               "SELECT TimesheetTableTemp.* " & _
               "FROM TimesheetTableTemp;"
  DoCmd.RunSQL "DELETE * FROM " & _
               "TimesheetTableTemp"
  DoCmd.SetWarnings True
  
End Sub

Thanks

**hertfordkc** · 12-13-2011, 06:08 AM

Originally Posted by shabbaranks

Hi All,

I was wondering if someone could help please... I have the code below which on click adds content of the form to a subform table. Once the user is ready to submit to the main table they click the submit which adds the content to a main table.

The problem is - and Im not sure how to change it, all the fields apart from the description are mandatory. But if they description field is left blank the content doesnt get added to the main table as sql doesnt like empty fields. How would I change the code to compensate for this?

Code:

Private Sub AddToSheet_Click()
Dim sActivity As String
Dim sProject As String
Dim sHours As Double
Dim sDescrip As String
Dim Mysql As String
Dim sLogUser As String
Dim sDate As Date
Dim sCostCode As String
If Me.Activity = "" Then
    MsgBox "You must enter activity before continuing", vbInformation
    Me.Activity.SetFocus
    Exit Sub
End If
If Me.Project = "" Then
    MsgBox "You must enter a Project before continuing", vbInformation
    Me.Project.SetFocus
    Exit Sub
End If
If Me.LoggedInUser = "" Then
    MsgBox "You must enter activity before continuing", vbInformation
    Me.LoggedInUser.SetFocus
    Exit Sub
End If
sCostCode = Me.CostCode
sActivity = Me.Activity.Column(0)
sProject = Me.Project
sHours = Nz(Me.Hour, 0)
sDescript = NZ(Me.Description," ")
Me.Description = sDescript
sLogUser = Me.LoggedInUser
sDate = Me.DatePicker
Mysql = "INSERT INTO TimesheetTableTemp (sCostCOde, Activity, Project, Hours, Description, suser, [task date]) Values "
Mysql = Mysql & "('" & sCostCode & "', '" & sActivity & "', '" & sProject & "', " & sHours & ", '" & sDescript & "', '" & sLogUser & "', '" + Format(sDate) + "')"
DoCmd.SetWarnings False
DoCmd.RunSQL Mysql
DoCmd.SetWarnings True
Me!TimesheetTableSubform.Form.Requery
ClearField
End Sub
Private Sub submit_Click()
  DoCmd.SetWarnings False
  DoCmd.RunSQL "INSERT INTO TimesheetTable " & _
               "SELECT TimesheetTableTemp.* " & _
               "FROM TimesheetTableTemp;"
  DoCmd.RunSQL "DELETE * FROM " & _
               "TimesheetTableTemp"
  DoCmd.SetWarnings True
  
End Sub

Thanks

Let me know if it works.

**shabbaranks** · 12-13-2011, 06:36 AM

Thanks for that, but unfortunatly it doesnt work. Would this be because I need to change the bit where its going from the subform table to the main table I need to add the Nz instead of adding * as per below

Code:

Private Sub submit_Click()
  DoCmd.SetWarnings False
  DoCmd.RunSQL "INSERT INTO TimesheetTable " & _
               "SELECT TimesheetTableTemp.* " & _
               "FROM TimesheetTableTemp;"
  DoCmd.RunSQL "DELETE * FROM " & _
               "TimesheetTableTemp"
  DoCmd.SetWarnings True
  
End Sub

As this is where its loosing the data? Hope I make sense as I did in my head but when i wrote it down it was a little confusing

**hertfordkc** · 12-13-2011, 05:08 PM

Originally Posted by shabbaranks

Hi All,

I was wondering if someone could help please... I have the code below which on click adds content of the form to a subform table. Once the user is ready to submit to the main table they click the submit which adds the content to a main table.

The problem is - and Im not sure how to change it, all the fields apart from the description are mandatory. But if they description field is left blank the content doesnt get added to the main table as sql doesnt like empty fields. How would I change the code to compensate for this?

Code:

Private Sub AddToSheet_Click()
Dim sActivity As String
Dim sProject As String
Dim sHours As Double
Dim sDescrip As String
Dim Mysql As String
Dim sLogUser As String
Dim sDate As Date
Dim sCostCode As String
If Me.Activity = "" Then
    MsgBox "You must enter activity before continuing", vbInformation
    Me.Activity.SetFocus
    Exit Sub
End If
If Me.Project = "" Then
    MsgBox "You must enter a Project before continuing", vbInformation
    Me.Project.SetFocus
    Exit Sub
End If
If Me.LoggedInUser = "" Then
    MsgBox "You must enter activity before continuing", vbInformation
    Me.LoggedInUser.SetFocus
    Exit Sub
End If
sCostCode = Me.CostCode
sActivity = Me.Activity.Column(0)
sProject = Me.Project
sHours = Nz(Me.Hour, 0)
sDescript = Nz(Me.Description," ")
Me.Description = sDescript
sLogUser = Me.LoggedInUser
sDate = Me.DatePicker
Mysql = "INSERT INTO TimesheetTableTemp (sCostCOde, Activity, Project, Hours, Description, suser, [task date]) Values "
Mysql = Mysql & "('" & sCostCode & "', '" & sActivity & "', '" & sProject & "', " & sHours & ", '" & sDescript & "', '" & sLogUser & "', '" + Format(sDate) + "')"
DoCmd.SetWarnings False
DoCmd.RunSQL Mysql
DoCmd.SetWarnings True
Me!TimesheetTableSubform.Form.Requery

Does this all work as expected????
ClearField
End Sub
Private Sub submit_Click()
  DoCmd.SetWarnings False
  DoCmd.RunSQL "INSERT INTO TimesheetTable " & _
               "SELECT TimesheetTableTemp.* " & _
maybe this should be 
             "SELECT * " & _
                "FROM TimesheetTableTemp;"
Is this the first statement that you can identify as not working?
  DoCmd.RunSQL "DELETE * FROM " & _
               "TimesheetTableTemp"
  DoCmd.SetWarnings True
  
End Sub

Thanks

Your first post noted that SQL doesn't like empty fields and blanks. I've had more trouble with fields being null than I have with empty field or blank fields. The earlier change which I suggested inserted a blank into a null field, but didn't do anything about an empty field. These distinctions may be important elsewhere in your database. If you are sure that empty fields are causing problems, I will specifically change that. However, I'd like to narrow down what is not working properly.

**shabbaranks** · 12-14-2011, 04:12 AM

Apologies - I didnt really understand what you meant by your first post, but I do now. Instead if the description is ever blank your amended code adds a null instead of just leaving it blank.. Thanks

Im pretty sure its the fact that the the insert is adding blank\empty fields. The only way I can think and confirm this is to add data into the field and then all works fine.

**hertfordkc** · 12-14-2011, 05:32 AM

Originally Posted by shabbaranks

Apologies - I didnt really understand what you meant by your first post, but I do now. Instead if the description is ever blank your amended code adds a null instead of just leaving it blank.. Thanks

Im pretty sure its the fact that the the insert is adding blank\empty fields. The only way I can think and confirm this is to add data into the field and then all works fine.

The code which I suggested specifically substitutes a blank for a null field. It does not act on empty fields: they remain empty. I've not had problems with blank or empty fields, just nulls. I gather that the section
Mysql = "INSERT INTO TimesheetTableTemp (sCostCOde, Activity, Project, Hours, Description, suser, [task date]) Values " Mysql = Mysql & "('" & sCostCode & "', '" & sActivity & "', '" & sProject & "', " & sHours & ", '" & sDescript & "', '" & sLogUser & "', '" + Format(sDate) + "')" DoCmd.SetWarnings False DoCmd.RunSQL Mysql DoCmd.SetWarnings True Me!TimesheetTableSubform.Form.Requery Does this all work as expected???? ClearField End Sub

Isn't working as expected????

**shabbaranks** · 12-14-2011, 06:18 AM

Kind of - the whole bit of code which inserts the form data to the subform table works fine - which is below:

Code:

Private Sub AddToSheet_Click()
Dim sActivity As String
Dim sProject As String
Dim sHours As Double
sDescript = Nz(Me.Description, " ")
Me.Description = sDescript
Dim Mysql As String
Dim sLogUser As String
Dim sDate As Date
Dim sCostCode As String
If Me.Activity = "" Then
    MsgBox "You must enter activity before continuing", vbInformation
    Me.Activity.SetFocus
    Exit Sub
End If
If Me.Project = "" Then
    MsgBox "You must enter a Project before continuing", vbInformation
    Me.Project.SetFocus
    Exit Sub
End If
If Me.LoggedInUser = "" Then
    MsgBox "You must enter activity before continuing", vbInformation
    Me.LoggedInUser.SetFocus
    Exit Sub
End If
sCostCode = Me.CostCode
sActivity = Me.Activity.Column(0)
sProject = Me.Project
sHours = Nz(Me.Hour, 0)
sDescript = Nz(Me.Description, " ")
Me.Description = sDescript
sLogUser = Me.LoggedInUser
sDate = Me.DatePicker
Mysql = "INSERT INTO TimesheetTableTemp (sCostCOde, Activity, Project, Hours, Description, suser, [task date]) Values "
Mysql = Mysql & "('" & sCostCode & "', '" & sActivity & "', '" & sProject & "', " & sHours & ", '" & sDescript & "', '" & sLogUser & "', '" + Format(sDate) + "')"
DoCmd.SetWarnings False
DoCmd.RunSQL Mysql
DoCmd.SetWarnings True
Me!TimesheetTableSubform.Form.Requery
ClearField
End Sub

The part that doesnt work (unless there is text in the description) is the code below:

Code:

Private Sub submit_Click()
Dim strSQL, strSQLDelete As String
    DoCmd.SetWarnings False
    strSQL = "INSERT INTO TimesheetTable SELECT * FROM TimesheetTableTemp;"
    strSQLDelete = "DELETE * FROM TimesheetTableTemp;"
    DoCmd.RunSQL (strSQL)
    DoEvents
    DoCmd.RunSQL (strSQLDelete)
    DoCmd.SetWarnings True
End Sub

Appreciate your help on this one

**hertfordkc** · 12-14-2011, 11:16 AM

Originally Posted by shabbaranks

Kind of - the whole bit of code which inserts the form data to the subform table works fine - which is below:

Code:

Private Sub AddToSheet_Click()
Dim sActivity As String
Dim sProject As String
Dim sHours As Double
sDescript = Nz(Me.Description, " ")
Me.Description = sDescript
Dim Mysql As String
Dim sLogUser As String
Dim sDate As Date
Dim sCostCode As String
If Me.Activity = "" Then
    MsgBox "You must enter activity before continuing", vbInformation
    Me.Activity.SetFocus
    Exit Sub
End If
If Me.Project = "" Then
    MsgBox "You must enter a Project before continuing", vbInformation
    Me.Project.SetFocus
    Exit Sub
End If
If Me.LoggedInUser = "" Then
    MsgBox "You must enter activity before continuing", vbInformation
    Me.LoggedInUser.SetFocus
    Exit Sub
End If
sCostCode = Me.CostCode
sActivity = Me.Activity.Column(0)
sProject = Me.Project
sHours = Nz(Me.Hour, 0)
sDescript = Nz(Me.Description, " ")
If isempty(sDescript) then sDescript = " "
Me.Description = sDescript
sLogUser = Me.LoggedInUser
sDate = Me.DatePicker
Mysql = "INSERT INTO TimesheetTableTemp (sCostCOde, Activity, Project, Hours, Description, suser, [task date]) Values "
Mysql = Mysql & "('" & sCostCode & "', '" & sActivity & "', '" & sProject & "', " & sHours & ", '" & sDescript & "', '" & sLogUser & "', '" + Format(sDate) + "')"
DoCmd.SetWarnings False
DoCmd.RunSQL Mysql
DoCmd.SetWarnings True
Me!TimesheetTableSubform.Form.Requery
ClearField
End Sub

The part that doesnt work (unless there is text in the description) is the code below:

Code:

Private Sub submit_Click()
Dim strSQL, strSQLDelete As String
    DoCmd.SetWarnings False
    strSQL = "INSERT INTO TimesheetTable SELECT * FROM TimesheetTableTemp;"
    strSQLDelete = "DELETE * FROM TimesheetTableTemp;"
    DoCmd.RunSQL (strSQL)
    DoEvents
    DoCmd.RunSQL (strSQLDelete)
    DoCmd.SetWarnings True
End Sub

Appreciate your help on this one

How is sDescript defined? Is it a string or ....?

**shabbaranks** · 12-15-2011, 04:19 AM

Hi Hertfordkc its working now thanks. I think I mistyped some of the code as an earlier example which didnt work now works. Thanks again

**shabbaranks** · 12-15-2011, 09:10 AM

Ok this is really wierd... It was working I havent changed a thing and now its not, again the data just dissapears. It doesnt go into the Timesheet table it just dissapears. But it worked for 5 mins earlier and Im sure I havent changed anything... Code is below:

Adds initial code to subform

Code:

Private Sub AddToSheet_Click()
Dim sActivity As String
Dim sProject As String
Dim sHours As Double
Dim sDescrip As String
Dim Mysql As String
Dim sLogUser As String
Dim sDate As Date
Dim sCostCode As String
If Me.Activity = "" Then
    MsgBox "You must enter activity before continuing", vbInformation
    Me.Activity.SetFocus
    Exit Sub
End If
If Me.Project = "" Then
    MsgBox "You must enter a Project before continuing", vbInformation
    Me.Project.SetFocus
    Exit Sub
End If
If Me.LoggedInUser = "" Then
    MsgBox "You must enter activity before continuing", vbInformation
    Me.LoggedInUser.SetFocus
    Exit Sub
End If
sCostCode = Me.CostCode
sActivity = Me.Activity.Column(0)
sProject = Me.Project
sHours = Nz(Me.Hour, 0)
sDescript = Me.Description
Me.Description = sDescript
sLogUser = Me.LoggedInUser
sDate = Me.DatePicker
Mysql = "INSERT INTO TimesheetTableTemp (sCostCOde, Activity, Project, Hours, Description, suser, [task date]) Values "
Mysql = Mysql & "('" & sCostCode & "', '" & sActivity & "', '" & sProject & "', " & sHours & ", '" & sDescript & "', '" & sLogUser & "', '" + Format(sDate) + "')"
DoCmd.SetWarnings False
DoCmd.RunSQL Mysql
DoCmd.SetWarnings True
Me!TimesheetTableSubform.Form.Requery
ClearField
End Sub

Adds code from subform to table:

Code:

Private Sub submit_Click()
    Dim strSQL As String
    Dim strSQLDelete As String
    
    DoCmd.SetWarnings False
    
    strSQL = "INSERT INTO TimesheetTable SELECT * FROM TimesheetTableTemp;"
    strSQLDelete = "DELETE * FROM TimesheetTableTemp;"
    
    DoCmd.RunSQL (strSQL)
    DoEvents
    DoCmd.RunSQL (strSQLDelete)
    
    DoCmd.SetWarnings True
    
End Sub

Thanks

**hertfordkc** · 12-15-2011, 09:18 AM

I've had problems when switching between form design and VBA where it seems the VBA gets out of sync with the forms. Forcing saves doesn't necessarily prevent the problem. The solution is save everything, exit Access, then restart Access.
I haven't been able to replicate the problem**, so I didn't post as a bug, but I probably should have reported it.
** Meaning I can't identify the sequence that creates the problem. However, I've experienced the problem a number of times.

**shabbaranks** · 12-15-2011, 09:40 AM

Nope still not working - is there a way I can get it to step through whats happening or at least output some kind of error? Its wierd how it worked one minute then not the next though.

Thanks

**shabbaranks** · 12-15-2011, 09:55 AM

Is it possible to add one field at a time to the timesheet table to see if that works and then go from there? Or does Access require all fields have something or null in?

**hertfordkc** · 12-15-2011, 10:51 AM

Two possibilities here:
I repeat: null <> empty <> blank
Nulls in a query can give unexpected results, but not erratic results. Therefore, unless you were randomly changing your table or your entries, I doubt that is the problem, but I wouldn't rule it out.
The other possibility is that the code that is displayed does not match what is being executed. I firmly believe that is a possibility, especially since you seem to have code working and then it doesn't. This was the problem I was describing in my last post. I don't know how it is possible unless the Basic code is stored separately from what is being executed.
I use msgbox and debug.print extensively to locate where results aren't as expected. You can also use breakpoints and single stepping in the debugger to go through code and see values at each step.

**shabbaranks** · 12-16-2011, 03:47 AM

Hi... Well I managed to work out the problem. The table which the data was going to had allow blank entries set to no but yes on the temp table. After I changed this all was hunky dory

VBA Doesnt add content of subform to table

Thread Tools

VBA Doesnt add content of subform to table

Maybe

I need clarification on what you know is working and what isn't.

Let's be clear on this.

I don't think this should be necessary, but give it a try.

Similar Threads

checkboxes onclick, beforeupdate, afterupdate doesnt work

Help I dunno y it doesnt work

split table, compare its splitted fields with another table, and then append content

ACCESS File size doesnt change after deleting objects

My subform doesnt refresh????? HELP!

Posting Permissions