Results 1 to 9 of 9
  1. #1
    swsailor is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2019
    Location
    Springboro Oh
    Posts
    9

    Problem updating unbound table records

    A form containing 5 list boxes, Event, Badge, LastName, FirstName and attendees. It also has two list boxes, LstEvent and LstVolunteers. LstEvent is populated from TblEvents form rrom which a particular event is selected. The second list box LstVolunteers is populated from TblAllEvents, which contains records from all events. Each event can have same name as other events. A selection from LstEvent will cause a requery and populate the LstVolunteers with only the names from the selected LstEvent. When selecting a volunteer in the LstVolunteers the five text boxes are populated. Giving an opportunity to change any of the fields.

    With all the data updated on the form I am looking for a way to update that record in the TblAllEvents. I have tried the DoCmd.RunComman acCmd Save record with no success, I have tried to create an update query and can get nowhere with that.

    It seems that the best way is the update query but I have miserably there.

    There must be a reasonable way to save this record.
    Any help will be appreciated.
    Thanks

  2. #2
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Sounds like you need to bind the form to the table tblAllEvents, but your description is a little confusing as it sound like your referencing that in one of the combo's ?
    Could you post up a picture of the tables and the relationships ?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    what is an unbound table?
    forms are bound or unbound to tables.

    If you want to add data to a table, run an append query.
    use the controls on the form in the query, to add to the table.

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You need a many-to-many setup.
    Have a look at this for possible method that might work for you.
    Allows you to select a volunteer with 2 listboxes showing available events and chosen events. Arrows let you move events between listboxes.
    The form allows you to either search for a volunteer or scroll thru the volunteers, with the listboxes automatically updating for the current volunteer.
    MTM Schedule-Edit-Members-davetgri-v01.zip

  5. #5
    swsailor is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2019
    Location
    Springboro Oh
    Posts
    9
    Thanks. I resolved the update wit a query. I have the similar problem with a delete operation. A simple form with 2 texboxes label job and course containing 1005 and 202 respectively. Also a command button the on click has the following code. The message box shows 1005202 but execution errors with 'Missing operator in query expression'. I've checked other sites and find no help.
    Any help at all.

    Thanks

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You're going to have to show code to get focused assistance.

    Guessing: an approach

    Code:
    Delete From yourTableName
    Where 
    Job = formtxtBoxJobControlname and
    Course = formtxtBoxCourseControlname

  7. #7
    swsailor is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2019
    Location
    Springboro Oh
    Posts
    9
    Sorry i didnt show the code:

    Private Sub Command5_Click()
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    Dim Qry As String
    Qry = "SELECT * FROM" & "TblRequitements WHERE [Job Nbr]&[Course Nbr] =" & Me.Job & Me.Course
    MsgBox Qry
    dbs.Execute Qry
    End Sub

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Try
    "SELECT * FROM TblRequitements WHERE [Job Nbr] = " & Me.Job & " AND [Course Nbr] = " Me.Course

    assumes job and course contain numbers and not text
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    swsailor is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2019
    Location
    Springboro Oh
    Posts
    9
    Thanks guys, I have resolved it with the delete query.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-12-2016, 10:57 PM
  2. Adding Records from Unbound Form to Database Table
    By lzook88 in forum Programming
    Replies: 19
    Last Post: 09-13-2015, 05:18 PM
  3. Two unbound boxes not updating in table
    By DeathByData in forum Access
    Replies: 29
    Last Post: 05-22-2013, 12:51 PM
  4. Problem with multiple table updating query
    By fekrinejat in forum Queries
    Replies: 3
    Last Post: 01-26-2013, 11:17 PM
  5. Replies: 2
    Last Post: 03-27-2012, 01:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums