Access 2010 database programming question

[napiedra]

I have a scanner gun and I want to use to read users TAG numbers and populate a database table. All I need is the following:



when user check-in by scanning his/her number the programs tries to find if the user was there:

1) if the user was not there, create a record for that user and have a field autopopulate with a date/time stamp for current date/time.

2) If the user has been there for some time, find the user and autopopulate the next field with date/time out and calculate the time difference in a third field and calculate a time rate in money based on hourly rate.

How hard is this for someone with very minimal experiece with Access 2012?

[June7]

Use of scanners for data entry is getting to be a frequent topic here. Search forum. Maybe this discussion will be helpful to you: https://www.accessforums.net/access/...70/index2.html

Consider a combobox with LimitToList property set to Yes. Then use code in the NotInList event to open form to create new user record.

Will there never be more than one record for each user?

[napiedra]

Users will be there for less than 2 days. After a user uses a tag in one day and he comes back a different day we issue a different tag so it will be a new record. Users will only have one record per day. If they come back a different day they will be a different user with different tag.

[June7]

Okay, so scan the tag into an unbound combobox or textbox. Depending on which you go with the code will be a little different.

Why would user have a tag and record not already exist?

[napiedra]

The tag is a roll of paper with barcode info and when a user comes in to use resources we assign tags by just pulling one of the roll and giving them to the them. The tag will be presented back when they are ready to live. That way we can determine how long the users stayed using the facility resources. We don't want to scan a tag until a user arrives so they are not charged more time than the actual time they were there.

[napiedra]

When you say a combo box, is that done with a form, table or query?

[June7]

Wish I knew more about your data structure. Want to provide db? Follow instructions at bottom of my post.

The combobox would be on a form because need code behind it.

[napiedra]

CTPdatabese.accdb Here you have the code. Remember, I am just trying to come up with this. I have not gotten anything to work yet. Thanks for your help.

[June7]

Since Tag is unique in the table, could designate it as primary key and eliminate the ID autonumber.

There is no need for the CurrentDate field. Populate TimeIn and TimeOut with full date/time. Set TimeIn with DefaultValue of Now() or use code to populate field.

Elapsed time can be calculated when needed. This data does not need to be saved to table. Use expression in textbox or in query: DateDiff("n",[Time In],[Time Out])
Divide by 60 if you want decimal hours instead of minutes.

Also, elapsed time would not be Date/Time type field anyway.

Put an unbound combobox named cbxTag in the form header, RowSource: SELECT Tag FROM CTP;
or a textbox named tbxEnterTag.

Code for combobox (if you prefer textbox, just change the name referenced):

Option Compare Database
Option Explicit

Private Sub cbxTag_AfterUpdate()
With Me.RecordsetClone
.FindFirst "Tag=" & Me.cbxTag
If .NoMatch Then
    DoCmd.GoToRecord acActiveDataObject, , acNewRec
    Me!TAG = Me.cbxTag
Else
    Me.Bookmark = .Bookmark
    Me.Time_Out = Now()
End If
End With
DoCmd.RunCommand acCmdSaveRecord
End Sub

Set all the textboxes in Detail section as Locked Yes, TabStop No.

What is AmountOwn field for? It is not on the form.

Advise no spaces in field names.

[napiedra]

Thank you so much. I would like to work a bit with your information and see if I can get it working...

[napiedra]

I have it almost all working except one thing:

On my code I update certain field but there is one field I need to uptade differently. The field AmountOwed is ElapsedTime/60 * HourlyRate

If the elapsed time is less than 30 minutes we need the value of AmountOwed to alwasy be no less than $2.00 but if it is more than 30 minutes we want the value to be the calculation.

Here is my code that works with my additions for the calulation

Option Compare Database
Private Sub Form_Load()
DoCmd.GoToRecord acDataForm, "CTParkingLLC", acNewRec
Forms!CTParkingLLC!FindTAG.SetFocus
End Sub
Private Sub FindTAG_AfterUpdate()
If (FindTAG & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[TAG]=" & FindTAG
If rs.NoMatch Then
DoCmd.GoToRecord acActiveDataObject, , acNewRec
Me!TAG = Me.FindTAG
Me.TimeIn = Now()
Me.HoulyRate = 7
Else
Me.Recordset.Bookmark = rs.Bookmark
Me.TimeOut = Now()
Me.ElapsedTime = DateDiff("n", [TimeIn], [TimeOut])
Me.AmountOwed = [ElapsedTime] / 60 * [HoulyRate]
End If
rs.Close
FindTAG = Null
End Sub



Now here is the same code with what I added to make it work but it is not working:

Option Compare Database
Private Sub Form_Load()
DoCmd.GoToRecord acDataForm, "CTParkingLLC", acNewRec
Forms!CTParkingLLC!FindTAG.SetFocus
End Sub
Private Sub FindTAG_AfterUpdate()
If (FindTAG & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[TAG]=" & FindTAG
If rs.NoMatch Then
DoCmd.GoToRecord acActiveDataObject, , acNewRec
Me!TAG = Me.FindTAG
Me.TimeIn = Now()
Me.HoulyRate = 7
Else
Me.Recordset.Bookmark = rs.Bookmark
Me.TimeOut = Now()
Me.ElapsedTime = DateDiff("n", [TimeIn], [TimeOut])
End If
Dim X As Integer
X = [ElapsedTime] / 60 * [HoulyRate]
If X < 2 Then Me.AmountOwed = 2
Else
Me.AmountOwed = [ElapsedTime] / 60 * [HoulyRate]
X.Close
rs.Close
FindTAG = Null
End Sub

Please, help will be much appreciated...

[June7]

Why is it not working, what happens - error message, wrong results, nothing?

X cannot be declared an integer unless you round the result of the calc to a whole number.

Why repeat the calc?

Me.AmountOwed = X

Missing End If for the If X < 2

Don't need X.Close

Run Debug > Compile

[napiedra]

What was happening was nothing. The form would stay there but the record AmountOwed did not get updated like I expected

[June7]

Did the corrections help?

Step debug. Follow code as it executes. See where it deviates from expected behavior. Fix. Repeat.

Review link at bottom of my post for debug guidelines.

[napiedra]

I got it working with the information you provided. Thanks!