Query on event (1 Viewer)

[radek225]

On event "key press" I want to check that in my field "tblZlecenia.priorytet" there is value "7", if yes then Exit sub.
I know how to create query to get results with all rows with "7" in my table, but I don't know how to get it in event using vba.
My code:

Private Sub Lista0_KeyPress(KeyAscii As Integer) 
   Dim strSQL As String 
   Dim db As DAO.Database 
   If KeyAscii = 50 Then 
     strSQL = "UPDATE tblZlecenia SET Priorytet = " & "7" & "  WHERE ID_Zlecenia='" & Me.Lista0 & "'"   
     CurrentDb.Execute strSQL   
     KeyAscii=0
   End If  
End Sub

 

[SmallTime]

Hi

Do you mean you want to UPDATE (Change) Priorytet in tblZlecenia to 7 for whatever record is selected in your Lista0

Or

Do you you want to Just check if the selected record in your Lista0 is ALDREADY 7

Perhaps it might be better if you explain what your're trying to achieve

Take care
Smalltime

 

[radek225]

I want to update "Priorytet" in "tblZlecenia" to 7, but only if there is no "7" in any "Priorytet" in "tblZlecenia". If already some record has value "7" then exit sub.

 

[SmallTime]

Assuming that the list box is bound to Priorytet

what about a simple

If Me.Lista0 <> 7 THEN
so something - Like run your update query
END IF

Also if you're always changing tblZlecenia.priorytet to 7 you don't need " & "7" & " - a 7 without the ampersands and quotes will do just fine. However, I suspect your needs might be a little more then stated. Are you sure you only need it changed to 7 and wont be needing to change it to something else in the future.

 

[SmallTime]

Sorry I cut my reply off a little short- got called away

I meant top point out that this isn't a very good method as people have a habit of clicking a list box and in this case it'll change your recode every time it's clicked without warning. Of you must do it this way then perhaps a message box before the change is executed would help and also give a way to back out if it's a mistake

 

[radek225]

Thanks for insight.
First of all "Priorytet" isn't bound field. I need information not about selected item, but from all of data on the list (where "7" could be in "priorytet" column).
Yes there will be variable not "7" (in the future), now I gave some example with "7"
I don't need message box before because only one person will be responsible for changing this data. There should be fast access. It's about priority to do some task.

 

[SmallTime]

Couple of ways to do this, but without knowing much more what about

If DLookup("[Prioryte]", "tblZlecenia ", "[ID_Zlecenia]=" & Me.Lista0) <> 7 Then
Msgbox "This is Not 7"
END IF

of course you can replace
Msgbox "This is Not 7"
With your update code

try it on the AfterUpdate Event of the listbox Lista0

 

[SmallTime]

Sorry missed the bit about all items in the list box.

Isn't ID_Zlecenia unique?

 

[radek225]

Id_zlecenia is unique

 

[SmallTime]

I need information not about selected item, but from all of data on the list

The easiest way is to do this is through a query otherwise you'll have to iterate through the rows in the listbox (I suspect much of the SQL for the query is already held in your Listbox) and a query will help you better visulise your data.

Create the query first in the Query Designer so can preview the result to make sure they're what you expect. Then when you're satisfied with the correct result you can copy the sql and use it in VBA as you already have done.


On the other hand

I want to update "Priorytet" in "tblZlecenia" to 7, but only if there is no "7" in any "Priorytet" in "tblZlecenia". If already some record has value "7" then exit sub

If your trying to update records based on a selection from the listbox, which is I thought you were trying to do from reading

 strSQL = "UPDATE tblZlecenia SET Priorytet = " & "7" & "  WHERE ID_Zlecenia='" & Me.Lista0 & "'"

and

I want to update "Priorytet" in "tblZlecenia" to 7, but only if there is no "7" in any "Priorytet" in "tblZlecenia". If already some record has value "7" then exit sub.

Then

If Nz(DCount("[Prioryte]", "tblZlecenia", "[ID_Zlecenia]=" & Me.Lista0 & " And [Prioryte] = 7"),0) > 0 Then
Exit Sub
else
'Excute code'
End If

This will count the number of Prioryte that are =7 in the table tblZlecenia WHERE ID_Zlecenia = a selected item in your list and if the count is 0 will execute code.

Sorry made some edits

 

[radek225]

I need check all data from list not only selected item so I had to change the code little bit

If Nz(DCount("[Priorytet]", "tblZlecenia", "[Priorytet]=" & pozycja), 0) > 0 Then

Now it's working. You really helped me SmallTime, Thank you for solving my problem

 

[SmallTime]

Really glad it worked out for you

Take care