Solved How to get a startup form to open with blank fields but once in the database it open the specific record? (1 Viewer)

Sampoline

Member
Local time
Today, 17:25
Joined
Oct 19, 2020
Messages
161
Sorry for the confusing title. Let me explain. I created an issue register type access database to have better communication between staff and technicians. I found a template that I have tweaked to better suit my requirements.

The startup form that the staff interact with looks like this , "Issue Details":

1607582351898.png


The form a technician views is the datasheet below, "Issue List":

1607582216143.png


When the ID number is clicked or the Title field is double-clicked, the initial startup form opens to the record pertinent to that ID field. So if I clicked ID 7, it will open the "Issue Details" form with the filled out fields. This is what I want.

But when the database opens, the "Issue Details" form is the startup form. So I don't want the fields to have any previous text filled (like shown in picture 2). I just want a blank form. But when I try to do this with:

Code:
DoCmd.OpenForm "Issue Details", , , NewRecord

It messes up. So when I click the ID 7 or 8 again in the form "Issue List", it opens a blank form.

How do I achieve the blank form on open, unless I click the ID field which will open the required row source in the form with the filled out text?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:25
Joined
Feb 28, 2001
Messages
27,001
It messes up.
Can you be just a little more specific? Define "messes up." What happens (or doesn't happen) that leads you to the conclusion that something is messed up?
 

Minty

AWF VIP
Local time
Today, 06:25
Joined
Jul 26, 2013
Messages
10,355
Try opening it filtered to a blank record

DoCmd.OpenForm "Issue Details", , , "ID = 0"
 

bastanu

AWF VIP
Local time
Yesterday, 23:25
Joined
Apr 13, 2010
Messages
1,401
Maybe look into using OpenArgs for the startup form. In its Open event check to see if the OpenArgs is empty and go to new record and if not empty (you passed the ID you want to go to) go to that record.
Something like this:

Code:
Dim nID as long , rst As Recordset

nID = Nz(Me.OpenArgs, 0)
If nID > 0 Then
    Set rst = Me.RecordsetClone
    rst.FindFirst "ID = " & nID
    If Not rst.NoMatch Then
       Me.Bookmark = rst.Bookmark
    End If
    rst.Close
Else
    DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End If
On the Issues List form you open the details like this:
DoCmd.OpenForm "Issue Details", acNormal, , , , , Me.ID
Cheers,
 

Sampoline

Member
Local time
Today, 17:25
Joined
Oct 19, 2020
Messages
161
Can you be just a little more specific? Define "messes up." What happens (or doesn't happen) that leads you to the conclusion that something is messed up?
Sorry that was vague on my part. I mean like the "Issue Details" form opens a blank record (when I click on the ID field in my "Issue List" form) instead of the record that pertains to that ID field. I only want the "Issue Details" form to be blank on startup. So I want the staff to see that form on open and be able to fill it in as new. But the technician needs to be able to use the "Issue List" form to click the ID number and it open the relevant record in that "Issue Details" form.
 

Sampoline

Member
Local time
Today, 17:25
Joined
Oct 19, 2020
Messages
161
Try opening it filtered to a blank record

DoCmd.OpenForm "Issue Details", , , "ID = 0"
Hi, I tried this but it doesn't fix the issue. Form is blank on startup (which I want) but remains blank when trying to open a filled-out form using the "Issue List" form.
 

Sampoline

Member
Local time
Today, 17:25
Joined
Oct 19, 2020
Messages
161
Maybe look into using OpenArgs for the startup form. In its Open event check to see if the OpenArgs is empty and go to new record and if not empty (you passed the ID you want to go to) go to that record.
Something like this:

Code:
Dim nID as long , rst As Recordset

nID = Nz(Me.OpenArgs, 0)
If nID > 0 Then
    Set rst = Me.RecordsetClone
    rst.FindFirst "ID = " & nID
    If Not rst.NoMatch Then
       Me.Bookmark = rst.Bookmark
    End If
    rst.Close
Else
    DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End If
On the Issues List form you open the details like this:
DoCmd.OpenForm "Issue Details", acNormal, , , , , Me.ID
Cheers,
So that first code, that's going into the open event of the "Issue Details" form and the 2nd code goes into the open event of the "Issue List" form? Have I interpreted that correctly, because my problem hasn't fixed?
 

bastanu

AWF VIP
Local time
Yesterday, 23:25
Joined
Apr 13, 2010
Messages
1,401
Yes, can you elabolarate what is happening = not fixed? Maybe you could try to upload a sample of your db (no sensitive data please) so we could see the problem.

Cheers,
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:25
Joined
Feb 19, 2002
Messages
42,981
When you want a form to work to both an add and an edit (as we normally do), you need a search feature on the form that uses unbound controls. Either combos or text boxes. Then the RecordSource for the form will be a query that uses the form's unbound control as criteria.

Select ... From .. Where SomeField = Forms!yourform!cboSomeField

Then in the AfterUpdate event of the unbound combo/textbox, use:

Me.Requery

The form will always open empty since the criteria field will be null.
 

bastanu

AWF VIP
Local time
Yesterday, 23:25
Joined
Apr 13, 2010
Messages
1,401
So that first code, that's going into the open event of the "Issue Details" form and the 2nd code goes into the open event of the "Issue List" form? Have I interpreted that correctly, because my problem hasn't fixed?

Yes to the first part and no to second, the Docmd.OpenForm line goes on the Issue List form to where you open the details (double-click of title and /or click the ID).

Cheers,
Vlad
 

Sampoline

Member
Local time
Today, 17:25
Joined
Oct 19, 2020
Messages
161
In my ID's on_click event I have:

Code:
Private Sub ID_Click()

    On Error Resume Next
    If (Form.Dirty) Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    DoCmd.OpenForm "Issue Details", acNormal, "", "[ID] = " & Nz(ID, 0), , acDialog
    If (IsNull(ID)) Then
        TempVars.Add "CurrentID", Nz(DMax("[ID]", Form.RecordSource), 0)
    End If
    DoCmd.SearchForRecord , "", acFirst, "[ID] = " & TempVars!CurrentID
    TempVars.Remove "CurrentID"
   
End Sub

So @bastanu when I add your code in, the open event of "Issue Details" and the click_event of the 'ID' in my "Issue List" form, it doesn't fix my problem. So what's happening again is, when I click the ID pertaining to a record in the "Issue List" form, it opens a blank form still. It doesn't open to the record.
 

Sampoline

Member
Local time
Today, 17:25
Joined
Oct 19, 2020
Messages
161
When you want a form to work to both an add and an edit (as we normally do), you need a search feature on the form that uses unbound controls. Either combos or text boxes. Then the RecordSource for the form will be a query that uses the form's unbound control as criteria.

Select ... From .. Where SomeField = Forms!yourform!cboSomeField

Then in the AfterUpdate event of the unbound combo/textbox, use:

Me.Requery

The form will always open empty since the criteria field will be null.
Hi Pat. So this ensures the form will open blank in general.

But what do I add in my 'ID' click for it to open to a specific record? In my above reply, I added the code I made for the ID_click, can you please see what needs to be done further. As it's still opening blank.. thanks.
 

Sampoline

Member
Local time
Today, 17:25
Joined
Oct 19, 2020
Messages
161
Here is the database template with some generic names. I haven't added any of the code that you guys have suggested. This is just fresh so you can play around with it. Thanks.
 

Attachments

  • Service_Desk_Issues_Register(Sampoline) - Copy.zip
    644.1 KB · Views: 215

bastanu

AWF VIP
Local time
Yesterday, 23:25
Joined
Apr 13, 2010
Messages
1,401
Please have a look at the attached file, might need to tweak some code as you seem to use some TempVars, but I think it does what your original post hinted as the desired outcome.

@Pat, that one liner is a bit of "fake news" 😊 as you would need a few more lines to add to the AfterUpdate event of the combo to do the search, add a control and modify the record source of the form and probably the biggest impact would be that in some cases the combo "search" could interfere with some custom search code (some people use filters, some change the recordsource, search as you type, etc.).

Cheers,
Vlad
 

Attachments

  • Service_Desk_Issues_Register(Sampoline)_Vlad.zip
    332.5 KB · Views: 201

Sampoline

Member
Local time
Today, 17:25
Joined
Oct 19, 2020
Messages
161
Please have a look at the attached file, might need to tweak some code as you seem to use some TempVars, but I think it does what your original post hinted as the desired outcome.

@Pat, that one liner is a bit of "fake news" 😊 as you would need a few more lines to add to the AfterUpdate event of the combo to do the search, add a control and modify the record source of the form and probably the biggest impact would be that in some cases the combo "search" could interfere with some custom search code (some people use filters, some change the recordsource, search as you type, etc.).

Cheers,
Vlad
Hi Vlad,

Thank you for your patience and advice. Your attached database worked for me! And with TempVars, I had most of my stuff as macros; so when I converted to code there were a bunch of TempVars everywhere. I'll fix it up.

Thanks,
Sam
 

bastanu

AWF VIP
Local time
Yesterday, 23:25
Joined
Apr 13, 2010
Messages
1,401
Hi Sam,

Glad to hear it worked for you, please post back if any piece of the code interferes with your existing functionality.

Cheers,
Vlad
 

Sampoline

Member
Local time
Today, 17:25
Joined
Oct 19, 2020
Messages
161
HI Vlad,

I'm having another issue on the same database. You may have noticed I had a form called "Login". The idea behind it was to restrict users who open the "Issue List" form using their user ID and access level. In the table "Contacts", there is two fields to be noted. One called 'Access Levels', which determines which users will be able to access the form based on their employment role/position and the other being 'WinID', which verifies their windows username.

1607923698451.png


In the "Login" form, the user ID uses a function to retrieve their windows login name.

1607923556626.png


The access level uses the table "Access Levels" table as it's row source

1607923844919.png


In my "Issue List" form, I have the following Form_Load event:

Code:
Private Sub Form_Load()

Dim AccessLevel

    AccessLevel = DLookup("[Access Levels]", "Contacts", "WinID=Forms!Login!UserID")
   
    If AccessLevel = 4 Or 5 Or 6 Then
    DoCmd.OpenForm "Login"
    DoCmd.Close acForm, "Login"
    Else
    MsgBox "You do not have access to open this form. Please contact your manager or team leader to gain permission to access it.", vbOKOnly, "Restricted"
    DoCmd.Close acForm, "Login"
    DoCmd.Close acForm, "Issue List"
    End If

End Sub

So as you can see, I want Access to restrict who can open the form. Only 4, 5 or 6 access level employee should be able to open the form.

But I keep receiving the following error on my DLookup when I attempt to open my "Issue List" form:

1607923459659.png


Any reason why? It can't find the form for some reason..

PS:
Code:
AccessLevel = DLookup("[Access Levels]", "Contacts", "[WinID]= " & Forms!Login!UserID)
doesn't work either..
 
Last edited:

bastanu

AWF VIP
Local time
Yesterday, 23:25
Joined
Apr 13, 2010
Messages
1,401
Hi Sam, many reasons ....

Looks like the code you show here is not quite the same to the one in the copy you uploaded by close.
Please review the update file and let me know if you need any clarifications.
DLookup("[Access Levels]", "Contacts", "WinID=Forms!Login!UserID") - you need to isolate you variables otherwise they will be interpretate as literal strings: DLookup("[Access Levels]", "Contacts", "WinID='" & Forms!Login!UserID &"'")
Also If AccessLevel = 4 Or 5 Or 6 Then I believe would not evaluate as you think, notice I've changed it to a Select Case (Case 4 to 6).

I don't see the need for the Login form if no maula interaction is needed.

Cheers,
Vlad
 

Attachments

  • Service_Desk_Issues_Register(Sampoline)_Vlad2.zip
    454.1 KB · Views: 207

Sampoline

Member
Local time
Today, 17:25
Joined
Oct 19, 2020
Messages
161
Hi Sam, many reasons ....

Looks like the code you show here is not quite the same to the one in the copy you uploaded by close.
Please review the update file and let me know if you need any clarifications.
DLookup("[Access Levels]", "Contacts", "WinID=Forms!Login!UserID") - you need to isolate you variables otherwise they will be interpretate as literal strings: DLookup("[Access Levels]", "Contacts", "WinID='" & Forms!Login!UserID &"'")
Also If AccessLevel = 4 Or 5 Or 6 Then I believe would not evaluate as you think, notice I've changed it to a Select Case (Case 4 to 6).

I don't see the need for the Login form if no maula interaction is needed.

Cheers,
Vlad
Hi Vlad,

Thankyou so much for your responses. I'll continue to work on this further tonight. I'll keep you updated if I'm stuck again.
 

Users who are viewing this thread

Top Bottom