Access 2010 Instant Messenger

[Pointman]

I am trying to create an instant messenger tool within my database,

I have created a table with the following
fields:
from (text)
to (text)
message (memo)
test right now I am just typing in yes or no

I have created an event procedure on timer (main form) so that if a record
exists in the messages table where to = reviewernumber and [test] = "no"
a form will open displaying the message.

I am new to this and I have not gotten the procedure right. If a message exists
where I am not the desired recipient, the form still opens.
Could anyone tell me where I am going wrong?

Private Sub Form_Timer()
If (DLookup("[to]", "messages", "[to] = [ReviewerNumber]")) And DLookup("[test]", "messages", "[test]") = "no" Then
DoCmd.OpenForm "message_popup"
End If
End Sub

I have this running after the user logs into a form and on the form it keep the reviewnumber in a field called ReviewerNumber and in the message table it has the To field. Basically it opens any message and doesn't seem to compare the to = reviewernumber.

Not sure if this helps but the to and reviewernumber fields are text field but have a number in the field. Example is the to field would be 245 and the reviewernumber could be 245. If the to and reviewernumber field are the same then open the form.

[Rabbit]

That's because you're using two DLookups. They are not linked. Get rid of the second lookup and look up test in the first one instead of to.

[Seth Schrock]

Try something like this:

If DCount('*', "Messages", "To = '" & ReviewerNumber & "' and Test = 'no') > 0 Then
    DoCmd.OpenForm "message_popup"
End if

You actually had a couple of errors in your code. First was a syntax error in the "[to] = [ReviewerNumber]" part. You need the ReviewerNumber outside the quotes so that it sees it as a field name that has a value instead of searching for the text "ReviewerNumber " (which would need single quotes around it anyway since it is a string value). The second error was in logic. By separating your two criteria into separate DLookup functions Access doesn't know that you are looking for a record that has both a TO value of 245 and a TEST value of "no". It just looks for a record with a TO value of 245 and then searches for another record that has a TEST value of "no".

Just a suggestion, as it stands right now, as long as you have a message that was sent to you in the table, the form will open even if you have already read the message. You might want to add a field that tells you if you have read the message and then add that field to your criteria so that the form only opens if you have an unread message.

[Pointman]

Seth, Let me first start out by saying I am a network admin and have no real experience...le t me change that to no experience in access or writing code. I am just trying my best to understand everything.

What I have is a field called test and if test is no then it will open the form if [to] and [reviewernumber] are the same.

I tried your code but I am receiving a syntex error.

I might not have explain it well but I also think it can't find the reviewernumber on the form because I am not telling it to look there?
[Forms]![00 StartUp]![reviewernumber]

I am just assuming it should be able to find it because it is lauching from the form.

Sorry that I am a complete noob but I am extremly thankful for all your help

[Seth Schrock]

If the ReviewerNumber is on the same form that your timer is running on, then you can just reference it using Me.ReviewerNumb er. If if is on a different form, then you most definitely have to reference it using the method you described.

Also, when you get the error and click OK, then it should highlight the problem. What is it highlighting?

No problem being a noob. It wasn't so long ago that I was in your same shoes and there is still plenty that I don't know. If there is something that you don't understand, just ask for clarification.

[Pointman]

If DCount('*', "Messages", "To = '" & Number & "' and Test = 'no') > 0 Then

It doesn't like the first ' that I underlined

If DCount('*', "Messages", "To = '" & Number & "' and Test = 'no') > 0 Then

and yes number is being launched from the same form

[Pointman]

I have a question....Doe s anyone have a better way of implementing instant messaging where someone logs in and they use the login number to pull instant messages from another table for that login number.

Because I think I got it to work but lol now it just opens a blank form with no information.

So yes if to = number then open form. So lol to = number but it doesn't know to open the form with that record.

I am so lost here.

[zmbd]

Pointman: Seth, Let me first start out by saying I am a network admin and have no real experience...le t me change that to no experience in access or writing code. I am just trying my best to understand everything.

If you will follow thru this tutorial MS Access 2010 Tutorials it will help tremendously with your overall understanding of the what and how of Access.

This will help keep you out of trouble should you need to upgrade Access 2007 reserved words and symbols and
AllenBrowne- Problem names and reserved words in Access

As you get a little more advanced in programing having this will be valuable in understanding why somethings happen when they do: Order of events for database objects (v2010)

and as you get time read thru the articles here (but read thru the titles, you'll find a lot of useful stuff here):Microsoft Access / VBA Insights Sitemap >> Some of these are quite dense. The trouble shooting articles are worth the time to read and will save you a headache or two.

Still looking for a good, free, online Access VBA Tutorials; however, if you follow the one above, it at least touches on things.
Oh, and look here for some other suggestions for trouble shooting > Before Posting (VBA or SQL) Code