Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







DoCmd.OpenForm Arguments


Hi,

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 Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
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
Hi,
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:-

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

FrmReviewList
txtLearn_ID
txtProvi_ID
txtLProg_ID
txtPlanned
txtType

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.

Taff.

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?

Simon

View Replies !   View Related
Pass Arguments To Access
Hello,

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.

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

Regards,
Todd

View Replies !   View Related
Open Form Using 2 Arguments.
Hi.
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.

eg.

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

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

Many thanks.

chiller149@gmail.com = is me..

View Replies !   View Related
DISTINCT And WHERE Arguments In SQL ASP Query
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'" rse.open 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.

SELECT DISTINCT arquivo.EMPRESA, arquivo.INDUSTRIA
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)
db2.Close
End Function

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
OpenForm
Hello,

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

View Replies !   View Related
UDFs That Accept Arguments To Process UPDATE/INSERT INTO SQL Stmts?
Before attempting to create a VBA User Defined Function that will accept arguments for creating UPDATE/INSERT INTO SQL statements, I thought I would check to see if some already exist. It seems like a very tough task to tackle. I'd like it to determine the data type of the the values being placed into the specified fields and subsequently provide the appropriate syntax. (i.e. '" & mString & "', #" & mDate & "#, etc.) Do any exist?

I'm working with MS Access 2003.

Thanks,

Steve G.
:confused:

View Replies !   View Related
UDFs That Accept Arguments To Process UPDATE/INSERT INTO SQL Stmts?
Before attempting to create a VBA User Defined Function that will accept arguments for creating UPDATE/INSERT INTO SQL statements, I thought I would check to see if some already exist. It seems like a very tough task to tackle. I'd like it to determine the data type of the the values being placed into the specified fields and subsequently provide the appropriate syntax. (i.e. '" & mString & "', #" & mDate & "#, etc.) Do any exist?

I'm working with MS Access 2003.

Thanks,

Steve G.

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)
HandleErr:
MsgBox "Error in NonConform Function : " & Err.Description
Resume Next
End Function

View Replies !   View Related
Access VBA: OpenForm To A Specific Record
I am not a specialist, but am trying to create an application with Access 2000to manage our company’s orders. I have a table called ‘Service’ containing descriptive data on the type of service requested. This table is linked to several other tables, with ‘ServiceNo’ as the primary key linking most of them. The service number is not an autonumber, but a counter incremented through code, for specific reasons that I won’t develop here.

I have created two forms: one to consult all the service records (F_Service_Consult), with a button to open another form that allows the user to add a new service record (F_Service_Add).

When the new service form opens, the service number counter is incremented and this number is assigned as the new 'ServiceNo' in a new record added to the ‘Service’ table (using the SQL ‘Insert’ command). I would like this form (F_Service_Add) to open on this record only. In the code that creates ‘ServiceNo’, the correct value is contained in a variable called ‘lngServiceCount’. I have tried using this code to open my form:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "F_Service_Add"
stLinkCriteria = "Forms![F_Service_Add]![ServiceNo]='" & lngServiceCount & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

When I run the code step-by-step, the correct value is shown for ‘lngServiceCount’ when the mouse hovers over the variable (for ex. ‘155’). When the form opens, I have 1 record (Filtered), but nothing shows in the ‘ServiceNo’ text box.

I have deactivated the code that is in the ‘OnCurrent’ event procedure of this form to avoid any interference.

There are many sub-forms on the main form, which also contain the ‘ServiceNo’ in text boxes.

When I use this command to open the form:

DoCmd.GoToRecord , , acLast

the form opens correctly, with the correct ‘ServiceNo’, but ALL the records are opened.

The variable type for ‘lngServiceCount’ is a long integer. (I hope that is the correct term in English, because I am using a French version of Access).

I would be very grateful if anyone could tell me what I’m doing wrong. I’ve tried to be clear, but if more information is required, I would of course provide what’s missing.
Thanks in advance.

View Replies !   View Related
Where Condition Of OpenForm Command Not Working
The scenario is... also apologies if this seems a lot. Simple really.

An edit button on a subform is to open a separate form for editing, and I obviously want to apply a where condition to display the relevant record. So I have

DoCmd.OpenForm "editReviewPEST", acNormal, , ReviewID=Me.ReviewID, acFormEdit, acWindowNormal, False

The Record Source for editReviewPEST uses an INNER JOIN...

SELECT Reviews.Year, Reviews.ReviewID, Reviews.LesseeID, Lessee.DateOfOrigSanction, Reviews.Exposure, Reviews.ExpoCurrency, Reviews.SentToCredit, Reviews.Approved, Reviews.Completed, Reviews.Comments, Reviews.LevelOfLastSanction, Reviews.CreditManager, Reviews.TypeOfReview, Reviews.DateOfLastCreditReview, Reviews.DateOfNextCreditReview FROM Lessee INNER JOIN Reviews ON Lessee.LesseeID = Reviews.LesseeID WHERE (((Reviews.LesseeID)=[Forms]![Reviews]![SelectLessee])) ORDER BY Reviews.ReviewID DESC;

So I get the form to open with all the records relvant to the lessee, but to filter it further to exact ReviewID, I thought I could include ReviewID=Me.ReviewID (an Autonumber PK) as the Where Condition to the DoCmd.OpenForm. Instead I get a form which looks like Add Record Form.

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved