Inserting selected listbox values into Access database table

[noobaccess]

Hi, i need help!

i am trying to insert multiple values that i have selected in my listbox to my database access table when i click the "add record button" but the values does not appear in my database table.

i have 2 listbox, when i select the first list box(businessNature) it will display the records in the 2nd list box(lstCuisine). However, the records in the the lstCuisine list box is not entered into the table in my database.

(ps: in my property sheet for my lstCuisine listbox its multi select is simple)


Here is my codes:

Private Sub Add_Record_Click()

If IsNull(Name) = True Or IsNull(Mobile) = True Or IsNull(Email) = True Or IsNull(CompanyName) = True Or IsNull(BusinessNature) = True Then
MsgBox "Please fill in Business Nature, Name, Contact, Email and Company Name"
Else
DoCmd.GoToRecord , , acNewRec
End If

Dim conceptValue As String
Dim strInsert As String
Dim i As Long


For i = 0 To lstCuisine.ListCount - 1
If lstCuisine.Selected(i) = True Then
conceptValue = conceptValue & lstCuisine.Column(0, i)
End If
Next i
strInsert = "INSERT INTO ProspectsTable(Concept)" & "VALUES(conceptValue);"
DoCmd.RunSQL strInsert
DoCmd.GoToRecord , , acNewRec

End Sub


Please help!!!!!!!! i need to know how to work this asap!

Thanks in advance!

 

[pr2-eugin]

The problem I believe is because your INSERT statement is not even getting executed.. I also have made some changes to your existing code.. I will break them down..

1 . Instead of using IsNull I have used Len() to check for the length of the string as a NULL <> empty String.. So to catch both Null and empty strings.. we use Len( theControl & vbNullString ) = 0.

2. The For loop you have is a bit complicated, as in it takes in the whole list and check every single item and checks if it is Selected, if so add it to the String.. Instead, you can optimise your code as,

For Each i In Me.lstCuisine.ItemsSelected
    conceptValue = conceptValue & Me.lstCuisine.ItemData(i)
Next i

3. The main reason your code did not work is because, the syntax was wrong, you did not leave a space between the ) and VALUES keyword, also conceptValue being a String needs to be enclosed in single quotes, so your Insert string changes to something like..

strInsert = "INSERT INTO ProspectsTable(Concept) " & "VALUES('" & conceptValue & "');"

4. Why do you go to a New Record after you inserted? I am not sure why, so I removed that line.

Putting all pieces together.. your code becomes,

Private Sub Add_Record_Click()
    If Len(Me.Name & vbNullString) = 0 Or Len(Me.Mobile & vbNullString) = 0 Or Len(Me.Email & vbNullString) = 0 Or Len(Me.CompanyName & vbNullString) = 0 Or Len(Me.BusinessNature & vbNullString) = 0 Then
        MsgBox "Please fill in Business Nature, Name, Contact, Email and Company Name"
        Exit Sub
    Else
        DoCmd.GoToRecord , , acNewRec
    End If

    Dim conceptValue As String
    Dim strInsert As String
    Dim i As Variant

    For Each i In Me.lstCuisine.ItemsSelected
        conceptValue = conceptValue & Me.lstCuisine.ItemData(i)
    Next i

    strInsert = "INSERT INTO ProspectsTable(Concept) " & "VALUES('" & conceptValue & "');"
    DoCmd.RunSQL strInsert
End Sub

 

[noobaccess]

Hey paul! Thanks!! the code works! but i am facing another problem now!

Why doesnt the values get inserted into the same line as the other records in the table?
When i add a record, it enter all the values(name, address, telephone number etc) into the same row as the table but my concept values did not get inserted into the same row as my other values(name, address, telephone number etc). How do i insert it into the same row ?

And, how do i put a comma if i want to insert more than 2 values in the table ?

Sorry i am really new to access!

 

[noobaccess]

and, i have another list box that has the same function as "lstcuisine" its called "park". do i use the same method that you have provided for lstcuisine?

this is what i have done, but i think my INSERT statement is wrong.

here:


Dim conceptValue As String
Dim parkValue As String
Dim strInsert As String
Dim i As Variant

For Each i In Me.lstCuisine.ItemsSelected
conceptValue = conceptValue & Me.lstCuisine.ItemData(i)
Next i

For Each i In Me.Park.ItemsSelected
parkValue = parkValue & Me.Park.ItemData(i)
Next i

strInsert = "INSERT INTO ProspectsTable(Concept, Park) " & "VALUES('" & conceptValue & "' & '" & parkValue & "');"
DoCmd.RunSQL strInsert

 

[pr2-eugin]

Okay.. I need a bit more information on this.. You have the ProspectsTable with fields like.. CustName, CustAddress, TelephoneNumber, Concept, Park.. And you have created a Bound Form to add/edit data in the table.. However the Concept and Park needs data from the two listboxes.. Is this correct?You do not need to run a Query on the same table if you are using a bound Form.. all you need to do is create another text box control on the form and make its control source to the Park field and Concept field.. So now this is what you have to do..

* Create two textbox controls that are bound to the two fields in your table,
* On the click of the button run the similar code,

For Each i In Me.lstCuisine.ItemsSelected
    conceptValue = conceptValue & ", " & Me.lstCuisine.ItemData(i)
Next i

For Each i In Me.Park.ItemsSelected
    parkValue = parkValue & ", " & Me.Park.ItemData(i)
Next i

* Finally assign it to the table field.. So code becomes..

Private Sub Add_Record_Click()
    If Len(Me.Name & vbNullString) = 0 Or Len(Me.Mobile & vbNullString) = 0 Or Len(Me.Email & vbNullString) = 0 Or Len(Me.CompanyName & vbNullString) = 0 Or Len(Me.BusinessNature & vbNullString) = 0 Then
        MsgBox "Please fill in Business Nature, Name, Contact, Email and Company Name"
        Exit Sub
    Else
        DoCmd.GoToRecord , , acNewRec
    End If

    Dim conceptValue As String, parkValue As String
    Dim i As Variant

    For Each i In Me.lstCuisine.ItemsSelected
        conceptValue = conceptValue & ", " & Me.lstCuisine.ItemData(i)
    Next i
    
    For Each i In Me.Park.ItemsSelected
        parkValue = parkValue & ", " & Me.Park.ItemData(i)
    Next i

    Me.conceptTextBoxControlName = Right(conceptValue, Len(conceptValue)-1)
    Me.parkTextBoxControlName = Right(parkValue, Len(parkValue)-1)
End Sub

Apart from that, If you need to add data to the same row you have to use an UPDATE statement not INSERT.. However a word of caution to this, when you use UPDATE statement make sure that you uniquely identify the record else you will update all records with the same information.. For example if you have a structure like..

ID    FirstName    LastName    City
1    Paul    Sanders    New York
2    Stan    Smith    Los Angles
3    Paul    Rudd

If you use an UPDATE as,

UPDATE custTable SET City='Dayton';

The result would be..

ID    FirstName    LastName    City
1    Paul    Sanders    Dayton
2    Stan    Smith    Dayton
3    Paul    Rudd    Dayton

Inorder to avioid this you have to use the WHERE Clause..

UPDATE custTable SET City='Dayton' WHERE ID=3;

 

[noobaccess]

hmm.. actually i do not want to edit the records. What i mean was that when i actually insert a new record into the table, all the values are inserted in the same row except the values from lstCuisine and park listbox. So i want to know how i can insert the values from lstCuisine and park listbox into the same row as my other values(which are name, telephone number, address etc) using the listbox. i want to keep my listbox.

This is what i see in my database table now:

Name Address telephone no Business Nature Concept Park
Mary XXX 99999999 F&B
Asian mount emily
As you can see, the concept and the park values are not on the same row as the values from name, address etc.

What i want to achieve in my database table is:

Name Address telephone no Business Nature Concept Park
Mary XXX 99999999 F&B Asian mount emily

Everything on the same row.


Answering your question:
Concept(lstCuisine) itself is a listbox with values on its own. Same for Parks(lstPark).

 

[pr2-eugin]

noobacess, I do understand what you mean.. Could you please read my post #5 again?

Also how do you add the other values mary, xx,999999 in the table?

 

[noobaccess]

In #5 you are telling me to add a textbox am I right? Correct me if i am wrong. I add the record using DoCmd.GoToRecord , , acNewRec . The name, address, telephone number etc values are in textbox format.

 

[pr2-eugin]

Are you using a Bound Form?

a bound form has a RecordSource, a table or query to which the form is "tied" or "based". An unbound form does not have a RecordSource, that doesn't mean it can't contain data, but the programmer will have to bring that data in manually, where a bound form automatically is associated with some data.

Look at the example I have put, it is an unbound Form.. on how to add data to a table.. More information here..

 

[noobaccess]

hey thanks paul!

but i have a problem now

strInsert = "INSERT INTO ProspectsTable(BusinessNature, Park, BusinessIdea, DateofEnquiry, Name, Address, Mobile, Fax, Email, CompanyName, Website, PricePoint, Concept) " & "VALUES('" & businessNatureValue & "','" & parkValue & "','" & Me.Business_Idea & "','" & Me.Date_of_Request & "','" & Me.txtName & "','" & Me.Address & "','" & Me.Mobile & "','" & Me.Fax & "','" & Me.Email & "','" & Me.CompanyName & "','" & Me.Website & "','" & Me.PricePoint & conceptValue & "');"

Is there something wrong with my SQL statement?

 

[noobaccess]

Hey hey!!! thanks! i found out the error in my sql statement ! And my records are inserted correctly like how i want it! Thanks Alot !!!!