DCount not finding records

[SteveJtoo]

My DCount statement is not finding a record that I know is there. I've spent hours trying different things but. . .

Can someone please help me.

Private Sub Transfer_WN_Books_Click()
Dim ST As String

ST = "H"
   
  If DCount("*", "Books", Author = " & Me!AuthorID & " And StatFlag = " & ST & ") > 0 Then
       DoCmd.Beep
 End If
End Sub

It never beeps! Thanks for anyones help.

 

[pbaldy]

Try

If DCount("*", "Books", "Author = " & Me!AuthorID & " And StatFlag = '" & ST & "'") > 0 Then

 

[SteveJtoo]

Sorry but still not working. ??

 

[pbaldy]

Are you sure the beep would be heard? I'd use a message box to be sure. What are the data types of the 2 fields in the criteria?

 

[SteveJtoo]

AuthorId is a number abd statflag is a string. Tried Message Box. no go. I know it's not working because it's actually in a If Then statement and the field is always set to the Then value.

 

[pbaldy]

Can you post the db here?

 

[SteveJtoo]

Can I send you an email explaining what I'm trying to do because I'm sure there is an easier and better way to do what I need.

 

[pbaldy]

It would be more appropriate to post it here. You may get more/better input as well.

 

[SteveJtoo]

Ok. Here goes.
I have a program that keeps records of books. There is an Authors table and a Books table (among others). There are 4 different status flags [Books].[StatFlag] that can be used. They are 'R' for books read, 'W' for books to read next (only one 'W' per Author, 'M' for more books (all books available for each author an 'P" for books that aren't seen.

I have a form (split screen) that lists all authors and all records with a 'W' called What's Next To Read (remember only 1 per author and I have a form that lists all the records with a 'M' called More Books (many books per author). My problem is that if there are no books flagged as 'W' the author does not show up on the list so I have made a blank record for each author with a 'H' status.

When I move a book from whats next to 'R' status then I look for the record with an 'H' status and change it to 'W' so it will show up in the What's Next form. When I move a book from More Books then I change the 'M' to 'W' and change the 'W' back to 'H'. These two steps are the problem I'm having. The code that I gave you never finds the record with an 'H'.

I could bypass all this if I could figure out a way to include all Authors in the list , even the ones without a 'W' record in Books. Then I would not have to have a blank record as a holding record. I can't figure out how to do this which is why I was trying to do it using holding records.

Phew! I hope this is understandable.

 

[SteveJtoo]

I guess writing all that down inspired me. I have solved my problem. If I have one blank record for each Author and make the queries using Nz(StatFlag) I can then set criteria as <>"R' etc. and eliminate all StatFlag codes I don't need but it will still show the blank one with the one I do need! No more trying to change statuses (is that a word) everytime I move a book.

Thank You Paul for your help. I AM a happy man!

 

[pbaldy]

Sorry for the delay, a lot going on. Glad you got it sorted out!