Access, Opening the Same Form in Different ways using Buttons

Question

I'm using Access 2007 at work and trying to build a database. I'd like to know if it's possible to have two different buttons on the "Main Menu" form that open the same "Data Entry" form. BUT, one button automatically goes to a new blank record for data entry, and the other button prompts the user to enter a specific ID # (tied to a field in the form) and then the form will open on that record. This would be for updating that specific record.

Is this possible? I am a beginner with VBA code. If this is possible with later versions of Access but not 2007, please let me know.

Answer 1

Create two buttons on the form. Let us name them as addRecBtn and openRecBtn let us say the form you are trying to open is tmpFrmName. So the first button will open the form in Data Entry Mode, the second one will open the form in normal edit mode.

The code should be something like,

Private Sub addRecBtn_Click()
    DoCmd.OpenForm "tmpFrmName", DataMode:=acFormAdd
End Sub

The second form will first have to get the number you are trying to open, for example let us call it the numberID. So your code would be.

Private Sub openRecBtn_Click()
    Dim numID As Long

    numID = InputBox("Please enter the ID : ")

    If DCount("*", "yourTable", "numberID = " & numID) <> 0 Then
        DoCmd.OpenForm "tmpFrmName", WhereCondition:="numberID = " & numID
    Else
        MsgBox "The numberID : " & numID & " does not exist, please try again !"
    End If
End Sub

Answer 2

You may be able to use DoCmd.OpenForm with different options to accomplish both your goals.

1. "one button automatically goes to a new blank record for data entry"

DoCmd.OpenForm FormName:="YourForm", View:=acNormal, DataMode:=acFormAdd

2. "the other button prompts the user to enter a specific ID # (tied to a field in the form) and then the form will open on that record"

Dim strWhere As String
strWhere = "[id]=" & Me.txtId
Debug.Print strWhere ' <- inspect this in the Immediate window
DoCmd.OpenForm FormName:="YourForm", View:=acNormal, WhereCondition=strWhere

The first form includes a text box named txtId, which contains the ID value you want to make current in the second form (YourForm).

Answer 3

Yes, it's possible.

My advice would be to do it with several functions - put the "shared" functionality in one (eg open the form), and then for the differences, make two functions which call the shared one

eg

function openForm()
{
    //Do open form stuff
}

function promptForID()
{
    openForm();
    //Do stuff that prompts for ID
}

function blankRecord()
{
    openForm();
    //Do stuff that sets up a blank record
}