Help with forms

[Risk80]

Im trying to create a database for a visitor logging system. Basically, if the visitor has never visited before, he or she must be entered into the database and "signed in". All of the visitor details are stored in a "visitors" table. Here's where the problem lies. If the person is an existing visitor, i.e. someone who has visited in the past, his or her details are already stored, so that person just needs to be assigned a visit from the visits table (since one person can have multiple visits), if it is a previous visitor, I am finding it hard to assign another visit to that person's name. For example if Joe Bloggs visited in the past, and his details as well as previous visit are already stored, then how do I assign another visit to him via a form. I can do this via the datasheet view, but not from within a form.

Since it is unrealistic for a visitor to remember his or her name, there must be a way of typing name and surname into a form to query the database (which I know how to do), but then based on that it needs to return that person's visitor ID which can then be added into the visits table once again (the two are related),

So to summarise the problem, i need it so by entering name and surname, that person's visitor ID is returned and if more than one person with same names a record can be selected by showing the whole record. From this, based on the selection that specific visitor's ID needs to show in the form and then the visit details can be entered into the form and all this is inserted into the visits table (visitor ID in visits is linked to visitor ID in visitor, so by entering the visitor ID in visits, it is automatically assigned to the appropriate visitor).

My guess is some sort of query, but i really have no idea how to go about solving the problem. Please help.

[Dököll]

Im trying to create a database for a visitor logging system. Basically, if the visitor has never visited before, he or she must be entered into the database and "signed in". All of the visitor details are stored in a "visitors" table. Here's where the problem lies. If the person is an existing visitor, i.e. someone who has visited in the past, his or her details are already stored, so that person just needs to be assigned a visit from the visits table (since one person can have multiple visits), if it is a previous visitor, I am finding it hard to assign another visit to that person's name. For example if Joe Bloggs visited in the past, and his details as well as previous visit are already stored, then how do I assign another visit to him via a form. I can do this via the datasheet view, but not from within a form.

Since it is unrealistic for a visitor to remember his or her name, there must be a way of typing name and surname into a form to query the database (which I know how to do), but then based on that it needs to return that person's visitor ID which can then be added into the visits table once again (the two are related),

So to summarise the problem, i need it so by entering name and surname, that person's visitor ID is returned and if more than one person with same names a record can be selected by showing the whole record. From this, based on the selection that specific visitor's ID needs to show in the form and then the visit details can be entered into the form and all this is inserted into the visits table (visitor ID in visits is linked to visitor ID in visitor, so by entering the visitor ID in visits, it is automatically assigned to the appropriate visitor).

My guess is some sort of query, but i really have no idea how to go about solving the problem. Please help.

Hello, Risk80!

Nicely detailed info...

You came to the right place, please fetch deeper into the pages, you will find posts on search mechanisms that'll work for you.

Currently on a project, turning in now, but do stay tuned, if other experts hadn't gotten to this, I'll fetch you something I know will work, of course you'd have to do a lot of the leg work:-)

In a bit!

[Minion]

I have a very similar project to this that I am working, but I think we're taking slightly different approaches. Regardless I should be able to dig something up for you that will work or at least a link to some of the threads there that have helped me, I'll have to look around.

First though I was wondering two things that I faced:

1. Are you only referencing people by their names? If so what happens when you run into individuals with the same name?

2. Can more than one more person be assigned to any one visit?

Like I said I'll try and dig some things up that might help, but for now let me point you at the DLookup function as this may get you moving in the right direction.

Hope this helps.

- Minion -

[Risk80]

Thanks for reply
No, I am not only referencing people by name, each person has a visitor ID, which is linked to the visits table. It is unrealistic for people to remmeber their ID so it is easier if they provide their name and the appropriate record is selected. Each visit may only have one visitor, but a single visitor can have many visits.

It is not a problem for if the person is a new visitor, but the problem i face is assigning more than one visit to a given visitor. The data structure allows for this and relationships have been set correctly, and it works if i do it in the datasheet, but I am unable to replicate this in a form (changing to next record goes to the next record for both visitor and visits).

As a note, it works if I enter a field for visitor ID and manually type the desired visitor ID into the form, but i want a more user friendly way of doing this (ie. converting an entered name into a visitor ID, and if 2 or more people have the same name, list all records with that name so that the required one can be selected). Maybe a query, where the result is displayed as a combo box in the form so that the required visitor can be selected if more than one, but i dont know how i would implement this.

I will try experimenting in the meantime with dlookup.

[lee123]

Hi risk80,

i have made this search box but i don't know if it will help you at all but you can try this code:

Private Sub Text63_AfterUpdate()
 
     Dim mydata As String
 
     mydata = Text63  'WHAT YOU WANT TO USE EXAMPLE: TEXTBOX, INPUTBOX
     If mydata = "" Or IsNull(mydata) Then Exit Sub
     DoCmd.GoToControl "customername"  ' OR WHAT ARE YOU SEARCHING FOR
     DoCmd.FindRecord mydata, acAnywhere, False, acSearchAll, , acCurrent, True
End Sub

lee123

[Risk80]

Thanks lee, I used a modified version of your code and eventually got it to work. Thanks :-)

[lee123]

Glad i could help.

lee123