Select records question

[Susan K via AccessMonster.com]

You guys have been wonderfully helpful to me in keeping me on the right track
with my
database. I have some expertise in creating web sites but this is definitely
nothing
like that at all, yet I still find myself thinking in that mode. Ugh! My
question is this:
How do I either hit Enter or Click on a Combo Box so that it pulls up the
record I want
to view? On this database I am creating I already have about 9 "practice"
records. I would
like the users to be able to pull these up to edit the information as needed.
I have done
all sorts of permutations on the setup for this combo box--I have it looking
the way I want
it to, etc. Setting up the combo box is not a problem at all for me. I just
need to get the
code to work. I have read all over the internet, and in the books I have
here, on different ways
to do it ..and have come up with nothing. I even tried Tom Wickerath's set up
on Northwind
and got it working with no problem at all, but when I tried to do the same
setup on my DB,
everything worked in the code worked except for pulling up the selected
records! Is there
a simple way to do this with the Code? Thanks!

Susan

 

[A Newbie]

I'm a newbie but I presume give you a suggest

I think you need select SQL string with criteria is your combo box and set
data on your form is

Yourforms.Recordsource = this SQL

 

[Chris B via AccessMonster.com]

In your forms design view, (make sure the "wizord" is selected), place a new
combo box onto the form- the wizord will ask you what you want to do, select
the "i want the combo box to lookup a record", take it from there...

 

[Susan K via AccessMonster.com]

Woudl I just enter the form name (PatientInfo) into the Recordsource
space or would I enter it in the SQL area as PatientInfo.RecordSource?
I am a bit confused here.

Susan

 

[Susan K via AccessMonster.com]

Chris,

I don't know what happened to the first response to you. I posted
it before I posted to Newbie, but it isn't here so I guess something
went wrong with it. To recap what I said earlier, I have no problem
at all using the combo box wizard to try to do what I want to do. I
have done it a dozen times over or more in trying this. I have used my
main form. I have used a query based on that form. The combo
looks great. It adds names as I enter more people into the database.
All that works. I just cannot click on the name showing in the box or
hit Enter or do anything which would bring up the desired person's
record. I have tried entering the code in OnClick, OnDoubleClick and
OnEnter. Nothing at all happens. In my previous attempt at this post, I
mentioned an error that has repeated itself in several of my attempts
at coding this section to work.

Here is one of the setups I tried:
Private Sub Combo727_AfterUpdate()
'Move to the record selected in the control
Me.RecordsetClone.FindFirst "[PatientID] = " & Me![Combo727]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

The error occurs at the Me.Bookmark=Me.RecordsetClone.Bookmark line
in this setup. Other setups have the error listed as Me.Bookmark =
rs.Bookmark, I think, but this is by memory only. Also, PatientID is not
my primary key though it IS an Autonumber. My PK is PatientChartNumber,
also a unique number which cannot be duplicated. I have tried this setup
with PatientChartNumber, also to no avail. I wanted to use PatientChartNumber
to begin with. PatientID is only after I ran out of ideas.

Stupid question here, but, let's say I compacted the database and restarted
the Automatic Number over again in the database, would this prevent me from
pulling up the desired records which were entered before the restart?

If the previous post to you pops up here after all, my apologies to everyone
for
the duplication.

Susan

In your forms design view, (make sure the "wizord" is selected), place a new
combo box onto the form- the wizord will ask you what you want to do, select
the "i want the combo box to lookup a record", take it from there...

You guys have been wonderfully helpful to me in keeping me on the right track
with my

[quoted text clipped - 23 lines]

Susan

 

[Susan K via AccessMonster.com]

I have tried it again using PatientChartNumber . This is the code I
used this time:

Private Sub Combo729_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PatientChartNumberID] = " & Str(Me![Combo729])
Me.Bookmark = rs.Bookmark
End Sub

This time the error IS Me.Bookmark=rs.Bookmark. Looks like my
memory didn't fail me earlier. A msg box popped up which said
"No current record." But the record IS in the database. Btw, I use
Access 2000, in case I forgot to mention it.

Susan

 

[Chris B via AccessMonster.com]

Hi Susan,
im still rather new at this too, so if anyone disagrees with me please let us
know so that I too can learn..
You need to do the "compact & Repair" often when making many changes to a
database, see if that fixes some of the the problems,

Copy and paste this in the After update of the combobox729.
..........
Private Sub Combo729_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PatientChartNumberID] = " & Str(Nz(Me![Combo729], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
.......................
Is it an option to have a search text box vs a combo lookup box, ie pt chart
number manually typed in, i was wondering how many pt's you deal with, may be
easier to search via a text box?

If it is an option then you could try this code from one of my databases,
make a textbox and change to suit your application...

Private Sub Text69_AfterUpdate()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

rst.FindFirst "[Case Number] = '" & Me.Text69 & "'"

If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
MsgBox "Case Number not found"

End If
Set rst = Nothing
End Sub

regards
Chris

I have tried it again using PatientChartNumber . This is the code I
used this time:

Private Sub Combo729_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PatientChartNumberID] = " & Str(Me![Combo729])
Me.Bookmark = rs.Bookmark
End Sub

This time the error IS Me.Bookmark=rs.Bookmark. Looks like my
memory didn't fail me earlier. A msg box popped up which said
"No current record." But the record IS in the database. Btw, I use
Access 2000, in case I forgot to mention it.

Susan

 

[Susan K via AccessMonster.com]

Chris,

I gave up on the combo box because I know the group I am doing this
for must have hundreds of patients since it is also a research facility,
so I figured they would know the patient's name and/or chart number
when the patient comes in---so they could type in either into a textbox.
I plugged in the code in the AfterUpdate area and the patient chart
number entered into the correct field on the form, but a msgbox popped
up saying "Data type mismatch in criteria expression." When I plugged it
in the OnEnter area, I did get the PCN too but without the warning..but
still no record was pulled up in either case. It is as if my records are
locked
away and I can never get access to them again to look at and/or edit. The
records ARE in the database and I can pull all of them up for use in the
Reports I have created. I just cannot seem to be able to pull them up in the
Form format. I know at one point a couple of weeks ago I was able to cycle
through the records after they were entered, but I haven't been able to do so
since then. And too much has been done between then and now for me to
retrace my steps and try to figure it out.

Susan

Hi Susan,
im still rather new at this too, so if anyone disagrees with me please let us
know so that I too can learn..
You need to do the "compact & Repair" often when making many changes to a
database, see if that fixes some of the the problems,

Copy and paste this in the After update of the combobox729.
.........
Private Sub Combo729_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PatientChartNumberID] = " & Str(Nz(Me![Combo729], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
......................
Is it an option to have a search text box vs a combo lookup box, ie pt chart
number manually typed in, i was wondering how many pt's you deal with, may be
easier to search via a text box?

If it is an option then you could try this code from one of my databases,
make a textbox and change to suit your application...

Private Sub Text69_AfterUpdate()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

rst.FindFirst "[Case Number] = '" & Me.Text69 & "'"

If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
MsgBox "Case Number not found"

End If
Set rst = Nothing
End Sub

regards
Chris

I have tried it again using PatientChartNumber . This is the code I
used this time:

[quoted text clipped - 14 lines]

Susan

 

[Susan K via AccessMonster.com]

Ok, I took care of the "Data mismatch" warning with the AfterUpdate
code, but I still cannot pull up the desired record. Do I have something
locked unknowingly? Btw, I compact the DB pretty often. Never have come
up with any repairs that I know of. Plus I run the DB through the "analyze
performance", etc. now and then. No real problems there.

Susan

 

[Susan K via AccessMonster.com]

[Banging head against keyboard]. Ok, now I have access to the
other records (whew)....it was a dumb operator error. But now
I once again get the data mismatch error, even though I put the
textbox in a general number format to match the Patient Chart
Number field.

Susan

 

[Susan K via AccessMonster.com]

Chris,

Thank you so much for your help! I got it all worked out now! Have had
a very fruitful day with the DB today!

Susan

 

[Chris B via AccessMonster.com]

Glad it worked out, am curious to know where the problem lay....