How to move name/date/time to a log 1

[CactysJack]

1. I have a form containing the field NAME. It is part of a gated community entry control program.

2. I want to double click (or hit a special command button)on NAME and have the NAME, LOT# and ARRIVED (date/time) entered into a record in a table called PERMGSTLOG which contains these three fields. Each new record would be appended.

3. We make the .mdb file READ ONLY to prevent accidental corruption of data by the user (of a replicate). Assuming we could do 1 and 2 above, would it be rendered unusable by the READ ONLY mode or would we have to put up with
accidental corruption by removing the READ ONLY attribute?

4. I am a neophyte Access programmer and can't find the solution in my books, any help on this will be appreciated.

 

[JeremyNYC]

You won't be able to change data in the mdb if you make it read only. Back up just like you vote: early and often.

Inserting the records shouldn't be a problem. Call a function that's something like this from whatever event you want to use (put this in a common module):

Function NewEntry()
Call DoCmd.OpenForm("frmEntry", , , , acFormAdd)
'Then set controls on FrmEntry (or whatever) to the values in controls on the form calling this function.
End Function

Also, you should be using an ID number of some sort to identify entities like people and Lots, not their names. otherwise you leave yourself vulnerable to typos, etc. This may mean using combo boxes or more complex queries on your forms, but it will make you much happier when you're trying to manage your data.

If you want more help on how to set the controls, post back with names of your forms and controls and the fields behind the controls.

Hope this gets you started on the right path.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[CactysJack]

Thanks Jeremy,

While I don't fully understand your reply, it does tell me there is a way if I can work it through.

You didn't mention double click vs. command button, is either possible?

Per your suggestion here are the form names etc. Controls, I am too new to know what I have or don't have. Maybe I don't have any.

The form I want to operate on is called "WEXFORD". It has all resident data including a subform called "PERMGST" which shows "LOT#" and "NAME". The guest "NAME" is what I want to focus on with a double click or command button which will append the "LOT#", "NAME" and "ARRIVAL" (date/time)record to a table called "PERMGSTLOG" with the three fields.

The permanent guest table is also called "PERMGST".

I hope this makes sense.

 

[JeremyNYC]

Yes, you will be able to do this from either a double-click or a command button.

The stuff about your tables and forms is a little confusing to me. What are the names of the controls on the second form that you need updated? And what are the names of the controls on the first form from which you'll get the data?

One tip: Change "Name" to something else. "Name" is an access reserved word, and using it as the name of a field or table is risky, as somewhere along the lines it will cause you problems.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[CactysJack]

Jeremy-

In studying the problem I note in the event tab for NAME it gives a double click possibility. What I apparently need is the code/macro/expression to do it.

John

 

[CactysJack]

The control on the form I want to double click is a text box for the label NAME (which I'll change per your recommendaton). Where I want to update the entry list is a table called PERMGSTLOG with the fields LOT#, NAME and ARRIVAL).

The purpose is to let the guard double click the name of the permanent guest on the PERMGST subform2 and have it aded to a log table PERMGSTLOG where each subsequent record will be a LOT#, NAME and ARRIVAL (time/date).

I hope this makes sense. (My last post crossed yours)

John

 

[JeremyNYC]

OK, so now it looks like you want to add a record to the subform, populating some of the controls to match some controls on the main form.

Try this. You may have to rename things to get it to work, but I think this will do what you need.

Private Sub Command5_Click()
Me!PermGst.SetFocus
Call DoCmd.GoToRecord(, , acNewRec)
Me!PermGst.Form!Lot# = Me!Lot#
Me!PermGst.Form!Name = Me!Name
End Sub
=============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[CactysJack]

Thanks Jeremy, I'll work through this now. I don't see the expression to add the date/time, is it there somewhere. Based on my limited knowledge, I was looking for a Now() or the like.

While we were working on this, I got a fix to another problem on pagination of a report. Fine forum and a lifesaver.

John

 

[SBendBuckeye]

Hey CactysJack,

Here are a couple things to get you started. If you don't have a primary key defined on your Name and Lot Number table, you need to add one. Then all you have to save in your log file is that primary key.

You can create the log file in a separate database for maximum security if you would like. That's what I did below to illustrate.

On your Wexford form, put a command button named cmdLog or btnLog (actually whatever you want to name it is fine). Then in the On Click event of the log button put something like the following. Substitute your own table and field names as appropriate.

TimeStamp has square brackets [] around it because it is a reserved word. NEVER use reserved words for table or field names. When you forget to use the square brackets you will get odd errors that will throw you off track until you finally notice it is a reserved word.

strSQL has pound signs # around the Now() function because it returns a date/time value. I defined tblLog with 3 fields, LogID as autonumber, primary key, NameID as numeric and TimeStamp as date/time. Since I don't supply a value for LogID and it is autonumber, it will generate one automatically.

The separate database (GLWJunk in my example) was in the same directory where I opened the main one, otherwise if the path is different you need to specify as much path as you need for the system to find it.

Private Sub cmdLog_Click()
Dim wsp As Workspace
Dim dbs As Database
Dim strSQL As String
strSQL = "INSERT INTO tblLog (NameID, [TimeStamp]) " _
& "VALUES (3, #" & Now() & "#)"
Set wsp = DBEngine.Workspaces(0)
Set dbs = wsp.OpenDatabase("GLWJunk.mdb&quot
dbs.Execute strSQL 'Insert a tlbLog record
dbs.Close
Set dbs = Nothing 'Explicitly release object memory
End Sub

Good Luck!

PS. If you post some more information about how you are using the system, perhaps we could help you in other ways as well. Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[SBendBuckeye]

Sorry, you guys were going bach and forth while I was getting around to responding.

 

[CactysJack]

It will take a while to digest this bountiful information. When I do, I'll get back.

This is really the last thing I believe I need to wrap up the program. I think the fact that we need to keep the file not READ ONLY may mean we will have to cover the keyboard and let the operators just use the mouse. That should prevent corruption which is real easy otherwise. The only downside of this is the inability to use the FIND function but that we can forego.

Best,

John

 

[SBendBuckeye]

If you put the log file in a separate database, you can leave the current one read only while still creating log records in the other one.

Good Luck!

 

[JeremyNYC]

Yeah, Now() will work.

Me!PermGst.Form!NameOfYourDateControl = Now()
=============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[CactysJack]

SBendBuckeye -

BINGO!

John

 

[JeremyNYC]

Yeah, the two databases will definitely work. But why rely on the hope that no user will change that read-only flag? I would just institute user-level security and lock up the application so that nothing but malicious intent would allow the unwanted changes to be made.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[CactysJack]

Jeremy -

Thanks for the tip on user level security. In our case, I wouldn't expect anyone to fool with the attributes except the administrator. And if entries or changes are locked out, can the PERMGSTLOG still be updated?

Second question:

How does

"Yeah, Now() will work.

Me!PermGst.Form!NameOfYourDateControl = Now()"

integrate with the earlier code sequence?

Best,

John

 

[JeremyNYC]

John,

First answer: With user-level security you don't have to try to prevent all changes. You can instead allow the users to make some changes and disallow them from making other changes. In a secured application, users never see anything but forms and reports, so you don't have to worry about them messing with data because you only allow them to make changes to the fields that they might legitimately need to change.

Second answer: Just put it in before my "End Sub" line (after changing the name for the control, of course).

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.