Forum Home
Press F1
Thread ID: 48818	2004-09-02 04:12:00	Using unbound forms for data entry in Access	scarlettnz (2239)	Press F1

Post ID	Timestamp	Content	User
268214	2004-09-02 04:12:00	I have a flat-file database and wish to use an unbound form for data entry (giving the users the option to save or cancel). Because of the large amount of data that is required to be viewed I am using a tab control on the main form. When I run the following code I get an error message "syntax error (missing operator in query expression 'ID=' I have no idea what is going wrong here. Any ideas gratefully received. Option Compare Database Private Sub cmdAddNew_Click() On Error GoTo Err_cmdAddNew_Click Dim rst As Recordset Set rst = CurrentDb.OpenRecordset("select * from " & _ "tblAcademicWorkload where ID =" & txtID) If chkNew = True Then rst.AddNew Else rst.Edit End If rst!ReceiptDate = txtReceiptDate rst!Division = txtDivision rst!Dept = txtDept rst!Nature = txtNature rst.Update rst.Close Set rst = Nothing chkNew = False Exit_cmdAddNew_Click: Exit Sub Err_cmdAddNew_Click: MsgBox Err.Description Resume Exit_cmdAddNew_Click End Sub	scarlettnz (2239)
268215	2004-09-02 11:06:00	Hi scarlettnz, I'm not familiar with programming Access the way you have shown it here but it looks like the error is coming from this piece of code : >Set rst = CurrentDb.OpenRecordset("select * from " & _ "tblAcademicWorkload where ID =" & txtID) Is there a field in the table "tbltblAcademicWorkload" called "ID"? If not, this is the problem. If "ID" is there, does the equal function after ID need a value of some sort? There is an "isnull" function to detect instances of null rather than leaving the vaue after the equal sign empty (if this is what you have done - I can't really tell). My guess is that this is occuring in the query (clue here : [quote] I get an error message "syntax error (missing operator in query expression 'ID=' [end quote]) that has been used to create the form - look at the query supporting the form (is it the tabbed form? I'm not sure) Is it necessary to use an unbound form? If you used a bound form linked to the main table (or a simple query based on the table) and if the user presses "Esc" half way through then it has the same effect as cancel. It is only once the user moves off the last field and / or onto a new record that the record is saved in Access. Reading your explanation I'm not sure if it is necessary to use an unbound form. However, if you really really really don't want the form linked to the main table for data security / integrity purposes, then you could set up an intermediary table (set up with exactly the same as the main table) to collect the input data and then use a query under a macro to transfer the data across to the main data table whilst at the same time stripping out and dumping the incomplete or nonsensical data. However, if you think the users will get half way through and then save the record where you require data then you could either a ) use an "on exit" macro to identify the errant data to either delete the incomplete record or prevent the exit or, b) in the table "tblAcademicWorkload" set the value for "Required" to "Yes" for the fields the users must fill in - this will prevent a user from exiting without completing all of the necessary data, or they can still press "Esc" to get out of the edit / add record mode. I'm not sure if I have answered your question but I hope this helps. Andrew	andrew93 (249)
1