Updating table fields using form

[Vivirtruvian]

Hi all,

I've been staring at my screen for hours trying to find a similar situation/solution to the issue I am facing, which has two criteria:

1. Form will be editing existing fields in a table that are blank (not creating new lines of data)
2. Form needs the fields to remain pre-filled with the same data until the form is closed and re-opened.

As a complete novice, I have managed to achieve one or the other, but cannot get a simple solution to both :banghead:

Context:

tblProtein: contains information of stock entering the warehouse which has the following fields:
- Date In
- Dkt No
- Material No
- Description
- SSCC ** (ie. pallet number)
- Batch Code
- Site *
- Section *
- Row*
- P *

* denotes that data for this field is currently empty
** denotes that this is the parameter for which I need to edit the Site/Section/Row/P data for

I have a form (frmPutaway) that allows me to search for a pallet by SSCC and bring up fields to edit each of the respective Site/Section/Row/P fields.

Private Sub txtGoTo_AfterUpdate()
    If (txtGoTo & vbNullString) = vbNullString Then Exit Sub
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    rs.FindFirst "[SSCC]=""" & txtGoTo & """"
    If rs.NoMatch Then
        MsgBox "Sorry, no such record '" & txtGoTo & "' was found.", _
               vbOKOnly + vbInformation
    Else
        Me.Recordset.Bookmark = rs.Bookmark
    End If
    rs.Close
    txtGoTo = Null
End Sub

However, I need this form to function so that an operator enters Site/Section/Row information once, and these values are carried over when the next SSCC is called up. I have done this using the following:

Private Sub txtRow_AfterUpdate()
Me.txtRow.DefaultValue = """" & Me.txtRow.Value & """"

End Sub

Private Sub txtSection_AfterUpdate()
Me.txtSection.DefaultValue = """" & Me.txtSection.Value & """"

End Sub

Private Sub txtSite_AfterUpdate()
Me.txtSite.DefaultValue = """" & Me.txtSite.Value & """"

I have txtP as a bound text box, because this will be an individual value for each line; the other text boxes I wish to type once to insert in bulk.

How can I get these fields to function as per the above code, but also send these fields to the respective fields in tblProtein in relation to the SSCC number I have called up?

Help is always appreciated

 

[Minty]

Default values only apply to to new records. The records you retrieve by searching are existing records, and therefore even if they have no values set, because they are already saved, the default values you are creating won't get applied.

TBH I'm not sure of an elegant way of doing this without adding some unbound controls with the values you want to use and a command button to apply them. I would be hesitant to do it all completely automatically as if someone makes a mistake they wouldn't have an easy "escape route".

 

[Vivirtruvian]

Thanks, Minty.

I have created a bit of a work-around using an update query that triggers upon losing focus on the unbound boxes. It's a little unpolished but seems to be working for a starting point. The operators have a chance to check all entries before closing the form at this point.

Thank you for the feedback