Problem adding record with data from form

[gamesman]

I have a form that gathers data from multiple tables and funtions. Each control is unbound. I want to use a command button to then store the values into a table. (If the controls are bound then I get validation errors during the loading of the form as the control values are not setting to the fields validation requirements.)

I want to use a command button to add the values in the controls into a single table. Since the controls are unbound, I can't just use the acMenuEdit, AcSave route. I've tried the DoCmd.RunQuery (Insert Into table (field,,,) values (me.control1, me.control2,,,) and that didn't work.

I'm trying to stay away from ADO/DAO due to network environment restrictions.

I would appreciate any suggestions.

 

[hsitz]

A couple things. First, the proper docmd method for you, I think, is 'RunSQL', not 'RunQuery'.

Also, you would need to build the SQL string by concatenating the values of the controls, not just by quoting their references. Like this:

DoCmd.RunSQL("Insert Into table (field,,,) values (" me.control1 & ", " & me.control2 & ", " & [etc., etc.] & ", &quot

That should work to at least give you a valid SQL update query.

But I'm not sure that you can get by the fields' validation requirements by doing it in this way, if the validation requirements are built into the field properties in the table design form. Someone else should be able to tell you that off the top of their heads. -- Herb

 

[gamesman]

Sorry, Actually I was using runsql not runquery and if I use the ampersand I get an error message about expected end of statment.

 

[hsitz]

I left out an ampersand before the me.control1 on my sample, should have been:

DoCmd.RunSQL("Insert Into table (field,,,) values ("
& me.control1 & ", " & me.control2 & ", "
& [etc., etc.] & ", &quot

Building SQL string scan be tricky. Also, if the control values are strings then you need to have single quotes around them, like this:

DoCmd.RunSQL("Insert Into table (field,,,) values ('"
& me.control1 & "', '" & me.control2 & "', "
& [etc., etc.] & ", &quot

Or if they're dates you have to have '#' signs:

DoCmd.RunSQL("Insert Into table (field,,,) values (#"
& me.control1 & "#, #" & me.control2 & "#, "
& [etc., etc.] & ", &quot

Or mix and match depending on the control value type varies: number - nothing extra needed, string - need single quotes around value, date - need # signs around value. -- Herb

 

[gamesman]

OK, so this is what I've got now:

DoCmd.RunSQL ("INSERT INTO call_log ( acc_num, call_date, call_time, acc_name, stat, comment, callerid, csr_rep, crr_route, crr_called, paged, radio ) values ('"&me.txtAcc_num&"', #"&Me.txtCall_Date"#, #"&Me.txtCall_Time"#, '"&Me.lbxAcc_Name&"', '"&Me.optStat','"&Me.txtComment&"', '"&Me.cmbCallerid&"', '"&Me.txtCsr_Rep&"', '"&Me.lbxCrr_Route&"', '"&Me.lbxCrr_Called&"', "&Me.ckPaged&", "&Me.ckRadio&&quot&quot

and I'm getting an error at the break between the values: {"', #"} Compile Error Expected: List seperator or )

Any suggestions?