Editing a single record via selection on a form

[decco21]

Hi all,



I just can't get my head quite around how to use a form to select a specific record from a table for editing on its own.

I am building a .mdb (2003-2007) in Access 2013. Originally I used a split form for this because Access 2013 doesn't take this functionality away even though I'm saving as a 2003-2007 file but because Access 2003 doesn't support split forms I am unable to do this (and any attempts to replicate the split form with subforms have just had me frustrated and I've given up on that idea).

My first form Edit a Drawing search (see below) prompts the user to select a Job Number from combobox JobNumber which populates via the SELECT DISTINCT function from the main table Drawing List. Another combobox DrawingNumber then filters based on the Job Number selection (as I only want the user to be able to edit a record that already exists in the database.



On clicking 'Edit Drawing', I want the following form Edit a Drawing to appear (see below) with the textboxes populated with the fields from the record selected on the Edit a Drawing search form, allowing the user to change all fields and then save the record. For this second form I've tried using something like the code below but to no avail - it always seems to be pulling up the first record or no record at all.



Any help would be really appreciated at this point! Thanks

Private Sub Form_Load()
    DoCmd.OpenTable "Drawing List"
    DoCmd.SearchForRecord acDataTable, "Drawing List", acFirst, Me.JobNumber = [Forms]![Edit a Drawing search].[JobNumber]
End Sub

[pbaldy]

One way:

http://www.baldyweb.com/wherecondition.htm

[ssanfu]

Also, you should use the click event of the "Edit Drawing" button of the "Edit a Drawing search" form, NOT the form load event of the "Edit a Drawing" form.

I always use a saved query or a SQL string for the form record source, never a table.

[decco21]

http://www.baldyweb.com/wherecondition.htm

Thanks again pbaldy! I gave it a shot but I'm a bit of a rookie so getting a little confused. Am I going to need it to look at the primary key value of the record from the table as this is the only unique identifier for the particular record (i.e. if I direct it at Job Number or Drawing Number it could be multiple records)? If so, do I need to add a control that stores the primary key value (ID in table Drawing List)?

I tried just implementing it looking at the Job Number with the following code, but I get a VBA error.

Private Sub EditButton_Click()    DoCmd.OpenForm "Edit a Drawing", , , "Job Number = " & Me.JobNumber
End Sub

I also tried this with the extra quotes in case VBA recognised it as text, but to no avail.

You have my database from a different thread if this helps

Also, you should use the click event of the "Edit Drawing" button of the "Edit a Drawing search" form, NOT the form load event of the "Edit a Drawing" form.

Thanks ssanfu, I think I used to have it like this but changed it a while ago, possibly because it wasn't working and I was clutching at straws :P

[pbaldy]

It is text, and the inadvisable space in the field name requires it be bracketed:

DoCmd.OpenForm "Edit a Drawing", , , "[Job Number] = '" & Me.JobNumber & "'"

You'll find in the long run that the spaces in your names are not worth the trouble.

[decco21]

Haha, I have already found that they aren't worth it, mate. Cheers for that.

It works well, but there's still the question of when there are multiple drawings for a single job number. If you select a drawing number, whilst you can cycle to that, I really would like it to focus initially on the record selected on the Edit a Drawing search form to avoid mistakes when users edit.

Is there a way to include multiple whereconditions?

[decco21]

Is there a way to include multiple whereconditions?

Took me a while to figure this out, but, quite simply, you just put an "and" in the code. However, this meant both comboboxes had to be filled in Edit a Drawing search for anything to appear in Edit a Drawing. To overcome this, I used the following code (for anyone who might find it helpful).

Thanks again Paul and Steve for all your help!

Private Sub EditButton_Click()


Dim strWhere As String
 
    If Len(Me.JobNumber & "") > 0 Then
    strWhere = "[Job Number]='" & Me.JobNumber & "' "
    End If
    
    If Len(Me.DrawingNumber & "") > 0 Then
    strWhere = "[Drawing Number]='" & Me.DrawingNumber & "' "
    End If
    
    If strWhere <> "" Then
        If Len(Me.DrawingNumber & "") <> 0 Then
            If Len(Me.JobNumber & "") <> 0 Then
                strWhere = "[Job Number]='" & Me.JobNumber & "' And  [Drawing Number]='" & Me.DrawingNumber & "'"
            Else
                strWhere = "[Drawing Number]='" & Me.DrawingNumber & "'"
            End If
        End If
    End If
    
    DoCmd.OpenForm "Edit a Drawing", , , strWhere
    DoCmd.Close acForm, "Edit a Drawing search", acSaveNo


End Sub

[pbaldy]

Happy to help!