creating a sql string using insert and select

Question

I have a simple form with 3 fields - a listbox which displays a list of venues from table Venue, a text box with a date picker and a text box which allows the user to insert the event name. I need the listbox value to look at the venue table and select the venue id. I then want to update the event table with this venue id, and the text box values of date and event name. The code I have created is as shown :

 Private Sub cmdCreateEvent_Click()

    Dim dteDateHeld As Date
    Dim strEventName As String
    Dim strVenue As String
    Dim strSQL As String

    'check at least the event name is complete - venue and date may be selected after creating the event - check with Lynette
    If Me.txtEventName.Text = "" Then
        MsgBox "Please enter the name of the event in order to create this record", vbOKOnly
        Exit Sub
    End If

'pass textbox fields to variables
dteDateHeld = Me.txtDateHeld.Text
strEventName = Me.txtEventName.Text


If Me.lstVenue.ListIndex = -1 Then 'no venue selected
    'create sql string to insert details into table if no venue selected
    strSQL = "Insert Into Event (EventName, DateHeld) Values (" & strEventName & "," & dteDateHeld & ")"
Else
    'Create sql string to insert details into table if venue has been selected
    strVenue = Me.lstVenue.Selected
    'strSQL = "Insert Into Event (Venue, EventName, DateHeld)Select from Venue (VenueID) Where VenueName = " & strVenue & " "
End If


'execute the sql code to update the table
CurrentDb.Execute strSQL

'clear fields
Me.txtDateHeld.Text = ""
Me.txtEventName.Text = ""
Me.lstVenue = Null

End Sub

I really need help finishing my SQL strings - and if you happen to spot anything else wrong with my code please feel free to let me know. I'm trying to keep it as simple as possible.

It doesn't error as it is incomplete. I need help to finish the sql statements. Fingers crossed the rest will work. — Pixie007
– Pixie007, Commented May 1, 2016 at 17:55
Before designing the query as you suggested I thought I'd test the code I have and have hit a whole host of other problems. Firstly when I ran the above code I had the error "Can't reference property or method for a control unless the control has focus". This error was produced at the line : If Me.txtEventName.Text = "" Then — Pixie007
– Pixie007, Commented May 2, 2016 at 7:31
sorry - hadn't finished last post. It gave the above error for any code relating to the text of a textbox. After googling it seemed I needed to change the text property to value. It now tells me there is a data mismatch at the line : dteDateHeld = Me.txtDateHeld.Text. It also errors when I try to pass the selected value of the listbox into a variable : strVenue = Me.lstVenue.Selected. The error says argument not optional. — Pixie007
– Pixie007, Commented May 2, 2016 at 7:40

Zaider · Accepted Answer · 2016-05-02 13:43:19Z

1

Your dteDateHeld variable should be a string. When building a SQL string like you are doing and passing a date it needs a # around the variable. Strings need to have quote marks ' around them.

strSQL = "Insert Into Event (EventName, DateHeld) Values ('" & strEventName & "',#" & dteDateHeld & "#)"

For your list box selection if your ID is in the first column you can use Me.lstVenue.Value

But even if if you have an ID you can't construct a SQL statement like:

strSQL = "Insert Into Event (Venue, EventName, DateHeld)Select from Venue (VenueID) Where VenueName = " & strVenue & " "

The number of values that you insert has to match the number of values that you select. You can select values that aren't fields in your table if you pass the value.

strSQL = "Insert Into Event (Venue, EventName, DateHeld) Select VenueName, '" & strEventName & "',#" & dteDateHeld & "# FROM Venue Where VenueID = " & Me.lstVenue.Value & " "

answered May 2, 2016 at 13:43

Zaider

2,0231 gold badge24 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pixie007 Over a year ago

Thanks Zaider - that was really useful. I have found that the lstVenue is returning the VenueID and not the Venue name that is in the list. This actually makes my life easier as it is the VenueID that I want to insert into the event table so now it will be a simple insert. Can you help me with the syntax to insert an integer (VenueID is an int field). And also do you know why it is returning VenueID and not VenueName? I only have one column in lstVenue and that is VenueName. I am using Me.lstVenue to retrieve the selected value.

Zaider Over a year ago

@Pixie007 I'm not sure why it would return ID if you only have Name in your record set of your list box. Do you have 2 columns but one of them hidden? As for inserting an ID as a numeric value it is "INSERT INTO Event (EventID) Values(" & EventID & ")". There are no ' around the value like you would use for a text field.

Collectives™ on Stack Overflow

creating a sql string using insert and select

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related