Record Count before Opening Form

[spock1971]

Guys

I've searched the database and found similar problems but don't understand the replies (mainly DCount()).

I have a control form where a user can search the database by entering information. The form asks them to type some text that will appear in a description field, and match any records with the same text.

However, when the user types the text and clicks the button to open the form (which is linked to a query searching the description field), I want a message box to say no records found before opening the form. Mainly because when it doesn't find a matching record, the form opens with a new autonumber.

I either want a matching record to appear, or a dialog box saying no matching records found and the form not opening therefore not creating a number that is not used.

I tried in the command button entering if recordcount>0 then openform but it doesn't like the else statement or doing a if recordcount=0 underneath.

Any ideas?

 

[ghudson]

DCount() will allow you to count the number of records in a table. You can also set your criteria to narrow the DCount to your specifications. The Access VBA help files have more details and good examples on how to use the DCount() function.

 

[neileg]

I agree with ghudson. But I would add that your form will only open with a new autonumber if the Allow addittions property is set to yes.

 

[spock1971]

Cheers Guys

First of all - I looked at the DCount help file and don't understand the make-up. I tried DCount( QueryName, Field, >0) and it didn't recognise the queryname. secondly, is it only able to count table rows?

If I have a seperate form then for adding to the database - but the form in question is a search for an exissting record and amend the data held within that record - can I turn off this allow additions to solve the problem?


Cheers

 

[neileg]

OK. You don't apply DCount to a query, you use the same criteria in DCount as in your query. The expression will look something like this:
DCount([MyTextField],"tblMyTable",[MyTextField]=Forms!frmMyForm.txtMyTextBox)

 

[spock1971]

Will look at that cheers.