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
3140 Views

Similar Articles

[PageSpeed] 45

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:

Can DoCmd.RunSQL be Done Quietly?
When running an action query using the DoCmd.RunSQL function, can the resulting message(s), such as "You are about to delete...." be avoided? -- croy You can either bracket it with the SetWarnings command: DoCmd.SetWarnings False DoCmd.RunSQL DoCmd.SetWarnings True or use the Execute method of the database object: CurrentDb.Execute "yourquery" The second method has the advantage of being faster and you can trap errors. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? F...

DoCmd.OpenForm command is not working as should.
I have this form with about 4 comand buttons on it but 1 of them is not working the way it should. Private Sub cmdChangeVendor_Click() DoCmd.Close acForm, Me.Name DoCmd.OpenForm "Selection Vendor and Reviewer", acNormal End Sub Private Sub cmdMainMenu_Click() DoCmd.Close acForm, Me.Name DoCmd.OpenForm "Invoice Database Launch", acNormal End Sub cmdMainMenu_Click works fine but cmdChangeVendor_Click doesn't work. "Selection Vendor and Reviewer" doesn't open, nothing open. but when I run it using the Toggle Breakpoint everything works the way ...

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...

Setting the primary key when using DoCmd.TransferDatabase acLink
Hi, I've created a module to create link tables to my Oracle database. The module is very simple as follows: Public Function CREATE_LINK_TABLE(ODBC_SCHEMA, ODBC_SOURCE_TABLE, ODBC_LOCAL_TABLE, DSN, UID, PWD) On Error Resume Next DoCmd.RunSQL "DROP TABLE " & ODBC_LOCAL_TABLE DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=" & DSN & ";UID=" & UID & ";PWD=" & PWD & ";SERVER=myserver", acTable, ODBC_SCHEMA & "." & ODBC_SOURCE_TABLE, ODBC_LOCAL_TABLE, False, True ...

Docmd.SendObject To: Cc: Bcc: Fields
Where I work at we still use Access 97. Nothing I can do about that. Anyways it appears the To: Cc: and Bcc: Line of the Docmd.SendObject have a max string length. Only 2/3 of my concatenated string transferred into the To: Line. I assume there is nothing I can do about it, but figured I'd ask. On Mar 3, 3:31=A0pm, Apexstar <Apexs...@discussions.microsoft.com> wrote: > Where I work at we still use Access 97. Nothing I can do about that. > Anyways it appears the To: Cc: and Bcc: Line of the Docmd.SendObject have= a > max string length. Only 2/3 of my concatenated...

Help with Docmd.runsql please.
Hi, I am trying to insert a record into a table based on inputted info from a form using the code builder. Code is as follows: Dim Sql As Text Dim TT As Text TT = IIF([Text1]="Prefault","Yes","No") SQL = "INSERT INTO MyTable ([Answer]) VALUES (TT)" DoCmd.Runsql SQL The above code does not work. When it is executed I get an input box asking me to insert a parameter for TT. I have tried replacing TT with [TT]and 'TT' and get the same result. I have also tried replacing TT with "TT" which actually inserts the text "TT" into th...

Use DoCmd.SendObject to send Select Query
Is this possible? Here is my code but it does nothing at all: DoCmd.SendObject acSendQuery, "QueryName", acFormatXLS, "user@email.com", , , "Subject", "Some Text in email message.", False This works when I make a table and acSendTable, but it doesn't do anything when it's a select query, not even an error message. I'm using Access 2000. Thanks. ...

DoCmd.OpenReport suddenly failing, macro OpenReport succeeds
This is weird. This database has worked ok for years, now suddenly in some VBA code a DoCmd.OpenReport is failing, the error handler there doesn't get hit, but the macro that called that VBA function gets an Action Failed in RunCode. If I open the report manually it succeeds, if I open it directly from a macro it succeeds. At least a couple of different places this happens. Anyone else seen this? One more thing, it only is a problem on one machine (WinXP SP2), others WinXP SP2 machines are still ok. And if the VBA code does a RunMacro which prints the report that works, but if the ...

Multiple instances of Outlook when executing multiple DoCmd.SendObject commands
Hello! The routine below works great when I send only one email. But when sending another email, I have to kill Outlook via Task Manager where it is immediately replaced by another session of Outlook and Outlooks again comes up. Any help or suggestions appreciated! Rbollinger ========================================================= Sub Send SendEmail() On Error GoTo Err_Command11_Click Dim stDocName, mTO, mCC, mSubject As String stDocName = "frmSnapShot" mTO = Forms!frmEmail!emailTO mCC = Forms!frmEmail!emailCC mSubject = Forms!frmEmail!Subject mpath =...

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 DoMenuItem
I have two forms and I am using the following code for deleting a selected record but it works in one form but not in other. Any help will be greatly appreciated. DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, A_SELECTRECORD_V2, , A_MENU_VER20 DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, A_DELETE_V2, , A_MENU_VER20 Thanks. Do yourself a huge favour, and stop using DoMenuItem. The help file for Access 97 includes this comment "the DoMenuItem method has been replaced by the RunCommand method. The DoMenuItem method is included in this version of Microsoft Access only for compatibility with...

DoCmd.FindRecord...How to use to match a field value
When I open a form, I would like Access to go to a specified record that meets the criteria in a certain field using the 'DoCmd.FindRecord' command in VBA. Unfortunately I have not had much success this way. Can you explain how I can find a record based on the contents of a field on the form. Below is my VBA code. Private Sub Workouts_Click() Dim stfilter As String Dim stFormName As String Dim getdateid As Integer stFormName = "Datelog" getdateid = Me!Workouts.Value stfilter = "[athleteid]=" & Me![Athleteid] DoCmd.Close, acform...

How do I use a text box control value in DoCmd.TransferSpreadsheet
I have a form with a button and 4 text boxes on it, the button has the following click event: Private Sub cmdImportFiles_Click() GetExcelFileData Me End Sub I want to loop through the text boxes on the, which are going to contain file paths, and do DoCmd.TransferSpreadsheet to import excel files into my database. I am have some problems with this, especially with "ctrl.Value" in the following code. Function GetExcelFileData(frmForm as Form) Dim ctrl as Control For Each ctrl In frmForm If TypeName(ctrl) = "TextBox" And ctrl.Name="txtInscope" Then ...

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.TransferDatabase acExport file name problem
Windows XP SP2 Access 2007 I am trying to export query results to dbase 5.0 format. It exports fine but somehoe it truncates the file name to 8 charaters. It used to work fine but since I changed the computer I am having this probleb. Can anyone please help me? Here is my syntax: DoCmd.Transferdatabase acExport, "dBase 5.0", CurrentProject.Path, acTable, "myTable", "LongDBFFileName.dbf" It creates the export and save as LongDBFF.DBF Hi, yes, this is a limitation of dbase isam driver, but you can first export to a short name and then rename the file us...

Report Filter In DoCmd.PrintOut ?
Hi, I am using the following code to print out to a PDF writer . . DoCmd.SelectObject acReport, "Invoice", True DoCmd.PrintOut My problem is that the Invoice report is normally called with a filter to display a single invoice. When I attempt to print using the above code, it prints all right, but it prints out EVERY record when I only want a single record. How do I pass filter parameters to the report, so that it only prints the invoice which I am interested in and not ALL of the invoices? Also, my user would like it to print directly without first prev...

Access Version and "DoCmd.RunCommand acCmdDatabaseSplitter"
HiI know that you can use DoCmd.RunCommand acCmdDatabaseSplitter only inAccess Version XP and Higher.So I have created following code:If sAccVersion => "10.0" Then DoCmd.RunCommand acCmdDatabaseSplitter Else MsgBox "........" Exit SubEnd IfSome users user Acc2000 and some XP.The users with Acc2000 have of course problems with the lines of code above,because Acc2000 can not read:DoCmd.RunCommand acCmdDatabaseSplitterSo my question is:Is it possible to use acCmdDatabaseSplitter in my code without gettingproblems with the users who use Acc2000.Thanks for the hel...

Strabge Problem With DoCmd.OutputTo When Generating a PDF File.
Access 2007 I have the following code: On error goto lblError <setupt variable> DoCmd.OutputTo acOutputReport, vRptName, acFormatPDF, vfilename, False msgbox "Report " & vfilename & " saved." <other code> lblExit: <cleanup code> Exit sub lblError: msgbox "Error: " & err.description resume lblExit end sub The report that is being output has an OnOpen event handler that I wanted to walk through. I put a breakpoint on the DoCmd.OuputTo statement and then hit F8. The report was output without steping through the co...

Delete Records based on DoCmd.RunSQL
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...

BorderStyle and DoCmd.OpenForm
Hello, I have an Access 2002 form that has been designed with the properties Pop Up=Yes, Modal=Yes, Border Style=Sizable, Min Max Buttons=Both Enabled. This form is called from another form with the following syntax: DoCmd.OpenForm "frmPopup", , , , acFormReadOnly, acDialog I need to open the form using the acDialog parameter to prevent the code following the OpenForm command firing too early. It is only when "frmPopup" is closed by the user that control should come back to the calling form. However, in using acDialog I lose the ability to be able to re-size the pop- ...

DoCmd.ApplyFilter problem
I have an OnClick event that does the following: DoCmd.ApplyFilter , "[TicketNo] = " & Me!GetTicket TicketNo is a column in my bound table and GetTicket is a textbox on the form. When I enter a value in the textbox and click the command button, the code works and I get the requested recond on the form. When I call the command button Click() procedure from other code, however, I get a prompt for parameter [TicketNo], the ApplyFilter is failing. I have moved a valid value to GetTicket control. Does anyone have a clue why this is happening? -- Dorian "Give someone...

Dates with day below ten aren't found with docmd.applyfilter state
Filter fails: the DoCmd.ApplyFilter , "[Date] =Forms![Games Today]![ViewDate]" does not work if the ViewDate field has a date selected where the day value is below 10. Works fine for dates 10 and above. The filter statement appears to be valid and shows as eg. [Date] = #02/12/2009# if script is halted. (I wouldn't use date as a field name nowadays, but this is a very old dbase). The program was written in access 97 and works fine there. Converted to MsAccess 2007 that links to an access 97 data file. PC is running windows 7. When first loaded none of the dat...

DoCmd.Close
Hi, I use a dialog form to bring up a report and I'd like to close the form automatically once the report is brought up. The below code doesn't work. What's the solution? TIA Private Sub Comman2_Click() DoCmd.OpenReport "Test", acViewPreview DoCmd.Close End Sub -------------------------------------------------------------------------------- Estoy utilizando la versi�n gratuita de SPAMfighter para usuarios privados. Ha eliminado 2895 correos spam hasta la fecha. Los abonados no tienen este mensaje en sus correos. �Pruebe SPAMfighter gratis ya! You probably need to sp...

The Docmd in this IF/Then Statement will not work
I am using the below coding and can not figure out why the "Else" DoCmd will not work. Debug shows no errors yet if "NO" is clicked in the message box the DoCmd.Close fails to work. Any idea why? 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 Curtis wrote: >I am using the below coding and can not figure out why the "...

Using DoCmd.SendObject
Hi, My default e-mail client is outlook2007 and configured to connect to an Exchange server.Is there any possibility of DoCmd.SendObject open Vista Windows Mail instead of Outlook 2007? If no, can someone tell me another procedure to do that. Thanks CJA Tony Toews has some good info here about various approaches to sending email from Access: http://www.granite.ab.ca/access/email.htm -- 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. "CJA" <car...