Access Diva Insert Blank Lines

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

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.