Client sign in, using form (1 Viewer)

[NixonShaun]

Hi I am working on a client sign in system using a form.
Table is ClientInfo
Fields are: ID. UserName. BirthDate. Phone. Last4SSN. FirstName. LastName. Email. And Date. The date is set to default value ( ). For today and it automatically assigns today's date to your visit.

I have a form that essentially has two buttons, one is for a new client to add there contact info via a separate form by providing the info. ID is primary key, UserName is last four of SSN and LastName (I would like it to auto generate but have only been able to concatenate in query.

The second button is for a returning user, which opens a new form with one field prompting you to enter UserName, when you do so and click a save button it should read the database, find a matching username and associated info and log a visit saving all info associated with your username and today's date.
I unfortunately am going to need help with the VB, presently it save an entry of today's date and the username only. No phone, email first, last etc

Please help.
Thanks
Shaun

 

[Ranman256]

For new user, user fills data, then clicks Save, which runs an append query. (no vb)
for returning user, user enters name, then on Enter click, do a lookup, if found open a form , where the query
uses the name listed in the text box.

sub btnEnter_Click()
if IsNull(Dlookup("UserName"],"table","[UserName]='" & txtBoxName & "'")) then
   msgbox txtBoxName & " not found"
else
   docmd.openform "frmFoundName"
end if
end sub

 

[theDBguy]

Hi Shaun. It sounded like you are duplicating user info in your sign in table. If so, you shouldn't. Just store the primary key value and use a query to display the associate info about the user.

 

[NixonShaun]

Thanks for your help Ranman and TheDBguy. Your insight helped greatly, I do simply want to append the date to a table and record the number of times a person visits. I also did run into the 'duplicating user info' issue.
To get around this i did the following:
I created two Tables - 1) Client Information Table and 2) Date of visit table. Both tables have a field called UserName and there is a relationship between these two tables on this field.
I can now have a hypthetical user fill out some simple demographic info: UserName, BirthDate, TelephoneNumber, Email, FirstName, LastName etc and then on a seperate form they can enter their UserName and todays date. This will log multiple visits in the DateOfVisit table. A query will show the different dates visited or can use Count to show the number of times visited.

At present there is no VB code, just simple Macros.
Does this seem like it fits the generally accepted database rules. Our local career center has been using a paper sign in sheet, which is fine but has to be manually compiled for reports and statistics of number of visitors and where they live.

I would like to automate this somewhat to make it easier to track how many visits they get and where those individuals are coming from.

 

[The_Doc_Man]

I would like to automate this somewhat to make it easier to track how many visits they get and where those individuals are coming from.

First, are you in a domain-based environment? If so, you can eliminate some parts of this process or at least simplify it. It is possible to get the domain username from the Environ("Username") function and the computer name from the Environ("Computername") function. So you can test for it being a new or returning user instantly by doing a DLookup or DCount of that username.

From there you would know what else you needed to know. For the security of the login, if it is a domain-based system, you can decide to "trust" the domain because Windows Domain logins are more secure than anything you are likely to implement with simple Access methods.

 

[NixonShaun]

Sorry Doc Man, there is no domain. It will be a simple kiosk type setup for the general public to visit the career center. we have to track the number of daily visits and the towns visitors come from.

 

[NixonShaun]

For new user, user fills data, then clicks Save, which runs an append query. (no vb)
for returning user, user enters name, then on Enter click, do a lookup, if found open a form , where the query
uses the name listed in the text box.

sub btnEnter_Click()
if IsNull(Dlookup("UserName"],"table","[UserName]='" & txtBoxName & "'")) then
   msgbox txtBoxName & " not found"
else
   docmd.openform "frmFoundName"
end if
end sub

Ranman, I used your code as an example but modified slightly.
The field i am looking up in the Table is UserName (its a Surname+last 4 digits of social security number = e.g Potter2345) the table is called ClientInfo and the textbox is txt3. Your script does a lookup in the table and lets the user know via message box if that username is not listed in the table. I have added it to msgbox indicating if it is found, and then onto a form where you input the information (again) and todays date to save today as the date you visited the career center. Is there a way to simplify this, where if your username is found in the database it will automatically append it to the database without having to open a form and re-enter this information?

Private Sub Command5_Click()
If IsNull(DLookup("[UserName]", "ClientInformation", "[UserName]='" & Text3 & "'")) Then
MsgBox Text3 & " not found in Database, please register as new user"
Else
MsgBox Text3 & " is registered as a user with the career center, when prompted please enter the username and today's date"
DoCmd.OpenForm "DateOfVisit", , , , acFormAdd
End If
End Sub