Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2004
    Posts
    47

    Unanswered: Updating Record in Table Using Unbound Form

    Hello,

    I am having a hard time with this project and I need a bit of help.

    I have 3 forms: 1 main one displaying the contents of a table and 2 unbound forms which serve for adding fields and updating fields. I managed to make the unbound form add new records using an SQL query. However, I don't know how to make the updating part.

    I would like when the user is watching the field he wants to update to hit a button and the update form to come out with the fields already filled out for him to edit. When he is finished he has to click Save and the record to be updated. Do I do it with an SQL query? If so how do I get the contents of the current record the user is looking at and how do I update that record with the new data?

    Thank you,

    a.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Because you are using unbound controls in your two latter forms you will need to pulled the required data from the 1st form. you can do this by placing something like this into the ControlSource property for each control field in your unbound forms:

    =Forms![myForm1Name]![myForm1ControlName]

    then, in the OnClick event for your save button, use the SQL UPDATE query statement in conjuction with the RunSQL function to save (update) the editing. Or, if your first form is still hot (open) then you could place code into the OnClick event of your save button and simply update the fields within the first form right away. For Example:

    Forms![myForm1Name]![myForm1ControlName] = Forms![myForm2Name]![TheControlOnMySecondForm]

    Forms![myForm1Name]![myForm1ControlName] = Forms![myForm3Name]![TheControlOnMyThirdForm]

    etc...


    Last edited by CyberLynx; 10-25-04 at 19:50.

  3. #3
    Join Date
    Sep 2004
    Posts
    47

    worked partly

    CyberLynx:

    Thanx for the reply. Your idea actually worked but partly.

    When I add that in my textboxes the data does transfer from main form to update form. However, it does not allow me to edit it. It says that it can't edit it since it is bound to main form. Initially I thought that this is because I had AllowEdits = False in the main form but even when I got rid of that and the other restrictrions it does not allow me to change anything.

    Any idea how to fix that?

  4. #4
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Why not allow the user to insert and update records on the main form? You can add data validation rules to the form and you can easily switch on/off editing and adding records by changing the properties of the form.

    The form properties you would change would be:

    Me.AllowAdditions = True/False
    Me.AllowEdits = True/False
    Me.AllowDeletions = True/False
    Me.DataEntry = True/False (this one clears the form of all data allowing the user to just add data. AllowAdditions must be set to True as well)

    These properties can be attached to a button on the form

  5. #5
    Join Date
    Sep 2004
    Posts
    47

    this is a way around

    The problem with your idea would be that all fields in the main form are textboxes and in the update form 2 of those I would like to be combo-boxes connected wit 2 additional tables related to the main table (which is bounded to the main form).

    I may however do it by replacing in the main form the textboxes with combo-boxes and locking and unlocking the fields.

  6. #6
    Join Date
    Sep 2004
    Posts
    47

    Updating current record with SQL

    Eventually, I created a form like the main one bound to the same table that would be used to update it.

    However, I want to save the information in that form using an SQL since some of the fields (like the combo boxes) are taking their contents from different related tables and actually when I hit save the updates are made in the related tables and not in the main table. So I was trying to do something like this (added to a button):

    Code:
    Private Sub btnUpdate_Click()
    
        
        Dim intCurrentRecordNum As Integer
        
        intCurrentRecordNum = Me.CurrentRecord
        
        Dim strUpdateSQL As String
        
        strUpdateSQL = "UPDATE tblUsers SET ID = txtID, Password = txtPassword, System = cmbSystem, Staff = cmbStaff, Temporary = chkTemporary WHERE ID Number = intCurrentRecord"
        
        DoCmd.RunSQL strUpdateSQL
        DoCmd.Close
    
    End Sub
    Am I doing something wrong?

    It gives me the following error: Runtime Error "3075" Syntac error (missing operator) in query expression "ID Number = intCurrentRecord".
    Last edited by astrosapiens; 10-27-04 at 11:33.

  7. #7
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Combo boxes can be used on the main form. There is no need for extra update SQL.

    The combo boxes will have their own recordset. The value of the combo box will be stored in the field on the main form. All you need to do is set the number of columns of the combo boxes to the number of columns in the combo box recordset. The first column is the value you want to save. Have a look at the help file to see the full details of how to work with a combo box

    Regards
    Justin

  8. #8
    Join Date
    Sep 2004
    Posts
    47

    thanx but..

    Thanx for the reply justin. I was thinking of that but I was using this format so that it is easier for the users to add additional options to the combo-boxes. I know it is a pain...but...

  9. #9
    Join Date
    Sep 2004
    Posts
    47

    figured the SQL but does not work

    I figured out how it should be but it is still updating the wrong fields in the wrong table.

    Code:
    Private Sub btnUpdate_Click()
    
        
        Dim intCurrentRecordNum As Integer
        
        intCurrentRecordNum = Me.CurrentRecord
        
        Dim strUpdateSQL As String
        
        strUpdateSQL = "UPDATE tblUsers SET ID = txtID, Password = txtPassword, System = cmbSystem, Staff = cmbStaff, Temporary = chkTemporary WHERE 'ID Number' = 'intCurrentRecordNum'"
        
        DoCmd.RunSQL strUpdateSQL
        DoCmd.Close
    
    End Sub

  10. #10
    Join Date
    Sep 2004
    Posts
    47

    Finally

    I finally figured it out. I hope this helps someone.

    In the table design I specified "Lookup Wizard" and pointed it to the tables that hold the values. Access created it's own relationships so you don't have to take care of it. Then I created out of it 2 forms: 1 main and 1 update. The main one will be only for looking and created an Update button that opens the update form to the specified field the user is looking at. In the update form the combo boxes are automatically created by the wizard and work perfectly. In the same time it is very easy to add options to the combo boxes by just entering the realted tables.

    Thanks to everyone that helped.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •