UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Opening A Record In VBA, Access 2013    
 
   
waitingroomz
post Feb 6 2019, 04:09 PM
Post#1



Posts: 93
Joined: 29-October 18



I am trying to Reference a record through a procedure but cannot get it correct.

The cmd button procedure on the form with the active record is:

CODE
Private Sub Command0_Click()

Dim oPatient As Variant

oPatient = Me.cboPatient
'Me.cboPatient is a Combo Box Lookup
Call TestPatient(oPatient)

End Sub


The Module Procedure is
CODE
Public Sub TestPatient(oPatient As Variant)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM tblPatients WHERE ID='" & oPatient & "'")

MsgBox "Looking at " & rs!PatientName

End Sub


Thoughts on how to reference the active record for viewing in a message box or using it for other stuff?
Go to the top of the page
 
theDBguy
post Feb 6 2019, 04:14 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,578
Joined: 19-June 07
From: SunnySandyEggo


Hi. Do you really need to open a recordset? If the form has the same record source, then you could just simply use it's recordset.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
waitingroomz
post Feb 6 2019, 08:20 PM
Post#3



Posts: 93
Joined: 29-October 18



For the purposes of what I will be using the Call procedure for, it would be much easier to grab a snapshot of the record.
Go to the top of the page
 
tina t
post Feb 6 2019, 08:29 PM
Post#4



Posts: 6,152
Joined: 11-November 10
From: SoCal, USA


QUOTE
I am trying to Reference a record through a procedure but cannot get it correct.

what happens when you run your code - do you get an error msg? or unexpected results in the msgbox?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
theDBguy
post Feb 6 2019, 09:35 PM
Post#5


Access Wiki and Forums Moderator
Posts: 76,578
Joined: 19-June 07
From: SunnySandyEggo


QUOTE
For the purposes of what I will be using the Call procedure for, it would be much easier to grab a snapshot of the record.
If this means the form where you're making the "call" is closed before or right after you make the call, then it makes sense. Otherwise, you can still "call" the procedure without using a recordset object because the "snapshot" of the record is also on the calling form (at least I think it is - maybe it's not - I can't see your form to know what it contains).

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
waitingroomz
post Feb 7 2019, 08:49 AM
Post#6



Posts: 93
Joined: 29-October 18



@tina t

It gives me a datatype mismatch error.

@theDBguy

Yes, the form will be closed right after the call.

I am going to build out a new form this morning that will have a temp list of IDs from a table for a Loop call function, which is what the idea of being able to reference the record directly (maybe via snapshot) to print x number of items through word.
Go to the top of the page
 
theDBguy
post Feb 7 2019, 11:01 AM
Post#7


Access Wiki and Forums Moderator
Posts: 76,578
Joined: 19-June 07
From: SunnySandyEggo


Hi.
QUOTE
Yes, the form will be closed right after the call.
You can certainly work harder, if you're so inclined. But to make your life easier, you don't have to "close" the form just yet. You can simply "hide" it for the meantime. Once you've got the information you wanted, then you can close it.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Feb 7 2019, 02:15 PM
Post#8



Posts: 6,152
Joined: 11-November 10
From: SoCal, USA


QUOTE
@tina t

It gives me a datatype mismatch error.

QUOTE
OpenRecordset("SELECT * FROM tblPatients WHERE ID='" & oPatient & "'")

okay. what's the data type of field ID in tblPatients?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th November 2019 - 05:26 PM