Re: VBA search coding problem -- Combobox Example

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


Use a combobox to pick the code and then they can only choose one that DOES exists

a control to be used for searching will be unbound (no ControlSource), so whatever record is on the screen will not be changed. I also like to change the background color of search combos so that the user knows this is not part of the data

"Do you mind explain what problem the If Else statement that I had that made it that it only runs parts of the statement? "

you had:

If IsNull(Me![IDsearch]) Or (Me![IDsearch]) = "" then

if IDsearch is nothing (null), you cannot compare that to a value... that is why I converted what you had to

Len(Trim(nz(Me.IDsearch,""))) = 0

Now, it appears that the real problem is that the user may enter an invalid patient id... using a combobox will solve this

Anyway, IMHO, you should not be using a text field to relate data in your tables. Conventionally, using ID in the fieldname implies that fieldname was created using an autonumber field in the main table and is defined to be a long integer in related tables. To avoid confusion, if you have a "Patient ID" that is their hospital number, consider using a different fieldname for that like "PatientNumber" or "PatientNum" (I like to keep names short)

Combobox Example

* Under no circumstances should you store names in more than one place. For instance, if you have a People table, define a PID (or PeopleID) autonumber field. Then, in other tables, when you want to identify a person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form), you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more than the sum of the columns to prevent the horizontal scrollbar from appearing.

PID will be stored in the form RecordSource while showing you names from another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that column indexes start with 0, not 1, in Access.



Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

ps... thanks, Allen ;)


esca007 wrote:
thank you for your response but the question is that the codes right now does not tell the data entry person that the ID does not exist. Do you mind explain what problem the If Else statement that I had that made it that it only runs parts of the statement? I tried that code in its original database and it works... so really want to know what I did wrong. thanks

"Allen Browne" wrote:

A bound form has a RecordsetClone - another set of pointers to the same records. You can search for the record in the clone set, and test NoMatch to see whether it was found. If it's not found, you can notify the user without having moved away from the current record. If it is found, you can set the form's Bookmark to the bookmark of the found record in the clone set.

Crystal's code takes care of all of that:
- checking the user entered something to search for;
- explicitly saving the record if edits are in progress (since it must be saved before you can go to another records);
- finding the first match in the RecordsetClone of the form;
- testing if a match was found;
- moving to that record if it was found.

I recommend you take her approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"esca007" <esca007@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B1B045DC-CC50-4873-9793-9A3B7C7B43A6@xxxxxxxxxxxxxxxx
Hi,
Thanks for the new code. I replaced your code with mine but it still doesn't
work.
Can you actually explain to me what your code means? Thanks

"strive4peace" wrote:

'~~~~~~~~~~~~~~~
If Len(Trim(nz(Me.IDsearch,""))) = 0 Then
MsgBox "Please enter a value to search for", _
vbOKOnly, _
"Invalid Search Criterion!"
Me.IDsearch.SetFocus
Exit Sub
end if

'save current record if changes were made
If me.dirty then me.dirty = false

Me.RecordsetClone.FindFirst _
"[Patient ID]= '" & Me.IDsearch & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

'~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



esca007 wrote:
Hi, I'm trying to develop a search function for my form, and found a similar
code for the website. After changing it, I've almost made the search work but
the problem I have right now is that the pop up function does not
differentiate between a correct ID and an incorrect ID. I think it has to do
with my If.. Else code. It will not do the 2 tasks in sequences. Below is my
code.

rivate Sub Search_Click()
Dim strPatient_ID As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

If IsNull(Me![IDsearch]) Or (Me![IDsearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![IDsearch].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Patient ID")
DoCmd.FindRecord Me!IDsearch

Patient_ID.SetFocus
strPatientID = Patient_ID.Text
IDsearch.SetFocus
strSearch = IDsearch.Text

'If matching record found sets focus in strPatientID
'and clears search control

If strPatient_ID <> strSearch Then
MsgBox "Match Found: " & strSearch & "" _
, , "Congratulations!"
IDsearch.SetFocus
IDsearch = ""
'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try Again." _
, , "Invalid Search Criterion!"
IDsearch.SetFocus
End If
End Sub

The problem starts at the Comment "If matching record found sets focus in
strPatientID 'and clears search control". The If Else function doesnt work.
In this set up, it'll going do the "If" task regardless if I type in an
existing ID or not.
Can anyone tell me what I wrote wrong? Thank you very much


.

Relevant Pages

  • URGENT Re: VBA search coding problem -- Combobox Example
    ... MVP Access ... a control to be used for searching will be unbound, ... fieldname was created using an autonumber field in the main table and is ... For instance, if you have a People table, define a PID ...
    (microsoft.public.access.modulesdaovba)
  • Re: VBA search coding problem -- Combobox Example
    ... a control to be used for searching will be unbound, ... using a combobox will solve this ... fieldname was created using an autonumber field in the main table and is ... For instance, if you have a People table, define a PID ...
    (microsoft.public.access.modulesdaovba)
  • Re: VBA search coding problem -- Combobox Example
    ... MVP Access ... a control to be used for searching will be unbound, so whatever record is on the screen will not be changed. ... using ID in the fieldname implies that fieldname was created using an autonumber field in the main table and is defined to be a long integer in related tables. ... For instance, if you have a People table, define a PID autonumber field. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Subform w/defaults from main form -- Combobox Example
    ... instead of setting the DefaultValue property of the subform control, ... Combobox Example ... PID, long, DefaultValue = Null ...
    (microsoft.public.access.forms)
  • Re: Show description in combobox -- Combobox Example
    ... Combobox Example ... Create an autonumber field in the People table --> ... PID, long, DefaultValue = Null ... create a combobox control ...
    (microsoft.public.access.forms)