DoCmd.OpenForm Arguments


I have got an unbound access form, and in this form I gather 2 dates (i.e. through txt fields). Once I have these fields, I want to open a form that has one list box on it. This list box should populate based on the dates that I gather on the previous form. How do I specify this through the DoCmd.OpenForm arguments? Currently I have the following code, but it is not working:

Form 1:
Dim whereClause As String
whereClause = "SELECT * FROM qryInvoice WHERE tblInv.InvDate Between #" & txtStartDate & "# And #" & txtEndDate & "#" & ";"
DoCmd.OpenForm "frmInvoiceFax", acNormal, , , , , whereClause

Form 2:
public Sub Form_Load(args As String)
MsgBox args
lstInvoice.Rowsource = whereClause
End Sub

I know I am not doing it the right way (because it is not working), but I can't actually find how to do it. Help!

View Replies


DoCmd.OpenForm Troubles

I have a form(frmGetRecord) with a subform(frmSubGetRecord). frmGetRecord has a combobox cmbCNO to choose the patient. The subform displays the admit date and discharge date(if there is one). There can be multiple admit/Discharge dates for a patient. Based on the values from fields on the form and subform, I would like to open a data entry form(frmEvents) for the particular record. I have a command button to run the code to open frmEvents. I am having trouble with the criteria in the where condition.

stLinkCriteria = "[CNo]=" & "'" & Me![cmbCNO] & "'" _
And "[AdmitDate]=" & "#" & Forms![frmGetRecord]![frmSubGetRecord].Form![AdmitDate] & "#" _
And "[DischDate]=" & "#" & Forms![frmGetRecord]![frmSubGetRecord].Form![DischDate] & "#"

DoCmd.OpenForm stDocName, , stQryName, stLinkCriteria, acFormEdit

When I click on the command button I get a type mismatch error. If I test the StLinkCriteria separately, the DoCmd.OpenForm will work for just the CNO field or just the AdmitDate field. If I test those two strings together, I get the type mismatch. Also, DischDate could be blank and I'm not sure how to add an IIf statement to the string to test for that in the where statement. I'm also not sure how to make sure it picks the correct record if there is more than one admit date. I would like to select the desired date record, then have the command button open the appropriate record.

Hope this makes sense. Thanks for any help.

View Replies View Related

DoCmd.OpenForm - Not Filtering Properly

I am having trouble getting the where condition on the DoCmd.OpenForm to work correctly. I have created a combo box on a form to select which file name to open the main form and to filter on but when I run it, it doesn't return any records. I know these records exist because the combo box is using a field to select distinct values from the same table. The current code I have is :

Private Sub ReactiveSelection_Change()
DoCmd.OpenForm "Reactive Form", acNormal, , "FILENAME" = Forms![Reactive File Search]![ReactiveSelection]
Me.ReactiveSelection = ""
End Sub

where "FILENAME" refers the field in the form that is used to select the variables in the combo box and also the field in the form which the query is supposed to run on. I hope this makes sense and someone can help with this!

View Replies View Related

Docmd.openform With Date Criteria Problem

Hi all,

Have a problem trying to open a form with certain dates.

I have the following information:-

Learn_ID -Text
Provi_ID - Text
Lprog_ID - Text
PlannedDate - Date/Time (Short Date Format)
ReviewType - Text


The above fields are unbound text boxes which retrieve the information from a listbox listReviews.

I have a command button which when I click opens frmEditReview using the following code:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FrmEditReview"
stLinkCriteria = "[Learn_id] = '" & Me![txtLearn_id] & "'" & " " & " and [provi_id] = '" & Me![txtProvi_id] & "'" & " " & " and [lprog_id] = '" & Me![txtLprog_id] & "'" & " " & " and [ReviewType] = '" & Me![txtType] & "'" & " " & " And [PlannedDate]= #" & CDate(Me![txtPlanned]) & "#"

DoCmd.OpenForm stDocName, , , stLinkCriteria

If I try to open a record which includes a date such as 15/03/2005 then their is no problem, but if I try to open a record with something like 01/02/2005 then it will not open and opens a blank form. It only happens with dates where the dd part (Format dd/mm/yyyy) is less than or equal to 12.

Does anyone have any ideas?

Thanks in advance for any help.


View Replies View Related

Docmd Openform Question - Version: 2003 (11.0)

I am using this command to open a form to a specific record determined by a user input. This works pretty well, however, if there is not a match to my "where condition" it still opens the form but to a "random" record.

how can i let the user know that no matches were found instead of just opening up a random record?

View Replies View Related

DoCmd.OpenForm Fails If Access Is Not The Active Window

I have an Access 2000 application which, on startup, always launches a 'switchboard' form and sometimes gets information from the user during the startup. Both forms are opened with VBA using DoCmd.OpenForm; the switchboard opens as acNormal and the input form (when used) as acDialog.

Under normal circumstances this works fine.

If, however, the Access window ceases to be the active window in Windows (e.g. the user selects another application's window in the taskbar) the forms do not open (all other VBA code runs correctly).

Any ideas?


View Replies View Related

DoCmd.OpenForm Where Clause Not Always Working - Version: 2003 (11.0)

I have a bit of code that works on my computer but not on my client's computer, which makes no sense to me. The problem is the "where" portion of the DoCmd.OpenForm method.

I have a master class list form that has subforms for all the class lists. I want to be able to open a student's record when I double-click on their name in one of the class lists. So on the double-click event, I set up code that feeds the subform name to a public function. Here's my code (with the error handling removed):

Private Sub Contact_Name_DblClick(Cancel As Integer)
OpenFormContacts ("ClassListMondayAM")
End Sub

Public Sub OpenFormContacts(strSubform As String)
DoCmd.OpenForm "Contacts", , , "[ContactID] = Forms!ClassListMaster!" & strSubform & "!ContactID"
End Sub

As I said, this works great on my computer, but not on my client's computer. It works if I "hard-code" a ContactID, like this:
DoCmd.OpenForm "Contacts", , , "[ContactID] = 36"

And it also works if I "hard-code" the subform name, like this:
DoCmd.OpenForm "Contacts", , , "[ContactID] = " & Forms!ClassListMaster!ClassListMondayAM!ContactID

Interestingly, if I rearrange the quotes in the statement above, it works on my computer but not my client's:
DoCmd.OpenForm "Contacts", , , "[ContactID] = Forms!ClassListMaster!ClassListMondayAM!ContactID"

So I thought maybe I'd leave the quotes out of the form reference:
DoCmd.OpenForm "Contacts", , , "[ContactID] = " & Forms!ClassListMaster! & strSubform & !ContactID

But then I get an error when I try to compile. I even tried this:

Dim strField As String
strField = "Forms!ClassListMaster!" & strSubform & "!ContactID"
DoCmd.OpenForm "Contacts", acNormal, , "[ContactID] = " & strField

Again, it works on my computer but not my client's.

So I guess I have two questions:
1. Why in the world does this work on one computer and not on another?
2. What can I do to get around this?

By the way, both computers are using Access 2003 and are current with all their Windows Updates. Also, the references are the same on both computers, no missing references. Thanks in advance for any insights!

View Replies View Related

DoCmd.OpenForm Question, Hiding A Form - Version: 2000 (9.0)

How's this? Code:Private Sub CalendarButton_Click()
Me.Visible = False
End Sub

View Replies View Related

Enhancing Interface And DoCmd.Quit Vs DoCmd.Close - Version: 2000 (9.0)

I would like to know how others handle the users interface (and testing mode) for a situation.

I'm on a mission to give the users an "efficient" interface. (Clean, intuitive, minimal clutter, etc.) And I like uncluttered code - if it doesn't really do anything, remove it.

Inherited an Access file to print Shipping labels, accesses our ERP database.
It has one form with text boxes to collect information and two buttons: Print and Exit.
When they click the [Exit] button the file closes - DoCmd.Quit. When I'm testing I like to change it to DoCmd.Close (This is a new technique for me. I have started doing this on my files that generate reports).

First thought: Do I really need the [Exit] button when the [Close] box will suffice. What are your thoughts about the pros and cons?

Things aren't going as expected while in "test" mode. I have DoCmd's to .Quit / .Close in both the Exit button sub and Form_Close. When I click to exit (either [Exit] or [Close Box] the file closes - I have to close Access and re-open the file. I've tried different combinations of .Quit and .Close in the two subs, but, can't get a combination to merely stop everything, and leave me with the database objects window in tact.

Here's coding:

Code: Private Sub cbExit_Click() <- Exit button on the form.

On Error Resume Next

Dim lRetVal As Long
lRetVal = Shell("C:Program FilesSeagullBartender 7.10Professionalartend.exe /X")
Me.Visible = False
'Quit when live, Close when testing

End Sub

Code: Private Sub Form_Close()

On Error Resume Next

Dim lRetVal As Long
lRetVal = Shell("C:Program FilesSeagullBartender 7.10Professionalartend.exe /X")
Me.Visible = False

'Quit when live, Close when testing

End Sub

Help. :-)

View Replies View Related

How To Process The Result Of A Docmd.openquery Or Docmd.runsql C - Version: 2003 (11.0)

I would like to select rows from tables be either using the docmd.openquery or docmd.runsql command.

I know how to perform this part of the puzzle, but how do I set up my code to process the result of this command?

I need to be able to capture data and later update it


View Replies View Related

DoCmd.Close Versus Unload (also Qs About DoCmd In General) - Version: 2007 (12.0)

Hello again fellow forumers,

It’s been a little over a week since I last picked up Access and VBA and I’m happy to report that I’m progressing quite nicely.

My only concern is a fear of becoming overly dependent on the functions/macros/actions (forgive the incorrect terminology) that are exclusive to MS Access 2007. Since I have no prior VB programming experience and am new to Windows IDEs, I am not able to immediately differentiate between what some might call ‘vanilla’ VB functions/code and functions that work only in Access. Furthermore, the (slight?) differences between VBA, VB6, and VB.NET do confuse me a little.

This is important for me because I am aiming for portability of code. I’ll get specific. Let’s start with this ‘DoCmd’ thing. Is this ‘DoCmd’ part of VBA or just Access-only? All I know is that I’ve been using ‘DoCmd’ for opening and closing forms, primarily. I’ve been learning from the Help file without any other reference, and it seems to encourage the use of ‘DoCmd’ for just about everything. As far as I understand it, ‘DoCmd’ and the ‘domain functions’ (plus a good lot of other stuff I’m sure) isn’t available outside of VBA or access. What then, are the VB equivalents for these actions? (Not all, just DoCmd.openform and DoCmd.close to begin with)

So my question for today, really… is just “How do I close and open forms without the use of ‘DoCmd’?” In addition, what are the main differences between using say, ‘DoCmd.openform’ and ‘Load’?

I have tried using ‘Load’ and ‘Unload’ but I am having problems with the syntax.
The statement I am trying to replace is:

DoCmd.Close acForm, "MainMenu", acSaveNo

I have tried:

Unload MainMenu
Unload “MainMenu”
Unload forms(“MainMenu”)


None of them work, which is to say they produce an error message of one kind or another.
I now turn to this forum for very much appreciated pointers.
And thanks for your patience!

View Replies View Related

Help With Openform

I am trying to open a second form that is linked to the first form but i cant get it to work it keeps coming up with a syntex error

DoCmd.OpenForm "frmstudentperformancepoorprevious", , , popUPID = '" & Me.StudentPerformanceID & "'"

Any ideas?? trying everything to get this form to link and everything is failing even the wizard which works if you just enter the record... but if you go back to it later and use the button it brings up a blank record

View Replies View Related



Im very new with access, and require a little help

what im trying to do is the following

I require a open form button, i can do this with no problems...

When the form opens, i need it to lookup a REP_ID value in a table to see if any records exist for the REP, if there is, i need it to display the form, with details.

If there are no details for REP_ID in the table, i require the form to open just blank, so input can be added!

How do i do this? Do i do it via code, macro?

Any help would be greatly appreciated

View Replies View Related

Pass Arguments To Access


I want to be able to pass arguments to an access file on start up. I want to be able to grab this value and then perform an action based on this.

The program will send an email with details for a change request, the recipient will then need to click on a link to accept or reject this change. So I want them to be able to click the link which will trigger the program to save accept or reject.


View Replies View Related

Open Form Using 2 Arguments.

I'm putting together a database of property clients and i have almost finnished but as always i seem to get so very close to the end of a project and i hit a wall.

background:- I have about 5 different tables which mostly have relationships but there is one main table (clientDetails) which holds the main record this has a very predictable clientID primary Key. the other tables have equally predictable keys for the tables ie: LettingID for a letting client insuranceID for insurance clients etc.. the deal is really that EVERYONE is a client so they get the main details filled out and another record in another table using the primary key of the main table in all other tables. one client can have many other records relating to them useing the primary key.

The Problem:- I have made lots of nice forms that all link up together so you can edit and brose all of the records in the database, and this works very well.. however my problem comes when i want to add a new record in one table that relates to the same record in another using the forms not the table view. eg. on form 1 (clientDetails) i add a new client to the database all fine even using the autonum for the PK. now i bring up the record of that client and i have a button (addProperty) this brings up another form (propertyDetails) using the PK from the previous. this works fine if there is already an entry in the DB but if not then the PK is autonumbered..

What i need:- I need to be able to open up a form using PK then (addProperty) button to the next form which finds that record using the pk (clientID) and FK (propertyID) and if there is no FK relating to the PK then to autonumber and use that... so i basically transfer the PK from form 1 to form 2 regarless of any other data relating to it, and auto numbering to the next number in that table for the FK.


form1 form 2
------- > ---------
PK=100 PK=100

this is probibly really basic.. but i hope someone can help ..

Many thanks. = is me..

View Replies View Related


I'm going nuts here...can you privide some help.

I'm trying to QUERY an ACCESS database called ARQUIVO and i would like to have from the column EMPRESAS all the DISTINCT records that have the same 'aviacao' in the INDUSTRIA column.

and it goes like this:
Code:<%Set rse = Server.CreateObject("ADODB.Recordset")sSQL = "SELECT DISTINCT empresa FROM arquivo WHERE industria='Aviacao'" sSQL,con, adOpenStatic, adLockPessimistic, adCmdText%>

But all I get in a 500 error.

The fact is that if I use "*" instead of "empresa" the query runs but i list all the records with 'aviacao' in INDUSTRIA

I have this SQL query made in ACCESS - that does what I want - but it doesn't rune in mine ASP page.

FROM arquivo
WHERE (((arquivo.INDUSTRIA)="Câmaras Municipais"));

Would anyone help on this?

View Replies View Related

Wrong Number Of Arguments

Can you help me? I get the error wrong type of arguments in my function as follows
Private Sub Command0_Click()
Call ANewDBWithPass("c:BELest", "secret")
End Sub

Function ANewDBWithPass(ByVal tName As String) '// adds the current date to the name of the database
Dim wsp As Workspace
Dim db2 As Database
Set wsp = DBEngine.Workspaces(0)
If Right(tName, 4) = ".mdb" Then
' Remove .mdb from the name
tName = Left(tName, Len(tName) - 4)
End If
' Add date and extension
tName = tName & Format(Date, "dd-mm-yyyy") & ".mdb"
' Create database
',Set db2 = wsp.CreateDatabase(tName, dbLangGeneral)
Set db2 = wsp.CreateDatabase(tName, dbLangGeneral & ";pwd=" & strPassword)
End Function

View Replies View Related

Openform With Criteria

I open a form with a criteria set but when the form opens it has every record, rather than restricting the records to those matching the criteria. I wonder if anyone has had this problem before? I use a query in the called to get the underlying recordset.

This is my code:

Dim CritText As String

CritText = "[ID1] = '" & Me.ID1 & "'"
DoCmd.OpenForm "frmTestAll", acFormDS, , stCritText

View Replies View Related

OpenForm Filter

Hi All,

I have a problem and I can't figure out what I am doing wrong, I have a code:

Private Sub Button4_Click()
Dim compl As String
compl = name1
DoCmd.OpenForm "form1", , , "Field1=" & name1
DoCmd.Close acForm, "form2"
End Sub

Now everytime I click on this button and I enter a value in textbox(name1) it askes for a parameter! This is the only field in the form (form1) where I have this problem by the way. With the other fields it doesn't ask for a parameter. Does anyone know what might be the problem?

View Replies View Related

OpenForm Filter

Access 2003
I'm sure this is incredibly simple, but I can't make it work, and can't find an answer in the forums.

:confused: I want to use OPENFORM to open a form showing only the records where a CHECKBOX is checked. I've tried using a query, using WHERE, using the OpenArgs, etc., but I'm clearly not formatting it right.

so... elements are:
btnActiveJobs (command button)
frmJob (form with job records)
chkActive (check box on frmJob that shows whether job is active)

The underlying Jobs table sets the Active field to yes/no

I'd like to do all this in the event code for the button, but I do also have a query (Query1) that produces the right jobs. Including this as the FilterName criterion has no effect.

View Replies View Related

Error: Too Many Arguments Specified - Version: 2003 (11.0)

Not sure if this is the right forum for this but I didn't see one for ADP files. I am trying to execute a simple select query in an ADP file via code and I’m getting an error message saying the sproc has “too many arguments specified”. It’s a very simple query and I don’t see what I’m doing wrong. "Com" is my command object variable and "Con" is my connection object variable, both of which are working fine. Thanks!


With Com
.ActiveConnection = Con
.CommandType = adCmdStoredProc
.CommandText = "dbo.MyProcedureName"
.Parameters.Append .CreateParameter("@Parameter", adVarChar, adParamInput, 9, Trim(FormField.Value))
End With
Set MyRecordset = Com.Execute


CREATE PROCEDURE dbo.MyProcedureName

@Parameter varchar(9)


FROM dbo.Table
WHERE (Field = @Parameter)


View Replies View Related

MsgBox Arguments - Version: 2003 (11.0)

I have the following code at the end of a module

Btn = MsgBox("Report Saved!" & vbCrLf & vbCrLf & _
"Do you wish to Open the Text Files?", vbYesNo, _
"Report Output Results")

Shell "Notepad.exe C:Report_Data.txt", vbMaximizedFocus

It opens the text file just fine with Notepad. However if you click no it still goes ahead and opens the Text files.

What can i do in terms of an If statement to Cancel Opening the file with the Shell command if the user clicks no?


View Replies View Related

Passing Arguments To Another Database - Version: 97 (8.0)

Is it possible to pass a variable to another database?

Some of my links to external databases would work a lot better if I could filter out records as the database opens, so I was wondering if this is possible, if so can someone please point me to an example.


View Replies View Related

The OpenForm Action Was Cancelled

I am trying to open a form with a specified record using double clicked event (list box) but access is throwin "The OpenForm action was cancelled". My code is:

Private Sub lstSchool_DblClick(Cancel As Integer)
Dim test As String
Dim thisForm As String

thisForm = "frmRegister"
test = "ID = " & "'" & lstSchool & "'"

DoCmd.OpenForm thisForm, , , test

End Sub

ID is a AutoNumber field. I have used this code in my other dbz and it works fine ....

View Replies View Related

Openform At Specific Record (again)

Sorry about this. I know there is loads on here about this but I just can't get it to work.

I have a basic table of dates.
One form based on the dates via a query

When the form opens, I want it to show today's date but allowing access to all the other date records (so no filter).

Any help please

View Replies View Related

Command Line Arguments To Open AND Close Db

Is there a command line argument to open AND close a db? I am running a schedule task to open a db which has an autoexec macro but I don't want the db to stay open after running the macro. Or is there a close db function in a macro? I see close form but not db.

View Replies View Related

Wrong Number Of Arguments - Version: 2.0 (not 2000)

i get the error "wrong number of arguments or invalid proprty assignment"

Private Function KillObject(strDbName As String, acObjectType As Long, strObjectName As String, StrPassword As String)
StrPassword = "secret"
Dim db As DAO.Database
Dim adb As Object
Set adb = CreateObject("Access.Application")
adb.OpenCurrentDatabase strDbName, , StrPassword
adb.DoCmd.DeleteObject acObjectType, strObjectName
Set adb = Nothing
End Function

Private Sub Command2_Click()
Call KillObject(GPath, 0, "products", "secret")

View Replies View Related

OpenReport Arguments Problem - Version: 2003 (11.0)

Hi all

I am having a problem here with a DoCmd.OpenReport issue, I am trying to use the OpenArgs method to open a report for a specific record on a form.

Basically I have a Main Form frmPatient
a subform frmActivityDetails Subform
and a field on the subform called ActivtyID

I have a cmd button on the sunform which when clicked opens the report "rptDwnGradeLetterSelect" in preview, it is supposed to open it showing the details for the field ActivityID, but it doesnt it opens the report OK but for all records not just the one I am on and want.


Private Sub cmdDwnGrdLtr_Click()
Dim strCriteria As String
strCriteria = Me.ActivtyID

DoCmd.OpenReport "rptDwnGradeLetterSelect", acViewPreview, , , acWindowNormal, "[ActivtyID] =" & strCriteria
End Sub

on checking the code as it runs, it picks up the correct Id in strCriteria, just the report does not use it as an OpenArg.

Any ideas?



View Replies View Related

Wrong Number Of Arguments. - Version: 2002 (10.0) XP

Error message that I received was wrong number of Arguments or invalid property assignment
What I'm trying to do is prevent entries were the trailer number and PO are the same. First time doing something like this. Any help or guidance will be great.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.TrailerNumber.Value And Me.PO
stLinkCriteria = "[TrailerNumber] And [PO] =" & "'" & SID & "'"
'check table for duplicate Distro values

If DCount("TrailerNumber", "PO", "logi00_r_VCMTrailer", stLinkCriteria) > 0 Then
'Undo duplicate entry
'Message box "Warning Trailer Number"
MsgBox "Warning Trailer and PO Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "Please use Search by Previous Comments by Trailer Number to update original comment.", vbInformation _
, "Duplicate Information"

End If

Set rsc = Nothing
End Sub

View Replies View Related

Wrong Number Of Arguments - Version: 2000 (9.0)

Can you help me? I get the error wrong type of arguments in my function as follows
Private Sub Command0_Click()
Call ANewDBWithPass("c:BELest", "secret")
End Sub

Function ANewDBWithPass(ByVal tName As String) '// adds the current date to the name of the database
Dim wsp As Workspace
Dim db2 As Database
Set wsp = DBEngine.Workspaces(0)
If Right(tName, 4) = ".mdb" Then
' Remove .mdb from the name
tName = Left(tName, Len(tName) - 4)
End If
' Add date and extension
tName = tName & Format(Date, "dd-mm-yyyy") & ".mdb"
' Create database
',Set db2 = wsp.CreateDatabase(tName, dbLangGeneral)
Set db2 = wsp.CreateDatabase(tName, dbLangGeneral & ";pwd=" & strPassword)
End Function

View Replies View Related

Passing Arguments In Hyperlinks - Version: 2003 (11.0)

I am trying to hyperlink two Data Access pages. On the first one I have a list of File numbers and I want to add a button next to each one so that the user can click it and go to another Data Access Page with more detailed information on the file.

I can add the hyperlink to the page, but, I can't figure out how to pass the filenumber so it opens in the next page.

I am also trying to do the same thing with an email and I want the Filenumber to be placed in the subject line automatically.

This is so easy to do in access, I don't understand why I can't do the same thing in the DAP

Thank You

View Replies View Related

How To Pass These Arguments To My Function?? - Version: 2002 (10.0) XP

I want to create a generic function whose purpose is to search a particular supplied value from a particular table field so that the function can be used in any search form.

for this the function should accept 4 arguments.
1.)a string argument which needs to be searched
2.)The form argument(ie the form name) in which the search string is provided
3.)The table argument which says the table to be searched
4.)the field argument in the table to be searched.

So my qs is can I pass arguments like this to a function and create my UDF accessible to any kind of search form??
please provide some lines of code for better understanding.

Please help.

View Replies View Related

Passing Variables In An OpenForm Method

I can't seem to get the following code to work.
Any help would be appreciated:

Public Function NonConform(strProduct, strBatch As String)

On Error GoTo HandleErr
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProductNonConforming"
stLinkCriteria = "[ProductName]=" & "'" & strProduct & "'"
stLinkCriteria = stLinkCriteria & "AND [BatchNum]=" & "'" & strBatch & "'"
DoCmd.OpenForm stDocName, , , (stLinkCriteria)
MsgBox "Error in NonConform Function : " & Err.Description
Resume Next
End Function

View Replies View Related

Copyrights 2005-15, All rights reserved