Disabling additions in a form (1 Viewer)

[MiniD011]

Hi Guys,

I have a form which acts as a search function for users looking to amend existing records. This is based on a query, where users enter a parameter (PolicyNumber) and this returns only the records which match the PolicyNumber in a form view. I have also added basic navigation buttons to move between all records with the same policy number. I have a problem in that I can't disable the option to add a new record in this form once people navigate to the end of the records.

The user clicks a 'search' button, manually inputs the policy number, and the records are returned in form view. The code for the button is as follows:

DoCmd.OpenForm "frmAdjustmentsEdit", acNormal, "qryPolicyNumberSearchWriteOff", "", acEdit, acNormal

Does anyone know how I can disable the addition of records in this? Also as a sidenote if no records are found I would like it to display a MsgBox, but despite trawling the internet have been unable to find a solution I could get to work.

Thanks in advance for any help!

 

[pr2-eugin]

Before opening the Form try using a DCount for the policy number.. If it is not 0 then open else display the error message.. Something along the lines of..

Dim countOfPolicy As Integer
countOfPolicy = DCount("*", "[COLOR=Blue]theTableName[/COLOR]", "[COLOR=Blue]policyNumberFieldName[/COLOR] = " & Me.[COLOR=Blue]textControlName[/COLOR])
If countOfPolicy <> 0 Then
    [COLOR=Green]'your DoCmd[/COLOR]
Else
    [COLOR=Green]'MsgBox("Sorry No Records")[/COLOR]
End If

Change all blue bits to match your design.. The above DCount is written in a general idea that Policy Number is a Number if it is a String the you should enclose them in single quotes..

Also on a Side note.. DCount can also be used on Queries..

 

[MiniD011]

Hi Paul, thanks again for being on hand.

I have tried this and wasn't sure whether to set this up based on the query or the table as the form is based on a query.

I have tried both, neither of which proved successful. For the table I have the following code:

Private Sub SearchWriteOff_Click()
Dim countOfPolicy As Integer
countOfPolicy = DCount("*", "tblAdjustments", "PolicyNumber = " & Me.PolicyNumber)
If countOfPolicy <> 0 Then
    DoCmd.OpenForm "frmAdjustmentsEdit", acNormal, "qryPolicyNumberSearchWriteOff", "", acEdit, acNormal
Else
    MsgBox ("No records found under that policy number")
End If
End Sub

This returns 'Method or Data Member not found.' in reference to '& Me.PolicyNumber'. And I am not sure what to do now. Sorry for being such a pain, and thanks again for the help!

 

[pr2-eugin]

So where do you enter the PolicyNumber? Is it on a Search Form? On which the button sits?

 

[MiniD011]

Essentially the form is based on a query which applies the filter. The SQL code for this is:
SELECT tblAdjustments.ID
FROM tblAdjustments
WHERE (((tblAdjustments.PolicyNumber)=[Enter Policy Number]));

That way when you click the button to search, you are prompted to enter the policy number, which then takes you to the appropriate record in a form view. I hope that makes a little more sense, it is just a basic parameter query to search.

Thanks again, also I managed to disable the addition of additional records by adding a simple line to my code so at least progress is being made, albeit slowly!

 

[pr2-eugin]

So technically it is a Parameter Query.. which cannot be easily passed.. You need to create a Text box control on the Form where the button resides.. So you can rewrite your query as..

SELECT tblAdjustments.ID
FROM tblAdjustments
WHERE (((tblAdjustments.PolicyNumber)=Forms![yourFormName]![theTextBoxName]));

This will take the value from the Form and filter your record based on the value you are passing..

 

[MiniD011]

Ok I have done that and got absolutely everything working without a hitch! Now all I need to do is spend 30 minutes testing it before handing it to the person I was creating it for!

Thanks again for all of your help Paul, on this and my previous query. I couldn't have done it without you!

 

[pr2-eugin]

Happy to Help Mini.. Someday you will do the same.. Good luck..