Need a Form to open without creating a new record

[Miked13]

Hello,

Working in Access(2007-2013).

Form opens to a record set from a query, form contains a button to pull/populate the form with a preexisting record and stamp with User's name(similar to an IT ticket system). Form works fine as long as the user has at least one record assigned to them.

Problem arises when a User with NO records assigned opens the form. Since the form has no records it creates its own and because all fields are required to be filled, it will not allow the user to do anything.

If there are no records i need the form to open without creating a new record. Please help!

Already tried:

Set Data Entry to NO and form is completely blank(solid white).
Created a message box to open when no records(used Dcount).

Any suggestions will be a big help(still learning)

 

[CJ_London]

You could try using the dcount function to see if there are any records for that user prior to opening the form. But not clear to me what you would do with that information

 

[Miked13]

System is to work like an IT Case assignment form. Essentially we have other departments request Inventory counts of a particular part. Our counters would log into the form and click a button to "activate" a count, by tagging the counters name. Then the forms record set would show the activated count. Problem is i cant get the form to open to a blank query without the system creating a new record. This is bad because the form doesn't contain all of the fields for them to create a new record. If this helps.

 

[CJ_London]

If this helps.

Not really.

And what is the point of opening a blank query?

 

[Miked13]

True.
So i should run the Query and DCount prior to the opening of the Form, and if the count is 0 then run code to import a record then refresh?

If so at what point in the code would i run the Query, Dcount and IF statement?

Sorry for the basic questions.

here is the code for the main form.

Option Compare Database
Option Explicit

Private Sub Combo84_AfterUpdate()
    Me.cboIssue.Requery
End Sub

Private Sub btnActCount_Click()
'Changing Control Source from qryInitialIVR to qryIVR
    Me.RecordSource = "qryIVR"
'Logs Counter and changes status to Open
        DoCmd.OpenForm "frmOpenCount", acFormDS, "", "", acEdit, acNormal
        DoCmd.GoToRecord acForm, "frmOpenCount", acFirst, 0
        Forms!frmOpenCount!txtStatus.Value = "Open"
        Forms!frmOpenCount!txtCounter.Value = Environ("Username")
        DoCmd.Close acForm, "frmOpenCount", acSaveYes
'Logs time and changes status to Pending
            DoCmd.OpenForm "qryActivateCount", acFormDS, "", "", acEdit, acNormal
            DoCmd.GoToRecord acForm, "qryActivateCount", acFirst, 0
            Forms!qryActivatecount!txtStatus.Value = "Pending"
            Forms!qryActivatecount!txtDateStart.Value = Now()
            DoCmd.Close acForm, "qryActivateCount", acSaveYes
'Refreshes List268
    Me.List268.Requery
End Sub

Private Sub btnRefresh_Click()
'Works
    Me.List268.Requery
End Sub

Private Sub btnSubmit_Click()
'Works
    Me.txtStatus.Value = "Counted"
    Forms!frmIVR.frmCount.Form.txtDateCount.Value = Now()
    DoCmd.Requery
    Me.Refresh
End Sub


Private Sub Form_Load()
    Me.txtCounter.Value = Environ("Username")
    Me.List268.Requery
End Sub

 

[CJ_London]

Please use the code tags when posting code to preserve the indentation, otherwise code is very difficult to read. Code tags can be found in the advanced editor. Highlight the code and click the # button

you should just need the dcount and run before opening the form.

 

[Miked13]

Oops. Ill try it and let you know. Thanks for the help!

 

[Miked13]

Thanks for the help CJ! I added the IF DCount statement in the login button, works fine now.