Insert (or Remove) Blank Lines...

We have a Continuous Form that we enter want to enter, let's say, ingredients into but we want to be able to insert (or remove) a blank line between rows for hand written notes, reorder the rows putting all the dry ingredients together or maybe move them up or down to put them in the order in which they will be used.  On top of all of that you want to then enter the instructions for said group of ingredients separate from the instructions at the bottom for the overall recipe.

This is going to take a minute, so, get some coffee, read thru ALL the instructions and then come back to Step 1.  Just like cooking you want to make sure you have all the ingredients (pieces and parts) before starting.  No one likes to run to the store in the middle of making cookies!
The difficult I do immediately, the impossible takes a little bit longer.
Private Sub cmdInsertBlankLine_Click()
On Error GoTo SmartFormError
 
    Dim lngOldOrder As Long
 
        If Me.NewRecord Then
            Me.chkBlankLine = True
            DoCmd.RunCommand acCmdSaveRecord
            Me.txtOrder = Nz(DMax("iOrder", "tblIngredients", "[iRecipeID]=" & Me![txtRecipeID]), 0) + 1
        Else
            lngOldOrder = Me.txtOrder
            'Debug.Print lngOldOrder
            DoCmd.RunCommand acCmdRecordsGoToNew
            Me.chkBlankLine = True
            DoCmd.RunCommand acCmdSaveRecord
            Me.txtOrder = lngOldOrder
 
            DoCmd.RunCommand acCmdSaveRecord
            Call fReorganize
            Forms![frmRecipes]![sfrIngredients].Form.Requery
        End If
 
Exit_SmartFormError:
    Exit Sub
 
SmartFormError:
 
    If Err = 2046 Or Err = 2501 Then
        Resume Next
    Else
        MsgBox Err.Description
        Resume Exit_SmartFormError
    End If
 
End Sub
VBA
Tips (Main)
Home
Creating a Multi-Value field using Alphabet
Copy Fields Down from above Record
Loop thru records and OutPutTo seperate .RTF or .PDF
Modified Spell Check
Code Snippets
Lock\Unlock Bound Controls
Loop while renumbering two columns
Create a Table with Dynamic Field Names
Snippets for Exporting to Excel
Log Field Changes
Log Record Deletions
Check for Duplicate Values
ValidateData()
ClearClipboard()
Selecting an Excel Worksheet from Access
Send eMail to Multiple Recipients
Cancel Save in a Bound Form
Automatically Send eMail Notifications
fFindBookmark()
Looping Records to Send eMail
fxlFindReplace()
fMouseOverCurrent()
Check if Table Exists
fAmortization()
fHighlightRequiredControls()
Function fReorganize()
 
    Dim lngNewOrder As Long
 
            strSQL = "SELECT iRecipeID, iOrder " & _
                        "FROM tblIngredients " & _
                            "WHERE iRecipeID = " & Me.txtRecipeID & " " & _
                                "ORDER BY iOrder, iBlankLine DESC"
 
        Debug.Print strSQL
        Set rs = CurrentDb.OpenRecordset(strSQL)
 
        lngNewOrder = 1
 
        rs.MoveFirst
        Do Until rs.EOF
            rs.Edit
            rs!cdOrder = lngNewOrder
            lngNewOrder = lngNewOrder + 1
            rs.Update
            rs.MoveNext
        Loop
            rs.Close
 
        DoCmd.RunCommand acCmdSaveRecord
 
End Function
Go to your Subform and add the required fields from above to the Record Source and save.  Then go to your Main Form and Subform and the Controls listed below.
tblIngredients (Partial list of fields)
  iIngredientID Primary Key - Autonumber
  iRecipeID Foreign Key (linked to tblRecipes) - Number, Long
iOrder Number, Long Integer
  iRemove Yes/No, Default - False (Optional)
iBlankLine Yes/No, Defualt - False
iInstructionsImagePath Text, 100
Once you have your Controls on the Form it should look something like the image to the right.

To finish the set-up, create a subfolder in the directory where the Backend to your database resides and name it Images.  Put the below images (or ones of your choice) into the folder using the names assigned.  You can change the names but you will need to follow suit in the below code.
noteAdd16.png (Note Add New)
noteEdit16.png (Note Edit meaning there is already an entry)
Time add the code...
Now the Command Buttons...
cmdInsertBlankLine
cmdRemoveLine
cmdInstructions
Private Sub cmdRemoveLine_Click()
On Error GoTo cmdRemoveLine_Click_Err
 
    Dim intResponse As Integer
 
    If Me.NewRecord Then
        DoCmd.CancelEvent
    Else
        intResponse = MsgBox("You are about to DELETE this Line Item, " & vbCrLf & "are you sure?", vbYesNo + vbCritical + vbDefaultButton2, "Delete")
 
            If intResponse = vbYes Then
                DoCmd.SetWarnings False
                DoCmd.RunCommand acCmdSelectRecord
                DoCmd.RunCommand acCmdDeleteRecord
                DoCmd.SetWarnings True
            Else    ' User chose No.
                DoCmd.CancelEvent
            End If
    End If
 
cmdRemoveLine_Click_Exit:
    Exit Sub
 
cmdRemoveLine_Click_Err:
    MsgBox "This Line Item has not been deleted, see Database Administrator!", vbCritical, "Not Deleted"
    Resume cmdRemoveLine_Click_Exit
 
End Sub
Private Sub cmdInstructions_Click()
 
    If DCount("iIngredientID", "tblInstructions", "iIngredientID=" & [txtInstructionID] & " And iAreaID = " & 9) = 0 Then
        DoCmd.OpenForm "sfrInstructions", , , , acFormAdd
        Forms![sfrInstructions]![txtIngredientID] = Me.txtInstructionID
        Forms![sfrInstructions]![txtAreaID] = 9
        Forms![sfrInstructions].Caption = "Instructions for " & Nz(Me.cboIngredientID.Column(1), "Enter Instructions or Notes")
    Else
        DoCmd.OpenForm "sfrInstructions", , , "iIngredientID=" & [txtInstructionID] & " And iAreaID = " & 9
        Forms![sfrInstructions].Caption = "Instructions for " & Nz(Me.cboIngredientID.Column(1), "Enter Instructions or Notes")
    End If
 
End Sub
If you haven't created sfrInstructions as indicated above, you need to do that now.  To get the Note image to flip to noteEdit16.png, letting you know there is a note there, in the On_Close event of sfrInstructions pop-up.

Note: Change the Path of the Note images to where yours are located.
DoCmd.RunCommand acCmdSaveRecord
If Me.txtInstruction <> "" Then
    strSQL = "UPDATE tblIngregients " & _
                "SET iInstructionsImagePath = "Z:\Recipes\images\noteEdit16.png" " & _
                    "WHERE iIngredientID=" & Me.txtIngredientID & ""
              CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges
Else
    strSQL = "UPDATE tblIngredients " & _
                "SET iInstructionsImagePath = "Z:\Recipes\images\noteAdd16.png" " & _
                    "WHERE iIngredientID=" & Me.txtIngredientID & ""
              CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges
End If
First order of business is to set up tables and add the fields that will be needed to make this happen.  (Note, for the purposes of this example I will use tblIngredients which is linked to tblRecipes, not shown.)

Add the required fields, indicated by the purple asterisk, to your table.  Note, since you will adding to your existing table don't forget to make note of the Field Names used in your table.
tblInstructions (Create sfrInstructions from this table, pictured below)
iInstructionID Primary Key - Autonumber
iIngredientID Foreign Key (linked to tblIngredients) - Number, Long
iInstruction Memo
iAreaID Number, Long Integer (Optional)
iShow Yes/No, Default - True (Optional)
frmRecipes (Main Form)
cmdUp Command Button with Up Arrow
cmdDown Command Button with Down Arrow
lstSortDetail List Box, Visible = No
Row Source qrySortIngredients (SQL below)

SELECT tblIngredients.iIngredientD, tblIngredients.iRecipeID, tblIngredients.cdOrder
FROM tblIngredients
WHERE (((tblIngredients.iOrder)>0))
ORDER BY tblIngredients.iOrder;
sfrIngredients (Subform)
cmdRemoveLine Command Button with Minus Sign
cmdInsertBlankLine Command Button with Right Arrow
cmdInstructions

Opens Form sfrInstructions
Back Style = Transparent and set on top of
imgInstructionsImagePath

Make sure to *Bring to the Front* so it is on top of the
Image  Control imgInstructionsImagePath
imgInstructionsImagePath Image Control, Control Source = iInstructionsImagePath
txtOrder Text Box
chkBlankLine Checkbox, Visible = No
chkRemove Checkbox, Visible = No (Optional)

Use if you only want to Hide the removed line and not
delete it.
fReorganize()
This gets added to frmRecipes Form's Module, that's right, behind the Form not in a separate Module.
Click to send feedback...
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.