Inserting values from combobox in table (1 Viewer)

[Yashika]

Hi Folks,

I am a newbie to ACCESS . And i am stuck with inserting values of combobox directly into a record in table.
I have five different tables(each has only one cloumn) and a complete full_details table(contains 5 cloumns).I have a simple form with five comoboxes and a save button on it. The five comboboxes are populated from five different comboxes at the time of form load. Now, I want to add values selected from these comboxes in fulll_details table.
My form is unbound.
I tried with VBA code ,

on the save button click,
I have written ,


Private Sub save_Click()
CurrentDb.Execute "Insert into Full_Details(Col1,col2,col3,col4,col5) Values ('" & Combo0 & "','" & Combo1 & "','" & Combo2 & "','" & Combo3 & "','" & Combo4 & "');"
MsgBox "Record Saved !!!", vbInformation, "Success"
End Sub


It gives me some error :banghead::banghead::banghead::banghead:
"You can't reference a property or method for a control unless the control has the focus property set"

Ahhh...now this has got on my nerves...

See, above query works fine ,if i set focus of one combobox,but it adds only 1 combobox value in full_details table as only 1 column value.

Please help asap. This forum has helped me a lot. Finally, i have registered myself today.

Hoping to get help from you all experts in ACCESS.

 

[James Dudden]

Try puttin 'Me!' in front of each controlname as below:

Private Sub save_Click()
CurrentDb.Execute "Insert into Full_Details(Col1,col2,col3,col4,col5) Values ('" & Me!Combo0 & "','" & Me!Combo1 & "','" & Me!Combo2 & "','" & Me!Combo3 & "','" & Me!Combo4 & "');"
MsgBox "Record Saved !!!", vbInformation, "Success"
End Sub

 

[pr2-eugin]

Hello Yashika, First.. Welcome to AWF..

I would just add a small suggestion to James's solution.. Use a . (dot) operator rather than ! (bang), using dot will allow you to see the list of controls available as you type..

Private Sub save_Click()
    CurrentDb.Execute "INSERT INTO Full_Details(Col1,col2,col3,col4,col5) Values ('" & Me.Combo0 & "','" & Me.Combo1 & "','" & Me.Combo2 & "','" & Me.Combo3 & "','" & Me.Combo4 & "');"
    MsgBox "Record Saved !!!", vbInformation, "Success"
End Sub

Also on top of that.. The Error sounds to me more like you are using a Property of a control (like Combo0.Text maybe ??!!?), if you have used Me. there is no need to refer to the Property .Value/.Text..

As a matter of fact it is best not to refer to the property .Value specially .Text.. .Text requires the control to have the focus..

 

[CJ_London]

Are your combo boxes actually called Combo1 etc or are they named something else? If something else then you need to change it in your sql.

Also, I notice that all the combo's are being treated as text - is this the case or are some of them going into numeric or date fields? if so you need to remove the single quotation marks for numbers and replace them with # for dates - which also will need to be formated american style - e.g. format(combo2,"mmddyyyy")

 

[Yashika]

hi All,

Thanku so much for all your suggestions.
It really feel awesome when you people help.

I got a answer before you all posted.
But no worries, I will tell where I was going wrong.

I had columns name with spaces. e.g Country Name,
so i needed to add square brackets, [Country Name] for the column name.
So the insert query would be
CurrentDb.Execute "Insert into Full_Details([Country Name],[city name],[street Name],[house Name],[flat Name]) Values ('" & Me.Text15 & "','" & Me.Text18 & "','" & Me.Text20 & "','" & Me.Text22 & "','" & Me.Text24 & "');"

I actually copied the values of each combobox into text and then wrote the query.
Morover,there is no need to set focus of textbox. Neither need to set property of textbox as text or Value.

Now , everything is working fine..but now i want to check if the inserted record is already present in table.
I tried to create a select query to count the no of rows which has same values as comboboxes.the selsect query is like :

selesct count(*) from full_details where colm= 'comobo1' ... and so on.

In vba code, i wrote
Dim str As String
str = "find"
If (Me.str.Count > 0) Then
error "already present
else
insert query here...
end if

i am getting an error ,invalid quantifier.
I dont know where i am wrong.

Please help me.

 

[pr2-eugin]

Me.str.Count is not a valid operation.. I think in this case you need to use either DCount or DLookUp or the combination of the two, to get the result..

 

[CJ_London]

Or

Incorporate directly into your query

current.execute("Insert into..... WHERE [Country Name]<>'" & Combo0 & "' AND [City Name] <> '" &....)

if currentdb.recordsaffected=0 then msgbox "not inserted - data already exists"

Benefit of this is the activity is done by accessing the table once rather than twice which can have implications if two people are trying to uppdate at the same time.

PS Recommend getting rid of those spaces if you can, makes it easier in the long run!