Problem with Elseif statment

[TomKat]

I have an ElseIf statement. If the field is left blank when the tech hits the save/update button. It prompts them that the field is empty and to fill it in, then takes them to that field.

Problem is that if the field is filled in, it still pops up the prompt and will not allow the tech to save/update. How to fix this. I need it to prompt the tech based on two field values if they are true.

Fields: DateIn
Type (within type, it must be either Desktop or Laptop). There are
two more values for Type but only Desktop or Laptop must be
true along with the DateIn value.
If either one of the other two values are true and the DateIn
value equals "Date". Then I don't want the msg.

Here is my ElseIf statement:

ElseIf IsNull(Me.Prior_User) And Me.DateIn And Me.Type = "Laptop" Or Me.Type = "Desktop" Then
MsgBox "OOPS! You forgot to enter the prior user. If you do not have or know it, enter 'unknown'."
DoCmd.GoToControl "Prior User"

If I leave out the me.type or use just one or the other. It works fine.
I think having both the me.types are killing it.

You all have been great in helping me fix other issues. Hope you can help with this one.

 

[pbaldy]

When you have a mix of AND & OR, you should use parentheses to make sure they are evaluated according to your desired logic. Without them, you'll get unexpected results.

 

[TomKat]

Ok, how do I use the parantheses?

Current way: Me.Type = "Laptop" Or Me.Type = "Desktop"
Correct way?: Me.Type = "(Laptop, Desktop)"

 

[pbaldy]

Try

ElseIf IsNull(Me.Prior_User) And Me.DateIn And (Me.Type = "Laptop" Or Me.Type = "Desktop") Then

 

[TomKat]

Ok, I see now. If you have the same field but need to list it with two different types of possiblities. Then enclose those in parantheses.

Thanks... I will try that when I get back to work tomorrow.

 

[TomKat]

Ok, next problem on this is that this form is a continuous form. Right now, it will not go to the next record to check for the missing or empty fields. It only runs on whichever record in the form the cursor is on. I would like this to go to each record and check for what is in the If statements until it reaches the last record in the continuous form. I am stumped and my thinking is using a findnext or findrecord but I don't think that is the right path. Here is my VB....

Private Sub cmdUpdate_Enter()
If IsNull(Me.SRMSTicket) Then
MsgBox "OOPS! You forget to enter the associated ticket number. If you do not have or know it, enter '11111111'."
DoCmd.GoToControl "SRMSTicket"
ElseIf IsNull(Me.Prior_User) And Me.DateIn = Date And (Me.Type = "Desktop" Or Me.Type = "Laptop") Then
MsgBox "OOPS! You forgot to enter the prior user. If you do not have or know it, enter 'unknown'."
DoCmd.GoToControl "Prior User"
'ElseIf Me.Type = "Desktop" Or Me.Type = "Laptop" And Me.DateIn = Date Then
' MsgBox "You are checking in a Desktop or Laptop. " & (Chr(13)) & (Chr(13)) & " Enter the prior user before saving!."
' DoCmd.GoToControl "Prior User"
ElseIf IsNull(Me.EmployeeID) And Me.DateOut = Date Then
MsgBox "You are checking out equipment, please assign it to a user."
DoCmd.GoToControl "EmployeeID"
Else
Exit Sub
End If
End Sub

I want to go to the next record, run back through the IF and keep going on to each record until the above criteria's have been met. Then allow the tech to hit the button to save/update the information in the form.

 

[boblarson]

The problem you face is that the second you move to another row (record), the data is already saved.

 

[TomKat]

The data is in a temp table. Once the techs update the information and hit the save/update button. It updates the main tables and clears the temp table.

The whole idea is when they hit the button, it should cycle through each record and verify the necessary data is filled in. If it is missing, tell the tech. Problem I have, is it only checks the record the cursor is in and skips the rest. If the other records are still missing data, it will saves/updates the information. Right now it only checks one record without checking the other records. I would like it to go through each record somehow, starting with the 1st record, check and if OK, then go to the next and check, if OK, go to the next, and so on.

Is there was another way to cycle through the records somehow and inform the tech the information is missing vice using the button?

The button should only cycle through each record and check and if OK, then save/update. If it finds missing data, then go to that records field and cancel the save/update event.

Is there a way to write this event on the main form itself vice using the button?

 

[pbaldy]

Rather than work on the form, I'd work directly on the temp table with a recordset:

  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  Set rs = db.OpenRecordset("TempTableName", dbOpenDynaset)

  Do While Not rs.EOF
    'your tests here
    rs.MoveNext
  Loop

  set rs = nothing
  set db = nothing

 

[TomKat]

How do I toss that into working directly with the Temp table?

is that tossed in some event on the main form?

 

[lala]

am i missing something? what's the me.datein doing? there's no criteria for it
or do i not know something?
as far as i know, you can't say me.datein and me.dateout <>date()

just as an example

it has to be

me.datein <>date() and me.dateout <>date()

am i wrong?

 

[TomKat]

DateIn and DateOut are fields. If the date in "DateIn" or "DateOut" field is equal to today (Date). Then do whatever else is stated. That is the only way I saw that works or told is to use the "=".

 

[lala]

that's not what i'm talking about, there's no criteria next to me.datein

 

[TomKat]

IsNull(Me.Prior_User) And Me.DateIn = Date And (Me.Type = "Desktop" Or Me.Type = "Laptop")
Criteria: if the field "Prior User" is empty and the field "DateIn" equals today, then notify the tech.

IsNull(Me.EmployeeID) And Me.DateOut = Date
Criteria: If the field "EmployeeID" is empty and the field "DateOut" equals today, then notify the tech.

 

[Brianwarnock]

There is nothing wrong with those but until post 6 I think there were a few dodgy ones eg

ElseIf IsNull(Me.Prior_User) And Me.DateIn And (Me.Type = "Laptop" Or Me.Type = "Desktop") Then

Brian

 

[TomKat]

Oh, I just put the one line that contained that data vice the whole If statement.

I still struggling to get this to loop through each record to verify each field is validated based on the IF statements, if not then cancel the update and return to the form. If all is good, then update the data.

 

[TomKat]

That's fine, if it moves to the next row and saves what is in that row, that is perfect. Then when they hit the button, it will update the main tables which is exactly what I want them to do.

 

[TomKat]

Brian, I see it now, the me.datein should have the "Date" there... must have been left out.