im doing door access..

how to check several columns before updating the database?
i tried this INSERT INTO...
'                ssSQL = "insert into DailyAccess (Date_Out,Time_Out) values  (Date,Time) where DailyAccess(Military_Number,Date_Out,Door) =  (" & InputMarkas & ","",MARKAS)"

but it updates not at the latest.but it updates at the other row..

i have three conditions..
that are inputmarkas = the input, date out = zero, Door=markas. i want to update the date that the user exit at the markas' door..

i also tried this UPDATE..
                ssSQL = "Update DailyAccess Set (Date_Out,Time_Out) = (Date,Time) where Military_Number =  " & InputMarkas & " and Date_Out = "" And Door = MARKAS"

can someone help me?
thanks..

Hi,
Your SQL syntax is not correct. You only put the fields into a list with the INSERT statement. For UPDATE you show each field and value individually :-

ssSQL = "Update DailyAccess Set Date_Out = Date, Time_Out = Time WHERE ((Military_Number =  " & InputMarkas & ") AND (Date_Out = '') AND (Door = 'MARKAS'))"

NOTE:- The empty String for Date_Out needs to be 2 single quotes as 2 double quotes close and re-open your ssSQL String that you are constructing. Also, when matching a String data type (such as Door) the string needs to be enclosed by single quotes - even if you are using a variable EG:- if you had put the value MARKAS into a String Variable called strDoor then the last part of your WHERE statement would be constructed as : .... AND (Door = '" & strDoor & "'))"



____________________________
multi-tasking - the ability to hang more than one app. at the same time.

thanks geoffs..
i tried.
but it occurs another error..
that is "no value given for one or more required parameters."..
im getting stuck now..

one more thing, im doing a database in ms access..


[Edited by cempaka on 15-04-2009 at 09:27 AM GMT]

why dont these codes work well?

Private Sub cmdEXITMARKAS_Click()

Dim Adodc2 As ADODB.Recordset
Dim sSQL As String
Dim ssSQL As String

Set Adodc2 = New ADODB.Recordset
Dim InputPejabat As String
Dim response As String
Dim responseaction As String
  InputMarkas = InputBox("Please enter Your Military Number", "Verify Number", "")
If InputMarkas = "" Then
response = MsgBox("To continue, You need to enter Your Military Number." & vbCrLf & "Do You wish to proceed?", vbCritical + vbYesNo, "Error")
    If response = vbYes Then
        Call Login_Markas
    Else ' response = vbNo
        MsgBox "We are sorry You do not wish to proceed." & vbCrLf & "We wish You a good day", vbInformation + vbOKOnly, "End"
        PILIHPINTU.Hide
        INTRO.Show
    End If
Else
    sSQL = "SELECT * FROM DailyAccess WHERE Military_Number = " & InputMarkas & ""
    Adodc2.Open sSQL, dedb.Connection1, adOpenDynamic, adLockOptimistic
        If Adodc2.RecordCount > 0 Then
        
            If (Adodc2!Date_In <> "" And Adodc2!Door = "MARKAS") Then
                'displaying data
                Adodc2.Close
                PlaySound "D:\projectaku\acc granted.wav", ByVal 0&, SND_FILENAME Or SND_ASYNC
                MsgBox "YES.YOUR ACCESS IS GRANTED!"

                ssSQL = "UPDATE DailyAccess SET Date_Out = Date, Time_Out = Time WHERE ((Military_Number =  " & InputMarkas & ") AND (Date_Out = '') AND (Door = 'MARKAS'))"
                Adodc2.Open ssSQL, dedb.Connection1, adOpenDynamic, adLockOptimistic
                dedb.Connection1.Execute ssSQL
                Adodc2.Update

                
            Else
                PlaySound "D:\projectaku\acc deny.wav", ByVal 0&, SND_FILENAME Or SND_ASYNC
                MsgBox "SORRY. YOUR ACCESS IS DENIED"
                Call PromptPassword
            End If
        Else
                MsgBox "Sorry.You are not even entered!"
        End If
End If
End Sub

im dying over here..
please help me..
thanks.

Without knowing how you have set up your database I can only make a few suggestions.
I gather that your intention is to Log the last date and time that a person has passed through a door, and that to gain access they must input a unique number which you store in the database as "Military_Number", so I am assuming that this is a proper number and is being stored as a Long Integer. You appear to be storing this in a record with a "Door" Field which I presume is a String Data Type, and Date_Out and Time_Out Fields which I am assuming from your values in the UPDATE statement are both of a DateTime Data Type.
So, firstly, you retrieve all records that matches the "Military_Number" and correctly check that records have been returned, you then check for the "Door" in question, but you do not move your record pointer from the start of the recordset, and if there is more than 1 door that this "Military_Number" can access then you will need to Loop through all the returned records to find the door called "MARKAS". A better way would be to include the "Door" parameter in your original query :-
sSQL = "SELECT * FROM DailyAccess WHERE ((Military_Number = " & InputMarkas & ") AND (Door = 'MARKAS'))"
Then, if RecordCount is greater than Zero you have found the correct record, so:-

If Adodc2.RecordCount > 0 Then
'position the cursor on the first record
Adodc2.MoveFirst
'and because you have opened the Recordset as a Dynamic Recordset you can Update it direct
Adodc2("Date_Out") = Date
Adodc2("Time_Out") = Time
Adodc2.Update
End If

Just a couple of points on the Date and Time Fields
In your original code you are checking for a Date_In Field that <> "" - DateTime Data will never be equal to an empty String - it is either a valid Date or it is Null.
Since each passing through a door includes both a date and a time why not store this in 1 Field (DateTime_Out = Now) - this will make it easier to manipulate your date for reports etc.


Of course, the above code will only hold true if you are keeping just one entry for that "Door" and "Military_Number" combination. If you are recording a seperate entry for each time the "Military_Number" passes through that door then you will need to adjust the SQL Statement accordingly. So, if this is an Exit from a previously recorded Entrance then you will be looking for a record with an empty "Date_Out" Field :-

sSQL = "SELECT * FROM DailyAccess WHERE ((Military_Number = " & InputMarkas & ") AND (Door = 'MARKAS') AND (Date_Out = " & vbNull & "))"



____________________________
multi-tasking - the ability to hang more than one app. at the same time.

thanks geoff...i really appreciate your help..

DailyAccess is the table where i am gonna filter the report from according to the requested user..
what am i doing here now, is that, after entering MARKAS door, it will store the date_in and Time_in in the DailyAccess..
after that, if the user exit the MARKAS, it should store the date_out and Time_out at the same when the same person entered the door..

what im having now, is that it update the date_Out and Time_out at the previous enter.not the latest one. the latest one, it does not update.

the example:

militarynum        door       date in     time in   date out    time out
33333                markas   130409    1400       140409      1500
33333                markas   140409    1300        
33399                library     140409    1310


the second entrance for MARKAS should be 140409.but it updates at the first one.not the second one.
im trying it out..

update from me..
i tried this..

Dim Adodc2 As ADODB.Recordset
Dim sSQL As String

Set Adodc2 = New ADODB.Recordset
Dim InputPejabat As String
Dim response As String
Dim responseaction As String
  InputMarkas = InputBox("Please enter Your Military Number", "Verify Number", "")
If InputMarkas = "" Then
response = MsgBox("To continue, You need to enter Your Military Number." & vbCrLf & "Do You wish to proceed?", vbCritical + vbYesNo, "Error")
    If response = vbYes Then
        Call Login_Markas
    Else ' response = vbNo
        MsgBox "We are sorry You do not wish to proceed." & vbCrLf & "We wish You a good day", vbInformation + vbOKOnly, "End"
        PILIHPINTU.Hide
        INTRO.Show
    End If
Else
    sSQL = "SELECT * FROM DailyAccess WHERE ((Military_Number = " & InputMarkas & ") AND (Door = 'MARKAS') AND (Date_Out = " & vbNull & "))"
    Adodc2.Open sSQL, dedb.Connection1, adOpenDynamic, adLockOptimistic
        If Adodc2.RecordCount > 0 Then  
                PlaySound "D:\projectaku\acc granted.wav", ByVal 0&, SND_FILENAME Or SND_ASYNC
                MsgBox "YES.YOUR ACCESS IS GRANTED!"

                Adodc2.MoveFirst
                Adodc2("Date_Out") = Date
                Adodc2("Time_Out") = Time
                Adodc2.Update

but it resulted that "access denied"...
i had enter the MARKAS door...

when i try to debug the statement, the military num = the right num..date_out = 1, but Door does not give any value..

when i debug at   If Adodc2.RecordCount > 0 Then  ,
it shows 0, that means it does not find the record..
but when i check, the record is there..

[Edited by cempaka on 15-04-2009 at 01:53 PM GMT]

[Edited by cempaka on 15-04-2009 at 02:08 PM GMT]

OK - I suspect the updating the wrong record problem would not happen in a real life situation because technically it is not possible for someone to ENTER a door more than once without first making an EXIT, unless of course there is more than one way out. So when you run your query you could sort the resulting Recordset in Descending order of EntryDate so that by moving to the first Record you will be updating the last entry time. Alternatively, to keep things tidy, you could put the exit time against ALL open entries by looping through the recordset.
In addition, I would suggest that since the RecordCount Property is not supported by all cursor types use the BOF and EOF File positions to determine if you have any results. Also, you may be having trouble with the vbNull value so replace that with the "Is Null" statement. So :-

sSQL = "SELECT * FROM DailyAccess WHERE ((Military_Number = " & InputMarkas & ") AND (Door = 'MARKAS') AND (Date_Out Is Null)) ORDER BY Date_In DESC"
Adodc2.Open sSQL, dedb.Connection1, adOpenDynamic, adLockOptimistic
If Not (Adodc2.BOF And Adodc2.EOF) Then
Adodc2.MoveFirst
Do Until Adodc2.EOF
Adodc2("Date_Out") = Date
Adodc2("Time_Out") = Time
Adodc2.Update
Adodc2.MoveNext
Loop
End If


I would just make a couple of comments on database design.
Firstly, if your table does not have a PrimaryKey Column set to an AutoNumber DataType then you should create one. This will ensure that there are no problems with updating data and will help on sorting, searching, etc. Primary Keys are obvious with things like a database of Customers or Sales Orders, its CustomerID or OrderID, but in your case its not so obvious but still good practice - call it what you want, TransactionID, EntryID, or even RowID.
Secondly, your Doors should really be stored in a seperate table "Doors" with a PrimaryKey "DoorID" and the name of the door "Door", then in your DailyAccess table instead of storing the name of the Door thousands of times over (which makes for a very big and SLOW database in a few months time) change the Field to a Long Integer called "DoorID" and store the PrimaryKey of the Door from the Doors table. If you need to display the door name in a report derived from your DailyAccess table you just create a Query based on the 2 tables with an INNER JOIN on the DoorID.


____________________________
multi-tasking - the ability to hang more than one app. at the same time.

youre a genius...thank you so much geoffs..
it works well...
thousands of thank you to you...

and thanks for your suggestions...
at first, i thought about separating the doors into separate tables..
but i think, for the report, (im using data report), do i must make 3 connections if there are 3 tables?

No need to make 3 connections for the report. Just make a Query in your Access Database that joins the 3 tables and collects the information you want, then base the Report on that Query.



____________________________
multi-tasking - the ability to hang more than one app. at the same time.

i think its ok for me..
cos im not doing the real one..
im just doibng the system..

i think i should also check for the table before user can enter, rite?
to check whether he/she had entered the door or the other door or not..
rite?