(Solved) – Skip empty fields when adding a new record using vba

(solved)-–-skip-empty-fields-when-adding-a-new-record-using-vba

I have a form that users open to add new fees and services for clients. There are 10 fields for fees and I have it setup to auto-populate the fee verbiage if the user un-checks a “standard hourly rate” check box.

If they un-click Std-Hourly, then the field [Fee001] will go from being greyed out and blank, to display “Services # 1 through # 3….” and the user can edit the text if they need it to show “Services # 1 through #7” (depending on how many services the client has)

When the user clicks the Save button, I have the data then saved to TblFee_ByClient table. The table has a record for each service ([ClientID] and [Service] are the only 2 fields in this table) but the form itself has 10 fields ([Fee001], [Fee002], Fee003] and so on) that the user can edit/generate.

All works except my code also adds records for the fields that the user clears/deletes so that I have a record with [ClientID] and no service listed. How do I add code to skip fields that are empty? Here is the vba so far and I am getting an error “Argument not optional” on the “Move.Next”:


Private Sub Toggle154_Click()

Dim TblFee_ByClient As DAO.Recordset
Set TblFee_ByClient = CurrentDb.OpenRecordset(“SELECT * FROM [TblFee_ByClient]”)

TblFee_ByClient.AddNew
    
TblFee_ByClient![ClientID] = Me.ClientID.Value
    If Me.Fee002.Value Is Not Null Then
TblFee_ByClient![Fee] = Me.Fee002.Value
    Else Move.Next
    End If
    
TblFee_ByClient![ClientID] = Me.ClientID.Value
TblFee_ByClient![Fee] = Me.Fee002.Value

TblFee_ByClient.Update
TblFee_ByClient.Close

Set TblFee_ByClient = Nothing

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *