DoCmd.SearchForRecord

Can anyone tell me what is wrong with the following statement?

DoCmd.SearchForRecord acDataForm, DetailForm, acFirst, WhereCond

DetailForm = "frmDynDfltsDetail"   frmDynDfltsDetail is a form that is 
currently open.

WhereCond = "kcoSinkTable = "PsgrService" AND kcoSinkField = "LoadFactor" 
AND kcoSourceTable = "Options" AND kcoSourceField = "PsgrLoadFactor" "

kcoSinkTable, kcoSinkField, kcoSourceTable, and kcoSourceField are Combo 
Boxes in frmDynDfltsDetail.

I get no error message when the DoCmd.SearchForRecord runs; it just doesn't 
seem to be able to find the specified record.  I suspect the problem is 
something simple but I can't see it.




0
Utf
12/24/2009 3:49:01 AM
access.modulesdaovba 1670 articles. 0 followers. Follow

3 Replies
2551 Views

Similar Articles

[PageSpeed] 48

Hi Chuck,
I think it's your where condition that doesn't work.
If PsgrService, LoadFactor etc. are constant put them between simple 
quotations i.e.
WhereCond = "kcoSinkTable = 'PsgrService' AND kcoSinkField = 'LoadFactor' 
AND kcoSourceTable = 'Options' AND kcoSourceField = 'PsgrLoadFactor'"
If they are variable you have to concatenate them to the where cond with 
ampersand i.e.
WhereCond = "kcoSinkTable = " & PsgrService & " AND kcoSinkField = " & 
LoadFactor & " AND kcoSourceTable = " & Options & " AND kcoSourceField = " & 
PsgrLoadFactor

HTH, Merry Xmas Paolo

"Chuck" wrote:

> Can anyone tell me what is wrong with the following statement?
> 
> DoCmd.SearchForRecord acDataForm, DetailForm, acFirst, WhereCond
> 
> DetailForm = "frmDynDfltsDetail"   frmDynDfltsDetail is a form that is 
> currently open.
> 
> WhereCond = "kcoSinkTable = "PsgrService" AND kcoSinkField = "LoadFactor" 
> AND kcoSourceTable = "Options" AND kcoSourceField = "PsgrLoadFactor" "
> 
> kcoSinkTable, kcoSinkField, kcoSourceTable, and kcoSourceField are Combo 
> Boxes in frmDynDfltsDetail.
> 
> I get no error message when the DoCmd.SearchForRecord runs; it just doesn't 
> seem to be able to find the specified record.  I suspect the problem is 
> something simple but I can't see it.
> 
> 
> 
> 
0
Utf
12/24/2009 7:59:01 AM
Thanks for the help, Paolo, but your suggestion didn’t quite do it.  The 
problem turned out to be use of form Control names instead of table column 
names in the WhereCond string.  For example, where I used kcoSinkTable (the 
name of a Key COmbo box), I should have used SinkTable (the name of the Combo 
Box’s ControlSource).  When I made that change in the generation of the 
WhereCond string, the SearchForRecord did what I was expecting.

What finally put me on the right track was reading (for the thousandth 
time!) the line in the Remarks section of the SearchForRecord Help that says: 
“You can refer to fields that are in the record source of a form or report 
but are not displayed on the form or report. In the preceding example, 
neither Description nor CategoryID must be displayed on the form or report 
for the criteria to work.”

The “… can refer to…” part is misleading in that it makes this sound 
optional; the “…neither…must be displayed” part is what really suggested the 
solution to me but it is still a bit ambiguous.  There should be a line that 
says something like, “Regardless of the ObjectType argument, the 
WhereCondition argument must refer to fields in the underlying table.”


"Paolo" wrote:

> Hi Chuck,
> I think it's your where condition that doesn't work.
> If PsgrService, LoadFactor etc. are constant put them between simple 
> quotations i.e.
> WhereCond = "kcoSinkTable = 'PsgrService' AND kcoSinkField = 'LoadFactor' 
> AND kcoSourceTable = 'Options' AND kcoSourceField = 'PsgrLoadFactor'"
> If they are variable you have to concatenate them to the where cond with 
> ampersand i.e.
> WhereCond = "kcoSinkTable = " & PsgrService & " AND kcoSinkField = " & 
> LoadFactor & " AND kcoSourceTable = " & Options & " AND kcoSourceField = " & 
> PsgrLoadFactor
> 
> HTH, Merry Xmas Paolo
> 
> "Chuck" wrote:
> 
> > Can anyone tell me what is wrong with the following statement?
> > 
> > DoCmd.SearchForRecord acDataForm, DetailForm, acFirst, WhereCond
> > 
> > DetailForm = "frmDynDfltsDetail"   frmDynDfltsDetail is a form that is 
> > currently open.
> > 
> > WhereCond = "kcoSinkTable = "PsgrService" AND kcoSinkField = "LoadFactor" 
> > AND kcoSourceTable = "Options" AND kcoSourceField = "PsgrLoadFactor" "
> > 
> > kcoSinkTable, kcoSinkField, kcoSourceTable, and kcoSourceField are Combo 
> > Boxes in frmDynDfltsDetail.
> > 
> > I get no error message when the DoCmd.SearchForRecord runs; it just doesn't 
> > seem to be able to find the specified record.  I suspect the problem is 
> > something simple but I can't see it.
> > 
> > 
> > 
> > 
0
Utf
12/27/2009 3:32:01 AM
Hi Chuck,

Yes, the expressions to search much be in the RecordSource ;)

if you use the .RecordsetClone.FindFirst  method to search, you do not have 
to worry about the form being active because it is specified.

in the header of almost every main form, I create one or more combos to Find 
a record.  The display part of the combo does not have to be more than a few 
characters -- however many you think they might need to see when they use the 
TypeAhead to find an item in the list.

If you have filter controls, rather than filtering the form, filter the Find 
combos

Make one or more unbound (no ControlSource) combos on your form (like in the 
header).  Let the first column be invisible and be the primary key ID of the 
RecordSource of your form and then, on its AfterUpdate event, put this in the 
[Event Procedure] code:
 
FindRecord
 
this code goes behind the form:
 
'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()
 
   'if nothing is picked in the active control, exit
   If IsNull(Me.ActiveControl) Then Exit Function
 
   'save current record if changes were made
   If me.dirty then me.dirty = false
 
   'declare a variable to hold the primary key value to look up
   Dim mRecordID As Long
 
   'set value to look up by what is selected
   mRecordID = Me.ActiveControl
 
   'clear the choice to find
   Me.ActiveControl = Null
 
   With Me
      'find the first value that matches
      .RecordsetClone.FindFirst "SomeID = " & mRecordID
 
     'if a matching record was found, then move to it
      If Not .RecordsetClone.NoMatch Then
         .Bookmark = .RecordsetClone.Bookmark
      End If
   End With
  
End Function
 
'~~~~~~~~~~~~~~~~~~~~
where
- SomeID is the Name of the primary key field, which is in the RecordSource 
of the form -- assuming your primary key is a Long Integer data type 
(autonumbers are long integers)
 
Remember that the Rowsource for a combo can come from anywhere -- it can 
pull from multiple tables or only use one ... just make sure that the first 
column is the primary key ID of the table you want to search (and that field 
is part of the RecordSource for the form you are searching). 

For instance, if your main form is People, you could have a combo to find a 
person using their phone number by linking to the Phones table (assuming your 
data is normalized) and putting the PeopleID in the first column (hidden) and 
the Phone in the column that displays.  In this case, I use a procedure to 
strip non-numeric characters so the user does not have to type parentheses or 
dashes since I store phone numbers with symbols.  The width of this column is 
set tiny so it is greater than zero, but is small enough so the user does not 
see it -- and this is the second column.  The user actually sees the third 
column -- the one with the mask symbols.
 
If you are searching the recordset on another form, change the FindRecord 
name to be specific (like FindRecord_Order) and, substitute
 
With Me --> With forms!formname
 
If the record you are looking for is on a subform, change the FindRecord 
name to be specific (like FindRecord_Whatever) and, substitute:

With Me --> with Me.subform_controlname.form

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


Warm Regards,
Crystal
 
Learn Access on YouTube
http://www.youtube.com/user/LearnAccessByCrystal

 *
   (:  have an awesome day!  :)
 *



"Chuck" wrote:

> Thanks for the help, Paolo, but your suggestion didn’t quite do it.  The 
> problem turned out to be use of form Control names instead of table column 
> names in the WhereCond string.  For example, where I used kcoSinkTable (the 
> name of a Key COmbo box), I should have used SinkTable (the name of the Combo 
> Box’s ControlSource).  When I made that change in the generation of the 
> WhereCond string, the SearchForRecord did what I was expecting.
> 
> What finally put me on the right track was reading (for the thousandth 
> time!) the line in the Remarks section of the SearchForRecord Help that says: 
> “You can refer to fields that are in the record source of a form or report 
> but are not displayed on the form or report. In the preceding example, 
> neither Description nor CategoryID must be displayed on the form or report 
> for the criteria to work.”
> 
> The “… can refer to…” part is misleading in that it makes this sound 
> optional; the “…neither…must be displayed” part is what really suggested the 
> solution to me but it is still a bit ambiguous.  There should be a line that 
> says something like, “Regardless of the ObjectType argument, the 
> WhereCondition argument must refer to fields in the underlying table.”
> 
> 
0
Utf
12/27/2009 4:42:01 PM
Reply:

Similar Artilces:

How convert Docmd.Domenuitem
I'm converting a 97 db to 2000. A form has a button that executes a Docmd.Domenuitem command. This is supposed to invoke Find and Replace. It does, but the subject is grayed out. I need the find to be based on the control where the cursor was when the button was pressed. Any ideas? Access help in 2000 does not even mention this command neither does my Access book. Maybe you need to do Screen.PreviousControl.SetFocus DoCmd.RunCommand acCmdFind or DoCmd.FindRecord On Thu, 29 Sep 2005 13:43:03 -0700, "mscertified" <rupert@tigerlily.com> wrote: >I'm converti...

Is it true about DoCmd.SendObject
That DoCmd.SendObject will find your default Mail client? -- Thanks in advance for any help with this......Bob MS Access 2007 accdb Windows XP Home Edition Ver 5.1 Service Pack 3 "Bob Vance" <rjvance@ihug.co.nz> wrote in message news:u91TwlG3KHA.4964@TK2MSFTNGP05.phx.gbl... > That DoCmd.SendObject will find your default Mail client? > > -- > Thanks in advance for any help with this......Bob > MS Access 2007 accdb > Windows XP Home Edition Ver 5.1 Service Pack 3 > > It's true if your default Mail client is Outlook :~) ...

When I close a form with DoCmd.Close I get a parameter popup
This is a repost. I will try to explain the problem a little better than I did the last time. When I execute DoCmd.Close on one of my forms I get one of those little unknown parameter popups, the kind you get when there's something wrong with your query. But all I'm trying to do is close the form, not read any data. This popup appears to be gernerated by the row source query in a list box on the form. This row source query references another text box on the form and this appears to be what the popup is asking for. But there's a value in this text box. And why should th...

Help with "Form DoCmd.Open and DoCmd.Close
The following two lines of code is not operating correctly. Private Sub cmdChangeVendor1_Click() DoCmd.Close acForm, "Invoice by Market Reviewer" DoCmd.OpenForm "Selections Vendor and Reviewer", acNormal End Sub When I click on the cmdChangeVendor1 button, I expect the form "Invoice by Market Reviewer" to close and the form "Selections Vendor and Reviewer" to open. But that is not the result I get. The form "Invoice by Market Reviewer" closes but that is about it. Nothing else opens. I have two forms that behaves this way. I have ...

docmd.runcmd SQL functions
I have a table with fields a,b,c,d. If I have a row (x) where x.d is blank, I want to search the table and see if there is a row (y) such that x.c=y.c and x.b=y.b and if y.d is not blank, then I want x.d to be filled in with y.d. I can't seem to figure out how to start this. Thanks Carl Frankly, you don't want to do this. You have an un-normalized data model and you are storing redundant data. If b and c determine d, then that should be stored *once* a record in its own table. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want ans...

DoCmd.FindRecord Error
My code has a function to find a record. The function is called by 2 separate search forms, each of which generates SQL for a query that builds a table of record keys of all qualifying recs. When there is only 1 qualifying rec, the function is called to open the primary form (Member Data) to the qualifying rec. The function uses the rec in the new results table ( a long int ) as the argument for the FindRecord. When called by 1 of the forms, it works properly. When called by the second form it fails on the DoCms.FindRecord line with Runtime error 2162: "A macro set to 1 of th...

DoCmd.OpenReport
Can someone please help me get the " and ' right for this? (PARTNUMBER is a text field). I just can't get it...argh! DoCmd.OpenReport stDocName, acPreview, , "[PARTNUMBER] =" ' "& Me.PARTNUMBER" ' " DoCmd.OpenReport stDocName, acPreview, , "[PARTNUMBER] =""" & Me.PARTNUMBER & """" -- Duane Hookom Microsoft Access MVP "Gina K" wrote: > Can someone please help me get the " and ' right for this? (PARTNUMBER is a > text field). I just can't get it...argh! >...

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8
Hello, I have vba that will import the data from Excel file. Is it possible to append the data from excel file to a table in Access in such a way that excel file does not have a field's name? When I run this transferspreadsheet command, it comes in as F1, F2,..... thanks, On Mar 3, 3:08=A0pm, "Boon" <boonyawat.la-ongth...@cnh.com> wrote: > Hello, > > I have vba that will import the data from Excel file. Is it possible to > append the data from excel file to a table in Access in such a way that > excel file does not have a field's n...

Comact & Repair using DoCmd
I would like to set somw databases to compact & repair when a form is opened (using a macro that opens the form from the task scheduler). I tried the following code on form load but it wants to create a new table. instead of running compact & repair: Private Sub Form_Load() DoCmd.DoMenuItem acForm, acTools, acDatabaseUtilities, acCompactAndRepairDatabase End Sub Any Ideas? Thanks, Alan hello you! "Alan" <Alan@discussions.microsoft.com> skrev i melding news:A470028B-2D43-49B2-B4C4-CA80C70C8B7A@microsoft.com... >I would like to set somw databases to compact &...

docmd.sendobject
Outlook 2003 does not accept message text from DoCmd.SendObject command in access database. All other fields pass to the message fine. Is there a setting in outlook 2003 that causes this? It works fine if I use Outlook 97 as my default mail program. DoCmd.SendObject , , "MSTXT", "recipient", "cc", "bcc", "subject", "Message text" ...

DoCmd.SendObject
The behavior of the DoCmd.SendObject method seems to be arbitrary in regards to which eMail client it selects to do the sending. Sometimes it selects Outlook Express, sometimes it selects Outlook. We only use the scheduling functions of Outlook through our Access app. No one here uses Outlook for eMail - we all use Outlook Express. I don't see any way in the SendObject method to specify which eMail client I want to use. Is anyone aware of a way I can force it to always select Outlook Express? Not strictly an Access Problem but, Both Outlook & Express wants to be your default ...

Filter expression passed with DoCmd
It appears that the filter expression passed to a report as a part of the DoCmd expression is not available in the Open code. I.e., Me.Filter is null. When? OnLoad? Oops, wrong group...........sorry "Bill" <billstanton@psln.com> wrote in message news:TO-dnbGyUMhI2y_anZ2dnUVZ_jydnZ2d@trueband.net... > It appears that the filter expression passed to > a report as a part of the DoCmd expression > is not available in the Open code. > > I.e., Me.Filter is null. > > When? OnLoad? > > > "Bill" <billstanton@psln.com> wrote in me...

docmd.close
I have an autoExec to call my function and verify user log in information to let user ue or close the application. I use docmd.close which only support database object like tables, forms, reports..etc. The MS Access stil open and run the main form. Is it possible to close the MS Access before open and load the main form use any other function? Your information is great appreciated, Souris, > Is it possible to close the MS Access before open and load the main form > use > any other function? Sorry, but I don't understand what you mean by that statement... but... ...

docmd.openform
Is there a way to open a form without making it visible from the start? Here's sample code to help explain: DoCmd.OpenForm "form1", acNormal Me.Visible = False Me.Combo1.RowSource = "" DoCmd.Close acForm, "Country_BT_Land_APAC", acSaveYes The minor problem with this is that the form is visible for a split second, and then becomes invisible - which looks like a flicker. Since I have to do this with 4 forms at one time, there will be 4 flickers. I'm trying to elimate the flicker problem. Thanks! -- Message posted via AccessMonster.com http://www.acces...

Docmd.Save
I'm using the following code to save my record before printing it If Me.Dirty Then Docmd.Save I'm getting an error saying I have not specified the object Do I have to specify 'table' and the table name each time I use this statement, I thought it defaulted to the current object? "mscertified" <rupert@tigerlily.com> wrote in message news:CE644884-31D0-4428-B1B0-D0E792B1F87A@microsoft.com... > I'm using the following code to save my record before printing it > > If Me.Dirty Then Docmd.Save > > I'm getting an error saying I have not spec...

Using DoCmd in an IF/Then statment
I am using the below coding, debug says there are no problems, but the Else statement DoCmd will not run. Can anyone tell me what is wrong here? Function Another_Record() MSGBOX "Do you wish to do another LRU update?", vbYesNo, "LRU UPDATE" If vbYes Then DoCmd.GoToControl "Serial Number" Else DoCmd.Close acForm, "EDIT CDS LRU ASSET" End If End Function This has been answered already about 5 posts below this. "Curtis" wrote: > I am using the below coding, debug says there are no problems, b...

DoCmd.SearchForRecord
Can anyone tell me what is wrong with the following statement? DoCmd.SearchForRecord acDataForm, DetailForm, acFirst, WhereCond DetailForm = "frmDynDfltsDetail" frmDynDfltsDetail is a form that is currently open. WhereCond = "kcoSinkTable = "PsgrService" AND kcoSinkField = "LoadFactor" AND kcoSourceTable = "Options" AND kcoSourceField = "PsgrLoadFactor" " kcoSinkTable, kcoSinkField, kcoSourceTable, and kcoSourceField are Combo Boxes in frmDynDfltsDetail. I get no error message when the DoCmd.SearchForRecord runs...

DoCmd to delete records
Hi I have a command button that when clicked deletes certain records from a subform based on an application number being equal. It deletes all records with the same applciation number as currently shown on the main form, see code below: DoCmd.RunSQL ("DELETE FROM tblAdultDetails WHERE [frmAdultDetails2].Form![ApplicationNum]=" + Str(Me.ApplicationNumber)) However I would like it to only delete records if the fields [MainApplicantFlag] =1 or [PartnerFlag] =1 as well as ApplicationNum being equal. These are both number fields btw. Can someone tell me what I should add to my code in...

DoCmd.OpenQuery
How can I use DoCmd.OpenQuery and not have it prompt that it is performing a append or delete. I would like it to perform the operation without giving me the warnings and just do it. Example in use within Access VB: DoCmd.OpenQuery "qry-append-BuildMonthlyUsage" DoCmd.OpenReport "rpt-ShowMonthlyUsage", acViewPreview Or is there another method that I should be using? Thank you in advance for your help, Michael Kintner On Wed, 7 Nov 2007 15:23:25 -0500, Michael Kintner wrote: > How can I use DoCmd.OpenQuery and not have it prompt that it is performing a >...

DoCmd.OpenReport Error
Hi , Please kindly assist. Every time I try to click the OK butto to open the report that I have created It passes an error of Run time error 2585, action cannot be carried out while processing a form or report or event. Below is the coding. in the Ok event procedure. Private Sub cmdOK_Click() Me.Visible = False 'DoCmd.OpenQuery "LicenseQueryB", acViewNormal, acEdit DoCmd.OpenReport "Torrens Valley Firearms Report B", acViewNormal, acEdit 'DoCmd.Close acForm, "License DialogB" End Sub Below is the coding for opening the Report which is call...

Docmd.Movesize
Docmd.Movesize Hello, I have the following code that I=92ve been using for several months. It=92s used to adjust the size of a form based on the number of records: ---------start code Private Sub FormResize() 'code to resize both frmMainform and sfrm Const DetailHeight =3D 240 'tried using me.detail.height but it didn't work 'remember that the constant above will depend on how tall your textbox (s)is Const FormTopMargin =3D 560 Dim lngRecordCount As Long lngRecordCount =3D Me.sfrm.Form.RecordsetClone.RecordCount '# of records 'form has Header, ...

DoCmd Code
I'm wanting to insert data, in this case and employee's name from query1, into the text of an email. Can anyone tell me how this is done. I've provide some sample code below. Many thanks! Function SendEmail() DoCmd.SendObject acSendQuery, "Query1", "HTML", "John Doe", , , "Adherence", _ "The following agent (Query1.[Employee Name]) is out of adherence" End Function you can use DLookup() but there may be a better way depending on how you are running this code. For example, how are you deriving the "John Doe" nam...

DoCmd.HourGlass not working
I've got a form with a treeview object. When I double click on a particular node in the treeview I open a second form. The second form takes awhile to populate so I want to display an hourglass cursor. I can't get the hourglass to display. What's odd is that it works from a button click event but not the treeview double click event. This code doesn't work: Private Sub TreeView1_DblClick() Call DoCmd.Hourglass(True) DoCmd.OpenForm "SecondForm" End Sub This code works: Private Sub Button_Click() Call DoCmd.Hourglass(True) DoCmd.O...

DoCmd.RunSQL
How can I get the Zero to be Null or Empty or Text? CompletedDate is a Text Field I thought "" or "Not updated" would get it but error's out DoCmd.RunSQL "update tblPayrollCompletion set CompletedDate = 0" Thanks for any help Hi Mike, I won't ask why you have a completed date as a text datatype, rather than a datetime datatype. But given that it IS a text datatype, the following will work: DoCmd.RunSQL "UPDATE tblPayrollCompletion SET CompletedDate = '0'" will set CompletedDate to the text string '0' DoCmd.RunSQL "...

DoCmd.OpenReport
Dim ReportName If Me.Action = 1 Then ReportName = "Rpt_ActivationBounty_Detail" If Me.Action = 2 Then ReportName = "Rpt_RevenueShare_Detail" If Me.Action = 3 Then ReportName = "Rpt_ClientSummary" If Me.Action = 4 Then ReportName = "Rpt_CheckRequisition" If Me.Action = 5 Then ReportName = "Rpt_ItemCodes_NONVAR_AUDIT" If Me.Action = 6 Then ReportName = "Rpt_ItemCodes_VAR_AUDIT" DoCmd.OpenReport ReportName, acViewPreview, , "Partner_ID=" & Me.Lkp The client selects a Partner from a drop-down list... Then selects which ...