[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1775
  • Last Modified:

Edit Record and Save In Unbound Form

I have a list box on a form that also has unbound fields for each one of the table fields in the list box. I have it set up so that when you click on one of the rows in the list box it poplulates the unbound fields on the form. I also have an Edit button that once you click on it you can then edit the information and then click the Save button to post to the table. The code I have works fine if you only have one key but because I have two I'm not sure how to insert the additional field. When I click save I get the following error.

Syntax error (missing operator) in query expression 'StoreNumber=&txtStoreNumber and Date=4/4/2009'

The keys in tblsales are StoreNumber and Date.

The issue is the line 19 of code-
Set rst = CurrentDb.OpenRecordset("select * from tblsales where StoreNumber= & txtStoreNumber and Date=" & txtDate)

Here it is with one key but the only problem is I have two.
Set rst = CurrentDb.OpenRecordset("select * from tblsales where StoreNumber=" & txtStoreNumber)

This is probably just missing an operator but I need someone to take a look. The entire code for both the Edit and Save buttons are below. The Save button is where the issue is.
Private Sub cmdSave_Click()
  '--- only process Save if there is data in company name
    If IsNull(txtDate) Then
        MsgBox "Please Enter A Date"
        txtDate.SetFocus
        Exit Sub
    End If
    
    If IsNull(txtSales) Then
        MsgBox "Please Enter Sales"
        txtSales.SetFocus
        Exit Sub
    End If
    
    
    Dim rst As Recordset
    '--- use the primary keys to find the record
    '--- if it is a new record, this will find no records
    Set rst = CurrentDb.OpenRecordset("select * from tblsales where StoreNumber= & txtStoreNumber and Date=" & txtDate)
    If chkNew = True Then   '--- do we add a new record and save an existing one
        rst.AddNew
    Else
        rst.Edit
    End If
    '--- transfer data from text boxes to table fields
    rst!StoreNumber = txtStoreNumber
    rst!Date = txtDate
    rst!Sales = txtSales
    rst!Labor = txtLabor
    rst!CashOS = txtCashOS
    rst!Comments = txtNotes
    rst.Update  '--- save the record
    rst.Close   '--- close the recordset
    Set rst = Nothing   '--- reclaim the memory the recordset was using
    chkNew = False  '--- reset the new flag
    '--- enable the list box and the Add New button and the Close button
    '--- must be done before moving focus to the list box
    List52.Enabled = True
    cmdAddNew.Enabled = True
    cmdCancel.Enabled = True
    '--- make sure the newest data is in the list box
    List52.Requery
    '--- set the focus to the list box
    List52.SetFocus
    List52 = List52.ItemData(1)
    Call List52_AfterUpdate
    '--- disable the text boxes and the Save button, and make Edit button enabled
    
    
    txtDate.Enabled = False
    txtStoreNumber.Enabled = False
    txtSales.Enabled = False
    txtLabor.Enabled = False
    txtCashOS.Enabled = False
    txtNotes.Enabled = False
    cmdSave.Enabled = False
    cmdEdit.Enabled = True
End Sub
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub cmdEdit_Click()
 
    '--- allow editing of the current record only if one selected
    If List52.ItemsSelected.Count <> 1 Then Exit Sub
    
    '--- enable the text boxes and the Save button
    txtDate.Enabled = True
    txtStoreNumber.Enabled = True
    txtSales.Enabled = True
    txtLabor.Enabled = True
    txtCashOS.Enabled = True
    txtNotes.Enabled = True
    cmdSave.Enabled = True
   
    
    '--- set the focus to the first text box
    txtDate.SetFocus
    
    '--- disable the list box and the Add New button and the Close button
    '--- must be done after the setfocus as we cannot disable something that has focus
    List52.Enabled = False
    cmdAddNew.Enabled = False
    cmdCancel.Enabled = False
    cmdEdit.Enabled = False
End Sub

Open in new window

0
dk04
Asked:
dk04
1 Solution
 
jpipkinsCommented:
Replace this:

Set rst = CurrentDb.OpenRecordset("select * from tblsales where StoreNumber= & txtStoreNumber and Date=" & txtDate)

with this:

Set rst = CurrentDb.OpenRecordset("select * from tblsales where StoreNumber= " & txtStoreNumber & " and Date=#" & txtDate & "#")
0
 
dk04Author Commented:
Excellent!! Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now