A good method for searching a table with an entry from a form?

[Michael_Randall]

I am trying to figure out a good method for allowing a user to enter student
data into a form to determine if the student already exists in a table.

I have a table: STUDENTSTABLE with the fields Last_Name, First_Name, and DOB.
I have a form: frmSEARCH with the text boxes lname, fname, and dob. The form
also contains a button: search_students. I have not specified a record source
for the form.

After the user enters he student information into the form and clicks
search, a message box will popup stating whether the student is or is not
already in the table.

I know how to popup the message box and I know how to access the on-click
event of the button, and I know how to make sure that all fields in the form
contain data.

I don't know the best way, programmatically/or other to take the information
from the form and use it, I'm assuming in a query, to find out if those
values already exist in the table. I've read some of the ADO and DAO
information from the book Access 2007 Insided Out, but I seem to be missing
something.

Any would be greatly appreciated. Please let me know if you need
clarification as to what I have done so far.

Thanks.

 

[Dale Fye]

Michael

Here is what I would do.

1. Put the command button in the forms footer

2. Add a listbox to the form, below the textboxes where you enter the name
and dob information. This listbox will have a blank row source, which we
will fill in later.

3. Figure out how high your form footer (with the command buttons) and the
textbox section of the form is (disregard the listbox). Once you have
figured this out, add a line of code to the forms Load event that sets the
height of the form to this height, effectively hiding the listbox).

me.insideheight = 3 * 1440 'assumes 3" height

4. In the click event of the cmdSearch do something like the following.
I've included the POB (place of birth) field to allow you to identify more
accurately the name of the student if there is more than one with the same
first/last/dob combination.

Private sub cmdSearch_Click

Dim strSQL as string

strSQL = "SELECT StudentID, Last_Name, First_Name, DOB, POB " _
& "FROM tblStudents " _
& "WHERE [Last_Name] = '" & me.txtLastName & "' " _
& "AND [First_Name] = '" & me.txtFirstName & "' " _
& "AND [DOB] = #" & cdate(me.txtDOB) & "#"
me.lstStudentMatch.RowSource = strsql

if me.lstStudentMatch.listcount = 0 then
msgbox "No matches!"
me.insideheight = 3 * 1440
else
me.insideheight = 6 * 1440 'assumes listbox is just under 3" high
endif

End Sub

5. You might also include a GotoRecord button which would be hidden and
disabled in the footer until the listbox gets displayed. And add code in the
listboxes Click event to enable that button. This button would have code
behind it which would find display that student in some other form which is
already open or which gets opened by the button.

 

[Michael_Randall]

Hi Dale,
--
Michael Randall
Student
Keller Graduate School of Management
Masters of Information Systems Management

Michael

Here is what I would do.

1. Put the command button in the forms footer

-------Ok, this worked

2. Add a listbox to the form, below the textboxes where you enter the name
and dob information. This listbox will have a blank row source, which we
will fill in later.

-------Ok, I added a list box lstStudentMatch and made sure that the row
source was blank. Should I set my form Record Source to STUDENTSTABLE?

3. Figure out how high your form footer (with the command buttons) and the
textbox section of the form is (disregard the listbox). Once you have
figured this out, add a line of code to the forms Load event that sets the
height of the form to this height, effectively hiding the listbox).

--------I estimated my form (footer, header, and body [no list box]) height
at 2" and set the form load event to "me.insideheight =2*1440".

--------The form still opened to the full size.

me.insideheight = 3 * 1440 'assumes 3" height

4. In the click event of the cmdSearch do something like the following.
I've included the POB (place of birth) field to allow you to identify more
accurately the name of the student if there is more than one with the same
first/last/dob combination.

Private sub cmdSearch_Click

Dim strSQL as string

strSQL = "SELECT StudentID, Last_Name, First_Name, DOB, POB " _
& "FROM tblStudents " _
& "WHERE [Last_Name] = '" & me.txtLastName & "' " _
& "AND [First_Name] = '" & me.txtFirstName & "' " _
& "AND [DOB] = #" & cdate(me.txtDOB) & "#"
me.lstStudentMatch.RowSource = strsql

if me.lstStudentMatch.listcount = 0 then
msgbox "No matches!"
me.insideheight = 3 * 1440
else
me.insideheight = 6 * 1440 'assumes listbox is just under 3" high
endif

End Sub

---------Here is my code:
Dim strSQL As String

strSQL = "SELECT StudentID, Last_Name, First_Name, DOB_or_SS " _
& "FROM STUDENTSTABLE " _
& "WHERE [Last_Name] = '" & Me.L_Name & "' " _
& "AND [First_Name] = '" & Me.F_Name & "' " _
& "AND [DOB_or_SS] = #" & CDate(Me.Date_Birth) & "#"

Me.lstStudentMatch.RowSource = strSQL

If Me.lstStudentMatch.ListCount = 0 Then
MsgBox ("No Matches!")
Me.InsideHeight = 2 * 1440
Else
Me.InsideHeight = 4 * 1440
End If

End Sub

-------This seems to work, except that in my table I have a Student_ID field
and when I click the button on the form, I get a popup box asking for the
Student_ID, similar to a parameter query. If the student is in the database
and I enter their student ID, the lstStudentMatch field then displays the
student ID value.

If the student is not in the table, the msgbox with "No Matches!" pops up.
So that is good.

I'm wondering why I'm being asked to supply the Student ID in a parameter
query. Also, the form doesn't seem to be resizing. Any recommendations on
these last two situations?

Thanks.

 

[Michael_Randall]

Hi Dale,

Ok, I figured out what the problem was and everything seems to work except
for the resizing of the form.

In my select query, I had added Student_ID. So now I have removed it and it
works fine. Again, just can't figure out the form resize. Should I have put
the list box in the footer or in the body just under the text boxes?
--
Michael Randall
Student
Keller Graduate School of Management
Masters of Information Systems Management

Michael

Here is what I would do.

1. Put the command button in the forms footer

2. Add a listbox to the form, below the textboxes where you enter the name
and dob information. This listbox will have a blank row source, which we
will fill in later.

3. Figure out how high your form footer (with the command buttons) and the
textbox section of the form is (disregard the listbox). Once you have
figured this out, add a line of code to the forms Load event that sets the
height of the form to this height, effectively hiding the listbox).

me.insideheight = 3 * 1440 'assumes 3" height

4. In the click event of the cmdSearch do something like the following.
I've included the POB (place of birth) field to allow you to identify more
accurately the name of the student if there is more than one with the same
first/last/dob combination.

Private sub cmdSearch_Click

Dim strSQL as string

strSQL = "SELECT StudentID, Last_Name, First_Name, DOB, POB " _
& "FROM tblStudents " _
& "WHERE [Last_Name] = '" & me.txtLastName & "' " _
& "AND [First_Name] = '" & me.txtFirstName & "' " _
& "AND [DOB] = #" & cdate(me.txtDOB) & "#"
me.lstStudentMatch.RowSource = strsql

if me.lstStudentMatch.listcount = 0 then
msgbox "No matches!"
me.insideheight = 3 * 1440
else
me.insideheight = 6 * 1440 'assumes listbox is just under 3" high
endif

End Sub

5. You might also include a GotoRecord button which would be hidden and
disabled in the footer until the listbox gets displayed. And add code in the
listboxes Click event to enable that button. This button would have code
behind it which would find display that student in some other form which is
already open or which gets opened by the button.


----
HTH
Dale

I am trying to figure out a good method for allowing a user to enter student
data into a form to determine if the student already exists in a table.

I have a table: STUDENTSTABLE with the fields Last_Name, First_Name, and DOB.
I have a form: frmSEARCH with the text boxes lname, fname, and dob. The form
also contains a button: search_students. I have not specified a record source
for the form.

After the user enters he student information into the form and clicks
search, a message box will popup stating whether the student is or is not
already in the table.

I know how to popup the message box and I know how to access the on-click
event of the button, and I know how to make sure that all fields in the form
contain data.

I don't know the best way, programmatically/or other to take the information
from the form and use it, I'm assuming in a query, to find out if those
values already exist in the table. I've read some of the ADO and DAO
information from the book Access 2007 Insided Out, but I seem to be missing
something.

Any would be greatly appreciated. Please let me know if you need
clarification as to what I have done so far.

Thanks.

--
Michael Randall
Student
Keller Graduate School of Management
Masters of Information Systems Management

 

[Dale Fye]

1. No, just leave the search forms RecordSource blank.

2. Do you have your form maximized in design view? If so, then try adding:

DoCmd.Restore

immediately after the me.insideHeight command.

3. You used StudentID (my guess as this was not in your original post) in
the SQL query, when it should have been Student_ID. Personally, I would
leave the StudentID field hidden in the list (zero width), since you really
don't need to see it.

----
HTH
Dale

Hi Dale,
--
Michael Randall
Student
Keller Graduate School of Management
Masters of Information Systems Management

Michael

Here is what I would do.

1. Put the command button in the forms footer

-------Ok, this worked

2. Add a listbox to the form, below the textboxes where you enter the name
and dob information. This listbox will have a blank row source, which we
will fill in later.

-------Ok, I added a list box lstStudentMatch and made sure that the row
source was blank. Should I set my form Record Source to STUDENTSTABLE?

3. Figure out how high your form footer (with the command buttons) and the
textbox section of the form is (disregard the listbox). Once you have
figured this out, add a line of code to the forms Load event that sets the
height of the form to this height, effectively hiding the listbox).

--------I estimated my form (footer, header, and body [no list box]) height
at 2" and set the form load event to "me.insideheight =2*1440".

--------The form still opened to the full size.

me.insideheight = 3 * 1440 'assumes 3" height

4. In the click event of the cmdSearch do something like the following.
I've included the POB (place of birth) field to allow you to identify more
accurately the name of the student if there is more than one with the same
first/last/dob combination.

Private sub cmdSearch_Click

Dim strSQL as string

strSQL = "SELECT StudentID, Last_Name, First_Name, DOB, POB " _
& "FROM tblStudents " _
& "WHERE [Last_Name] = '" & me.txtLastName & "' " _
& "AND [First_Name] = '" & me.txtFirstName & "' " _
& "AND [DOB] = #" & cdate(me.txtDOB) & "#"
me.lstStudentMatch.RowSource = strsql

if me.lstStudentMatch.listcount = 0 then
msgbox "No matches!"
me.insideheight = 3 * 1440
else
me.insideheight = 6 * 1440 'assumes listbox is just under 3" high
endif

End Sub

---------Here is my code:
Dim strSQL As String

strSQL = "SELECT StudentID, Last_Name, First_Name, DOB_or_SS " _
& "FROM STUDENTSTABLE " _
& "WHERE [Last_Name] = '" & Me.L_Name & "' " _
& "AND [First_Name] = '" & Me.F_Name & "' " _
& "AND [DOB_or_SS] = #" & CDate(Me.Date_Birth) & "#"

Me.lstStudentMatch.RowSource = strSQL

If Me.lstStudentMatch.ListCount = 0 Then
MsgBox ("No Matches!")
Me.InsideHeight = 2 * 1440
Else
Me.InsideHeight = 4 * 1440
End If

End Sub

-------This seems to work, except that in my table I have a Student_ID field
and when I click the button on the form, I get a popup box asking for the
Student_ID, similar to a parameter query. If the student is in the database
and I enter their student ID, the lstStudentMatch field then displays the
student ID value.

If the student is not in the table, the msgbox with "No Matches!" pops up.
So that is good.

I'm wondering why I'm being asked to supply the Student ID in a parameter
query. Also, the form doesn't seem to be resizing. Any recommendations on
these last two situations?

Thanks.

5. You might also include a GotoRecord button which would be hidden and
disabled in the footer until the listbox gets displayed. And add code in the
listboxes Click event to enable that button. This button would have code
behind it which would find display that student in some other form which is
already open or which gets opened by the button.

 

[Dale Fye]

listbox goes in the body, just under the text boxes.

Dale

Hi Dale,

Ok, I figured out what the problem was and everything seems to work except
for the resizing of the form.

In my select query, I had added Student_ID. So now I have removed it and
it
works fine. Again, just can't figure out the form resize. Should I have
put
the list box in the footer or in the body just under the text boxes?
--
Michael Randall
Student
Keller Graduate School of Management
Masters of Information Systems Management

Michael

Here is what I would do.

1. Put the command button in the forms footer

2. Add a listbox to the form, below the textboxes where you enter the
name
and dob information. This listbox will have a blank row source, which we
will fill in later.

3. Figure out how high your form footer (with the command buttons) and
the
textbox section of the form is (disregard the listbox). Once you have
figured this out, add a line of code to the forms Load event that sets
the
height of the form to this height, effectively hiding the listbox).

me.insideheight = 3 * 1440 'assumes 3" height

4. In the click event of the cmdSearch do something like the following.
I've included the POB (place of birth) field to allow you to identify
more
accurately the name of the student if there is more than one with the
same
first/last/dob combination.

Private sub cmdSearch_Click

Dim strSQL as string

strSQL = "SELECT StudentID, Last_Name, First_Name, DOB, POB " _
& "FROM tblStudents " _
& "WHERE [Last_Name] = '" & me.txtLastName & "' " _
& "AND [First_Name] = '" & me.txtFirstName & "' " _
& "AND [DOB] = #" & cdate(me.txtDOB) & "#"
me.lstStudentMatch.RowSource = strsql

if me.lstStudentMatch.listcount = 0 then
msgbox "No matches!"
me.insideheight = 3 * 1440
else
me.insideheight = 6 * 1440 'assumes listbox is just under 3" high
endif

End Sub

5. You might also include a GotoRecord button which would be hidden and
disabled in the footer until the listbox gets displayed. And add code in
the
listboxes Click event to enable that button. This button would have code
behind it which would find display that student in some other form which
is
already open or which gets opened by the button.


----
HTH
Dale

I am trying to figure out a good method for allowing a user to enter
student
data into a form to determine if the student already exists in a table.

I have a table: STUDENTSTABLE with the fields Last_Name, First_Name,
and DOB.
I have a form: frmSEARCH with the text boxes lname, fname, and dob. The
form
also contains a button: search_students. I have not specified a record
source
for the form.

After the user enters he student information into the form and clicks
search, a message box will popup stating whether the student is or is
not
already in the table.

I know how to popup the message box and I know how to access the
on-click
event of the button, and I know how to make sure that all fields in the
form
contain data.

I don't know the best way, programmatically/or other to take the
information
from the form and use it, I'm assuming in a query, to find out if those
values already exist in the table. I've read some of the ADO and DAO
information from the book Access 2007 Insided Out, but I seem to be
missing
something.

Any would be greatly appreciated. Please let me know if you need
clarification as to what I have done so far.

Thanks.

--
Michael Randall
Student
Keller Graduate School of Management
Masters of Information Systems Management

 

[Michael_Randall]

Thanks for the help Dale!

I will probably expand the functionality of this item. I just haven't
decided completely the best way for the complete task to be accomplished
(usability).

If the person is in the database, I would want to show form that displays
their name as well as class status (3 tables).

if the person isn't in the database, I would like to give the user the
option of adding them. This entails creating a form that accesses two
additional tables (total 3).

So, anyway, I need to do some more task analysis.

Thanks again.






--
Michael Randall
Student
Keller Graduate School of Management
Masters of Information Systems Management

listbox goes in the body, just under the text boxes.

Dale

Hi Dale,

Ok, I figured out what the problem was and everything seems to work except
for the resizing of the form.

In my select query, I had added Student_ID. So now I have removed it and
it
works fine. Again, just can't figure out the form resize. Should I have
put
the list box in the footer or in the body just under the text boxes?
--
Michael Randall
Student
Keller Graduate School of Management
Masters of Information Systems Management

Michael

Here is what I would do.

1. Put the command button in the forms footer

2. Add a listbox to the form, below the textboxes where you enter the
name
and dob information. This listbox will have a blank row source, which we
will fill in later.

3. Figure out how high your form footer (with the command buttons) and
the
textbox section of the form is (disregard the listbox). Once you have
figured this out, add a line of code to the forms Load event that sets
the
height of the form to this height, effectively hiding the listbox).

me.insideheight = 3 * 1440 'assumes 3" height

4. In the click event of the cmdSearch do something like the following.
I've included the POB (place of birth) field to allow you to identify
more
accurately the name of the student if there is more than one with the
same
first/last/dob combination.

Private sub cmdSearch_Click

Dim strSQL as string

strSQL = "SELECT StudentID, Last_Name, First_Name, DOB, POB " _
& "FROM tblStudents " _
& "WHERE [Last_Name] = '" & me.txtLastName & "' " _
& "AND [First_Name] = '" & me.txtFirstName & "' " _
& "AND [DOB] = #" & cdate(me.txtDOB) & "#"
me.lstStudentMatch.RowSource = strsql

if me.lstStudentMatch.listcount = 0 then
msgbox "No matches!"
me.insideheight = 3 * 1440
else
me.insideheight = 6 * 1440 'assumes listbox is just under 3" high
endif

End Sub

5. You might also include a GotoRecord button which would be hidden and
disabled in the footer until the listbox gets displayed. And add code in
the
listboxes Click event to enable that button. This button would have code
behind it which would find display that student in some other form which
is
already open or which gets opened by the button.


----
HTH
Dale



:

I am trying to figure out a good method for allowing a user to enter
student
data into a form to determine if the student already exists in a table.

I have a table: STUDENTSTABLE with the fields Last_Name, First_Name,
and DOB.
I have a form: frmSEARCH with the text boxes lname, fname, and dob. The
form
also contains a button: search_students. I have not specified a record
source
for the form.

After the user enters he student information into the form and clicks
search, a message box will popup stating whether the student is or is
not
already in the table.

I know how to popup the message box and I know how to access the
on-click
event of the button, and I know how to make sure that all fields in the
form
contain data.

I don't know the best way, programmatically/or other to take the
information
from the form and use it, I'm assuming in a query, to find out if those
values already exist in the table. I've read some of the ADO and DAO
information from the book Access 2007 Insided Out, but I seem to be
missing
something.

Any would be greatly appreciated. Please let me know if you need
clarification as to what I have done so far.

Thanks.

--
Michael Randall
Student
Keller Graduate School of Management
Masters of Information Systems Management

.