DoCmd.RunSQL with multiple SQL Statements

P

PatAccess

Registered User.
Local time
Today, 09:08
Joined
May 24, 2017
Messages
284
Hello,

Here is my code:

Code: 
Private Sub cmdAddNewField_Click()
Dim strAddFolderNoPath As String
Dim strAddFolderAndPath As String
Dim strAddCateg As String
Dim strAddTheme As String

strAddFolderNoPath = "INSERT INTO TblPhotosFolders(FolderName)VALUES('" & Me.txtAddFolder.Value & "')"
strAddFolderAndPath = "INSERT INTO TblPhotosFolders(FolderName,Path)VALUES('" & Me.txtAddFolder.Value & "','" & Me.txtAddFolderPath.Value & "')"
strAddCateg = "INSERT INTO TblPhotosCategories(Categories)VALUES('" & Me.txtAddCateg.Value & "')"
strAddTheme = "INSERT INTO TblPhotosThemes(ThemeName)VALUES('" & Me.txtAddTheme.Value & "')"

If Not IsNull(Me.txtAddFolder) And IsNull(Me.txtAddFolderPath) Then 'If no path was entered
    DoCmd.RunSQL strAddFolderNoPath
    Me.cboFolders.Requery
    Me.txtAddFolder = ""

ElseIf Not IsNull(Me.txtAddFolderPath) Then
    DoCmd.RunSQL strAddFolderAndPath 'If a path was entered
    Me.cboFolders.Requery
    Me.txtAddFolder = ""
    Me.txtAddFolderPath = ""
    
ElseIf Not IsNull(Me.txtAddCateg) Then
    DoCmd.RunSQL strAddCateg
    Me.cboCateg.Requery
    Me.txtAddCateg = ""
    
ElseIf Not IsNull(Me.txtAddTheme) Then
    DoCmd.RunSQL strAddTheme
    Me.cboThemes.Requery
    Me.txtAddTheme = ""
    
ElseIf Not IsNull(Me.txtAddCateg + Me.txtAddTheme) Then
    DoCmd.RunSQL strAddCateg
    DoCmd.RunSQL strAddTheme
    Me.cboCateg.Requery
    Me.cboThemes.Requery
    Me.txtAddCateg = ""
    Me.txtAddTheme = ""
End If

End Sub

Everything works until the last ElseIf statement, which only insert record into the first table (DoCmd.RunSQL strAddCateg) and not the second (DoCmd.RunSQL strAddTheme).
Is it that I need to run both SQL statements at the same time? If so, how do I do that? If not, any suggestions?

when both "Me.txtAddCateg" and "Me.txtAddTheme" have a value, I want to add those values to the appropriate tables.
 
Hi. What value is in Me.txtAddTheme? Just curious...
 
PatAccess said:
Just any Typed text
Click to expand...
Okay, thanks. I was just curious because of your use of this piece of code:
Code: 
ElseIf Not IsNull([COLOR=Red][B]Me.txtAddCateg + Me.txtAddTheme[/B][/COLOR]) Then
What do you expect it's supposed to do?
 
theDBguy said:
Okay, thanks. I was just curious because of your use of this piece of code:
Code: 
ElseIf Not IsNull([COLOR=Red][B]Me.txtAddCateg + Me.txtAddTheme[/B][/COLOR]) Then
What do you expect it's supposed to do?
Click to expand...

When a value is entered in both Me.txtAddCateg and Me.txtAddTheme fields, those values are entered within the appropriate tables (from the Docmd.RunSQL statements)
 
PatAccess said:
When a value is entered in both Me.txtAddCateg and Me.txtAddTheme fields, those values are entered within the appropriate tables (from the Docmd.RunSQL statements)
Click to expand...
Hi. So, are you saying if "both" textboxes have something typed in them, your code works fine? If so, I guess your original post means the second SQL statement is not working because maybe nothing was entered in either of the two textboxes then? Sorry, I'm a bit confused...
 
Whatever that last section is supposed to do, I think it will never run as it means both items cannot be null. Therefore the code would have already run in the earlier ElseIf statement.

BTW You should have a space before VALUES in each sql statement
 
isladogs said:
Whatever that last section is supposed to do, I think it will never run as it means both items cannot be null. Therefore the code would have already run in the earlier ElseIf statement
Click to expand...
That would have been my next suggestion... to step through the code.
 
Code: 
(1) If Not IsNull(Me.txtAddFolder) And IsNull(Me.txtAddFolderPath) Then 'If no path was entered
  ...

(2) ElseIf Not IsNull(Me.txtAddFolderPath) Then
  ...
    
(3) ElseIf Not IsNull(Me.txtAddCateg) Then
  ...
    
(4) ElseIf Not IsNull(Me.txtAddTheme) Then
  ...
    
(5) ElseIf Not IsNull(Me.txtAddCateg + Me.txtAddTheme) Then
  ...


The 5th set of code can never be reached because for it to be true the 3rd or 4th set of code gets hit first. You should have a line of code for those 2 variables like you do for the 1st set of code. Test them together before you test them individually.


Honestly, I wouldn't use a set of ElseIf's at all--there's no reason to try and cram all the logic together and make each dependent on a prior condition. Handle each variable indepently of the others:

Code: 
If IsNull(V1)=False Then
  ' handle the presence of Variable 1 here

  End If


If IsNull(V2)= False    
  ' handle presences of Variable 2 here


  End If

...
 
isladogs said:
Whatever that last section is supposed to do, I think it will never run as it means both items cannot be null. Therefore the code would have already run in the earlier ElseIf statement.

BTW You should have a space before VALUES in each sql statement
Click to expand...

OK then that is my problem. How do I get the code to look at at both fields, make sure they are NOT NULL and then insert those values in the appropriate tables? because the first statements are going into the same table. Me.txtAddCateg + Me.txtAddTheme are not. that's my issue.
 
You could either
1. swop the order , placing the final statement before the preceding two statements
Or as plot suggested
2. Use 4 separate if...End If statements omitting the last one

Method 1 would be more efficient as only the first true statement will be run.
Method 2 means each statement would be checked in turn and more than one may run
 
isladogs said:
You could either
1. swop the order , placing the final statement before the preceding two statements
Or as plot suggested
2. Use 4 separate if...End If statements omitting the last one

Method 1 would be more efficient as only the first true statement will be run.
Method 2 means each statement would be checked in turn and more than one may run
Click to expand...

and the Suggestion from theDBGuy
That would have been my next suggestion... to step through the code.
Click to expand...
Worked. Thank you Guys.

Here is the new working code
Code: 
Private Sub cmdAddNewField_Click()
Dim strAddFolderNoPath As String
Dim strAddFolderAndPath As String
Dim strAddCateg As String
Dim strAddTheme As String

strAddFolderNoPath = "INSERT INTO TblPhotosFolders(FolderName) VALUES('" & Me.txtAddFolder.Value & "')"
strAddFolderAndPath = "INSERT INTO TblPhotosFolders(FolderName,Path) VALUES('" & Me.txtAddFolder.Value & "','" & Me.txtAddFolderPath.Value & "')"
strAddCateg = "INSERT INTO TblPhotosCategories(Categories) VALUES('" & Me.txtAddCateg.Value & "')"
strAddTheme = "INSERT INTO TblPhotosThemes(ThemeName) VALUES('" & Me.txtAddTheme.Value & "')"

If Not IsNull(Me.txtAddFolder) And IsNull(Me.txtAddFolderPath) Then 'If no path was entered
    DoCmd.RunSQL strAddFolderNoPath
    Me.cboFolders.Requery
    Me.txtAddFolder = ""

ElseIf Not IsNull(Me.txtAddFolderPath) Then
    DoCmd.RunSQL strAddFolderAndPath 'If a path was entered
    Me.cboFolders.Requery
    Me.txtAddFolder = ""
    Me.txtAddFolderPath = ""
End If

If Not IsNull(Me.txtAddCateg + Me.txtAddTheme) Then
    DoCmd.RunSQL strAddCateg
    DoCmd.RunSQL strAddTheme
    Me.cboCateg.Requery
    Me.cboThemes.Requery
    Me.txtAddCateg = ""
    Me.txtAddTheme = ""
    
ElseIf Not IsNull(Me.txtAddCateg) And IsNull(Me.txtAddTheme) Then
    DoCmd.RunSQL strAddCateg
    Me.cboCateg.Requery
    Me.txtAddCateg = ""
    
ElseIf Not IsNull(Me.txtAddTheme) And IsNull(Me.txtAddCateg) Then
    DoCmd.RunSQL strAddTheme
    Me.cboThemes.Requery
    Me.txtAddTheme = ""

End If

End Sub
 
Hi. Glad to hear you got it sorted out. Good luck with your project.
 
Why mix up the two methods? The End if in the middle can be removed and the next line changed to ElseIf.

One more thing.
Using CurrentDB.Execute is more efficient than DoCmd.RunSQL.
 
isladogs said:
Why mix up the two methods? The End if in the middle can be removed and the next line changed to ElseIf.

One more thing.
Using CurrentDB.Execute is more efficient than DoCmd.RunSQL.
Click to expand...

I'll read up on the CurrentDB.Execute. Thank you for the ElseIf suggestion
 
You must log in or register to reply here.

Similar threads

F
Solved Form with subform filtered by two cascading combo boxes and a checkbox
2
Replies
26
Views
747
Faoineag
F
Y
New with a problem
Replies
4
Views
415
Pat Hartman
P
J
Solved Variable in HAVING clause
Replies
8
Views
508
John Sh
J
M
Solved Update records through a recordset
Replies
11
Views
547
Pat Hartman
P
C
Invalid use of Me.
Replies
8
Views
457
cheekybuddha
cheekybuddha

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom