Set Query Criteria from Form
Thank you Thank you Thank you for your help... Concept: I have a report and underlying query object. I would like to be able to click a button on a form that would set the criteria for a field in the query and then run the report. Of course several buttons to set different criteria... One approach was to set a text box on the form that would be populated by code and that used as the criteria in a parameter query. I found out that I couldn't pass >= in the parameter which is part of the criteria. Is there a way through code to set the criteria for a field in an existing query object?? Otherwise is there another way to solve the problem?? Thanks again, Joel
|
0 |
|
Utf
10/23/2007 12:51:02 AM
access.formscoding
7493 articles. 
0 followers.
6 Replies
1034 Views
Hi Joel
Design your report to be based on a query without parameters, listing all
possible records. Then, in your form, build a WHERE clause that is based on
the options that have been selected and pass that to the report through the
WhereCondition (4th argument) of DoCmd.OpenReport. For example:
Dim sWhere as String
Const cAND = " AND "
Const cFmtDate = "\#yyyy-mm-dd\#"
If Not IsNull(cboSelectCustomer) Then
sWhere = "[CustomerID]=" & cboSelectCustomer & cAND
End If
If Not IsNull(txtStart) Then
sWhere = sWhere & "[OrderDate]>=" & Format(txtStart, cFmtDate) & cAND
End If
If Not IsNull(txtEnd) Then
sWhere = sWhere & "[OrderDate]<=" & Format(txtEnd, cFmtDate) & cAND
End If
' add other criteria as required
If Len(sWhere) <> 0 Then
' remove the last " AND "
sWhere = Left( sWhere, Len(sWhere) - Len(cAND))
End If
DoCmd.OpenReport "rptOrders", , , sWhere
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"Joel" <Joel@discussions.microsoft.com> wrote in message
news:F0C9E6C7-3A1A-4081-9DFD-983AC417489C@microsoft.com...
> Thank you Thank you Thank you for your help...
>
> Concept: I have a report and underlying query object. I would like to be
> able to click a button on a form that would set the criteria for a field
> in
> the query and then run the report. Of course several buttons to set
> different criteria...
>
> One approach was to set a text box on the form that would be populated by
> code and that used as the criteria in a parameter query. I found out that
> I
> couldn't pass >= in the parameter which is part of the criteria.
>
> Is there a way through code to set the criteria for a field in an existing
> query object??
>
> Otherwise is there another way to solve the problem??
>
> Thanks again,
>
> Joel
|
|
0 |
|
Graham
10/23/2007 9:20:09 AM
Graham: WOW!...Thanks for the concept and specific code! I see what you code is doing. I have an example of my code below but I have some syntax problem...something about single quotes for text but I can't seem to get it right. Any suggestions?? Dim sWhere as String sWhere = "[Dept Acct Number]= (>="1601000" And <="1601215") OR (>="1601800" And <="1602316" And <>"1601920")" DoCmd.OpenReport "rptOrders", , , sWhere Thanks so much and Cheers! Joel "Graham Mandeno" wrote: > Hi Joel > > Design your report to be based on a query without parameters, listing all > possible records. Then, in your form, build a WHERE clause that is based on > the options that have been selected and pass that to the report through the > WhereCondition (4th argument) of DoCmd.OpenReport. For example: > > Dim sWhere as String > Const cAND = " AND " > Const cFmtDate = "\#yyyy-mm-dd\#" > If Not IsNull(cboSelectCustomer) Then > sWhere = "[CustomerID]=" & cboSelectCustomer & cAND > End If > If Not IsNull(txtStart) Then > sWhere = sWhere & "[OrderDate]>=" & Format(txtStart, cFmtDate) & cAND > End If > If Not IsNull(txtEnd) Then > sWhere = sWhere & "[OrderDate]<=" & Format(txtEnd, cFmtDate) & cAND > End If > ' add other criteria as required > If Len(sWhere) <> 0 Then > ' remove the last " AND " > sWhere = Left( sWhere, Len(sWhere) - Len(cAND)) > End If > DoCmd.OpenReport "rptOrders", , , sWhere > -- > Good Luck :-) > > Graham Mandeno [Access MVP] > Auckland, New Zealand > > > "Joel" <Joel@discussions.microsoft.com> wrote in message > news:F0C9E6C7-3A1A-4081-9DFD-983AC417489C@microsoft.com... > > Thank you Thank you Thank you for your help... > > > > Concept: I have a report and underlying query object. I would like to be > > able to click a button on a form that would set the criteria for a field > > in > > the query and then run the report. Of course several buttons to set > > different criteria... > > > > One approach was to set a text box on the form that would be populated by > > code and that used as the criteria in a parameter query. I found out that > > I > > couldn't pass >= in the parameter which is part of the criteria. > > > > Is there a way through code to set the criteria for a field in an existing > > query object?? > > > > Otherwise is there another way to solve the problem?? > > > > Thanks again, > > > > Joel > > >
|
|
0 |
|
Utf
10/23/2007 1:40:01 PM
sWhere = "[Dept Acct Number]>='1601000' And [Dept Acct Number]<='1601215') OR ([Dept Acct Number]>='1601800' And [Dept Acct Number]<='1602316' And [Dept Acct Number]<>'1601920')" or sWhere = "[Dept Acct Number] BETWEEN '1601000' And '1601215') OR (([Dept Acct Number] BETWEEN '1601800' And '1602316') And [Dept Acct Number]<>'1601920')" Note that it's necessary to repeat the field name for each specific check. Note, too, that I changed all of the double quotes inside the string to single quotes. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Joel" <Joel@discussions.microsoft.com> wrote in message news:36B02072-0087-42B3-82D2-E936C6878295@microsoft.com... > Graham: > > WOW!...Thanks for the concept and specific code! > > I see what you code is doing. > > I have an example of my code below but I have some syntax > problem...something about single quotes for text but I can't seem to get > it > right. Any suggestions?? > > Dim sWhere as String > > sWhere = "[Dept Acct Number]= (>="1601000" And <="1601215") OR > (>="1601800" > And <="1602316" And <>"1601920")" > > DoCmd.OpenReport "rptOrders", , , sWhere > > Thanks so much and Cheers! > > Joel > > "Graham Mandeno" wrote: > >> Hi Joel >> >> Design your report to be based on a query without parameters, listing all >> possible records. Then, in your form, build a WHERE clause that is based >> on >> the options that have been selected and pass that to the report through >> the >> WhereCondition (4th argument) of DoCmd.OpenReport. For example: >> >> Dim sWhere as String >> Const cAND = " AND " >> Const cFmtDate = "\#yyyy-mm-dd\#" >> If Not IsNull(cboSelectCustomer) Then >> sWhere = "[CustomerID]=" & cboSelectCustomer & cAND >> End If >> If Not IsNull(txtStart) Then >> sWhere = sWhere & "[OrderDate]>=" & Format(txtStart, cFmtDate) & cAND >> End If >> If Not IsNull(txtEnd) Then >> sWhere = sWhere & "[OrderDate]<=" & Format(txtEnd, cFmtDate) & cAND >> End If >> ' add other criteria as required >> If Len(sWhere) <> 0 Then >> ' remove the last " AND " >> sWhere = Left( sWhere, Len(sWhere) - Len(cAND)) >> End If >> DoCmd.OpenReport "rptOrders", , , sWhere >> -- >> Good Luck :-) >> >> Graham Mandeno [Access MVP] >> Auckland, New Zealand >> >> >> "Joel" <Joel@discussions.microsoft.com> wrote in message >> news:F0C9E6C7-3A1A-4081-9DFD-983AC417489C@microsoft.com... >> > Thank you Thank you Thank you for your help... >> > >> > Concept: I have a report and underlying query object. I would like to >> > be >> > able to click a button on a form that would set the criteria for a >> > field >> > in >> > the query and then run the report. Of course several buttons to set >> > different criteria... >> > >> > One approach was to set a text box on the form that would be populated >> > by >> > code and that used as the criteria in a parameter query. I found out >> > that >> > I >> > couldn't pass >= in the parameter which is part of the criteria. >> > >> > Is there a way through code to set the criteria for a field in an >> > existing >> > query object?? >> > >> > Otherwise is there another way to solve the problem?? >> > >> > Thanks again, >> > >> > Joel >> >> >>
|
|
0 |
|
Douglas
10/23/2007 2:04:27 PM
Thanks Douglas: (Added a ( after the first quote)... Works fine! Joel "Douglas J. Steele" wrote: > sWhere = "[Dept Acct Number]>='1601000' And [Dept Acct Number]<='1601215') > OR ([Dept Acct Number]>='1601800' > And [Dept Acct Number]<='1602316' And [Dept Acct Number]<>'1601920')" > > or > > sWhere = "[Dept Acct Number] BETWEEN '1601000' And '1601215') OR (([Dept > Acct Number] BETWEEN '1601800' > And '1602316') And [Dept Acct Number]<>'1601920')" > > Note that it's necessary to repeat the field name for each specific check. > Note, too, that I changed all of the double quotes inside the string to > single quotes. > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "Joel" <Joel@discussions.microsoft.com> wrote in message > news:36B02072-0087-42B3-82D2-E936C6878295@microsoft.com... > > Graham: > > > > WOW!...Thanks for the concept and specific code! > > > > I see what you code is doing. > > > > I have an example of my code below but I have some syntax > > problem...something about single quotes for text but I can't seem to get > > it > > right. Any suggestions?? > > > > Dim sWhere as String > > > > sWhere = "[Dept Acct Number]= (>="1601000" And <="1601215") OR > > (>="1601800" > > And <="1602316" And <>"1601920")" > > > > DoCmd.OpenReport "rptOrders", , , sWhere > > > > Thanks so much and Cheers! > > > > Joel > > > > "Graham Mandeno" wrote: > > > >> Hi Joel > >> > >> Design your report to be based on a query without parameters, listing all > >> possible records. Then, in your form, build a WHERE clause that is based > >> on > >> the options that have been selected and pass that to the report through > >> the > >> WhereCondition (4th argument) of DoCmd.OpenReport. For example: > >> > >> Dim sWhere as String > >> Const cAND = " AND " > >> Const cFmtDate = "\#yyyy-mm-dd\#" > >> If Not IsNull(cboSelectCustomer) Then > >> sWhere = "[CustomerID]=" & cboSelectCustomer & cAND > >> End If > >> If Not IsNull(txtStart) Then > >> sWhere = sWhere & "[OrderDate]>=" & Format(txtStart, cFmtDate) & cAND > >> End If > >> If Not IsNull(txtEnd) Then > >> sWhere = sWhere & "[OrderDate]<=" & Format(txtEnd, cFmtDate) & cAND > >> End If > >> ' add other criteria as required > >> If Len(sWhere) <> 0 Then > >> ' remove the last " AND " > >> sWhere = Left( sWhere, Len(sWhere) - Len(cAND)) > >> End If > >> DoCmd.OpenReport "rptOrders", , , sWhere > >> -- > >> Good Luck :-) > >> > >> Graham Mandeno [Access MVP] > >> Auckland, New Zealand > >> > >> > >> "Joel" <Joel@discussions.microsoft.com> wrote in message > >> news:F0C9E6C7-3A1A-4081-9DFD-983AC417489C@microsoft.com... > >> > Thank you Thank you Thank you for your help... > >> > > >> > Concept: I have a report and underlying query object. I would like to > >> > be > >> > able to click a button on a form that would set the criteria for a > >> > field > >> > in > >> > the query and then run the report. Of course several buttons to set > >> > different criteria... > >> > > >> > One approach was to set a text box on the form that would be populated > >> > by > >> > code and that used as the criteria in a parameter query. I found out > >> > that > >> > I > >> > couldn't pass >= in the parameter which is part of the criteria. > >> > > >> > Is there a way through code to set the criteria for a field in an > >> > existing > >> > query object?? > >> > > >> > Otherwise is there another way to solve the problem?? > >> > > >> > Thanks again, > >> > > >> > Joel > >> > >> > >> > > >
|
|
0 |
|
Utf
10/23/2007 2:39:02 PM
Oops, you're right. That's required too. Sorry about that! -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Joel" <Joel@discussions.microsoft.com> wrote in message news:7AC0A341-9F66-429C-8631-042616FCC2C9@microsoft.com... > Thanks Douglas: > > (Added a ( after the first quote)... Works fine! > > Joel > > "Douglas J. Steele" wrote: > >> sWhere = "[Dept Acct Number]>='1601000' And [Dept Acct >> Number]<='1601215') >> OR ([Dept Acct Number]>='1601800' >> And [Dept Acct Number]<='1602316' And [Dept Acct Number]<>'1601920')" >> >> or >> >> sWhere = "[Dept Acct Number] BETWEEN '1601000' And '1601215') OR (([Dept >> Acct Number] BETWEEN '1601800' >> And '1602316') And [Dept Acct Number]<>'1601920')" >> >> Note that it's necessary to repeat the field name for each specific >> check. >> Note, too, that I changed all of the double quotes inside the string to >> single quotes. >> >> -- >> Doug Steele, Microsoft Access MVP >> http://I.Am/DougSteele >> (no e-mails, please!) >> >> >> "Joel" <Joel@discussions.microsoft.com> wrote in message >> news:36B02072-0087-42B3-82D2-E936C6878295@microsoft.com... >> > Graham: >> > >> > WOW!...Thanks for the concept and specific code! >> > >> > I see what you code is doing. >> > >> > I have an example of my code below but I have some syntax >> > problem...something about single quotes for text but I can't seem to >> > get >> > it >> > right. Any suggestions?? >> > >> > Dim sWhere as String >> > >> > sWhere = "[Dept Acct Number]= (>="1601000" And <="1601215") OR >> > (>="1601800" >> > And <="1602316" And <>"1601920")" >> > >> > DoCmd.OpenReport "rptOrders", , , sWhere >> > >> > Thanks so much and Cheers! >> > >> > Joel >> > >> > "Graham Mandeno" wrote: >> > >> >> Hi Joel >> >> >> >> Design your report to be based on a query without parameters, listing >> >> all >> >> possible records. Then, in your form, build a WHERE clause that is >> >> based >> >> on >> >> the options that have been selected and pass that to the report >> >> through >> >> the >> >> WhereCondition (4th argument) of DoCmd.OpenReport. For example: >> >> >> >> Dim sWhere as String >> >> Const cAND = " AND " >> >> Const cFmtDate = "\#yyyy-mm-dd\#" >> >> If Not IsNull(cboSelectCustomer) Then >> >> sWhere = "[CustomerID]=" & cboSelectCustomer & cAND >> >> End If >> >> If Not IsNull(txtStart) Then >> >> sWhere = sWhere & "[OrderDate]>=" & Format(txtStart, cFmtDate) & >> >> cAND >> >> End If >> >> If Not IsNull(txtEnd) Then >> >> sWhere = sWhere & "[OrderDate]<=" & Format(txtEnd, cFmtDate) & >> >> cAND >> >> End If >> >> ' add other criteria as required >> >> If Len(sWhere) <> 0 Then >> >> ' remove the last " AND " >> >> sWhere = Left( sWhere, Len(sWhere) - Len(cAND)) >> >> End If >> >> DoCmd.OpenReport "rptOrders", , , sWhere >> >> -- >> >> Good Luck :-) >> >> >> >> Graham Mandeno [Access MVP] >> >> Auckland, New Zealand >> >> >> >> >> >> "Joel" <Joel@discussions.microsoft.com> wrote in message >> >> news:F0C9E6C7-3A1A-4081-9DFD-983AC417489C@microsoft.com... >> >> > Thank you Thank you Thank you for your help... >> >> > >> >> > Concept: I have a report and underlying query object. I would like >> >> > to >> >> > be >> >> > able to click a button on a form that would set the criteria for a >> >> > field >> >> > in >> >> > the query and then run the report. Of course several buttons to set >> >> > different criteria... >> >> > >> >> > One approach was to set a text box on the form that would be >> >> > populated >> >> > by >> >> > code and that used as the criteria in a parameter query. I found >> >> > out >> >> > that >> >> > I >> >> > couldn't pass >= in the parameter which is part of the criteria. >> >> > >> >> > Is there a way through code to set the criteria for a field in an >> >> > existing >> >> > query object?? >> >> > >> >> > Otherwise is there another way to solve the problem?? >> >> > >> >> > Thanks again, >> >> > >> >> > Joel >> >> >> >> >> >> >> >> >>
|
|
0 |
|
Douglas
10/23/2007 4:05:48 PM
Thanks for jumping in, Doug. It was 3am in this part of the world! :-) G. "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message news:O2Z3i2XFIHA.3548@TK2MSFTNGP06.phx.gbl... > sWhere = "[Dept Acct Number]>='1601000' And [Dept Acct Number]<='1601215') > OR ([Dept Acct Number]>='1601800' > And [Dept Acct Number]<='1602316' And [Dept Acct Number]<>'1601920')" > > or > > sWhere = "[Dept Acct Number] BETWEEN '1601000' And '1601215') OR (([Dept > Acct Number] BETWEEN '1601800' > And '1602316') And [Dept Acct Number]<>'1601920')" > > Note that it's necessary to repeat the field name for each specific check. > Note, too, that I changed all of the double quotes inside the string to > single quotes. > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "Joel" <Joel@discussions.microsoft.com> wrote in message > news:36B02072-0087-42B3-82D2-E936C6878295@microsoft.com... >> Graham: >> >> WOW!...Thanks for the concept and specific code! >> >> I see what you code is doing. >> >> I have an example of my code below but I have some syntax >> problem...something about single quotes for text but I can't seem to get >> it >> right. Any suggestions?? >> >> Dim sWhere as String >> >> sWhere = "[Dept Acct Number]= (>="1601000" And <="1601215") OR >> (>="1601800" >> And <="1602316" And <>"1601920")" >> >> DoCmd.OpenReport "rptOrders", , , sWhere >> >> Thanks so much and Cheers! >> >> Joel >> >> "Graham Mandeno" wrote: >> >>> Hi Joel >>> >>> Design your report to be based on a query without parameters, listing >>> all >>> possible records. Then, in your form, build a WHERE clause that is >>> based on >>> the options that have been selected and pass that to the report through >>> the >>> WhereCondition (4th argument) of DoCmd.OpenReport. For example: >>> >>> Dim sWhere as String >>> Const cAND = " AND " >>> Const cFmtDate = "\#yyyy-mm-dd\#" >>> If Not IsNull(cboSelectCustomer) Then >>> sWhere = "[CustomerID]=" & cboSelectCustomer & cAND >>> End If >>> If Not IsNull(txtStart) Then >>> sWhere = sWhere & "[OrderDate]>=" & Format(txtStart, cFmtDate) & >>> cAND >>> End If >>> If Not IsNull(txtEnd) Then >>> sWhere = sWhere & "[OrderDate]<=" & Format(txtEnd, cFmtDate) & cAND >>> End If >>> ' add other criteria as required >>> If Len(sWhere) <> 0 Then >>> ' remove the last " AND " >>> sWhere = Left( sWhere, Len(sWhere) - Len(cAND)) >>> End If >>> DoCmd.OpenReport "rptOrders", , , sWhere >>> -- >>> Good Luck :-) >>> >>> Graham Mandeno [Access MVP] >>> Auckland, New Zealand >>> >>> >>> "Joel" <Joel@discussions.microsoft.com> wrote in message >>> news:F0C9E6C7-3A1A-4081-9DFD-983AC417489C@microsoft.com... >>> > Thank you Thank you Thank you for your help... >>> > >>> > Concept: I have a report and underlying query object. I would like >>> > to be >>> > able to click a button on a form that would set the criteria for a >>> > field >>> > in >>> > the query and then run the report. Of course several buttons to set >>> > different criteria... >>> > >>> > One approach was to set a text box on the form that would be populated >>> > by >>> > code and that used as the criteria in a parameter query. I found out >>> > that >>> > I >>> > couldn't pass >= in the parameter which is part of the criteria. >>> > >>> > Is there a way through code to set the criteria for a field in an >>> > existing >>> > query object?? >>> > >>> > Otherwise is there another way to solve the problem?? >>> > >>> > Thanks again, >>> > >>> > Joel >>> >>> >>> > >
|
|
0 |
|
Graham
10/23/2007 8:55:08 PM
Similar Artilces:
Footnote in a form
I need to add footnotes in a locked form. The sections where I need footnotes are unprotected when I tyr to include a footnote the footnote is greyed out. I am using Office 2002 and 2003 Thank you Footnotes are just another feature that is unavailable in protected documents (even in unprotected sections). In order to insert a footnote, you will have to unprotect the form. -- Stefan Blom Microsoft Word MVP "FMini" <FMini@discussions.microsoft.com> wrote in message news:E62C2081-7B07-4D75-8114-A1A754FE7CDB@microsoft.com... >I need to add footnotes in ...
How to send form data to tables programatically
Is there a way to programatically have a form update to the underlying tables? What I mean is... As a user types into a form, Access doesn't seem to actually send that data to the table (and thereby create autonumbers) until user moves on to another record. Is there a way to force that data to the tables (ie make the new records) while the user is still on the form? On Sun, 30 Dec 2007 11:33:02 -0800, markmarko <markmarko@discussions.microsoft.com> wrote: >Is there a way to programatically have a form update to the underlying tables? > >What I mean is... As a user ...
settings tab
New system setup, and I set myslef up a the 1st and only user so far. Using the Web-based interface, I don't see the settings tab for configuring the system. What am I missing? If you have installed the Outlook client for CRM, you can not have Outlook open at the same time as the web interface, or you won't be able to view the Settings area (there's a workaround for this which you can find on the newsgroup). Also, once you can access your user record, open it and deselect "Restricted Access." -- Matt Wittemann http://icu-mscrm.blogspot.com "leec" wro...
How to set maximizing window for Excel?
Does anyone have any suggestions on how to maximize the excel window whenever I open excel? I have set Excel application's property into maximizing window, but I get many excel files and shortcuts, which property are set into normal mode. Does anyone have any suggestions on how to open in maximizing windown for Excel? Thanks in advance for any suggestions Eric Eric expressed precisely : > Does anyone have any suggestions on how to maximize the excel window whenever > I open excel? > I have set Excel application's property into maximizing window, but I get ...
Completing field on subform from command button on main form
This is a simplistic version of what I need to do. I have a form named frmCLIENT with a subform named sfFAMILY. On the form frmCLIENT I have a command butten and when I press it I want it to automatically complete a field named CHILDREN on the subform sfFAMILY with the word "NONE". Can anyone help me. Thanks, LDMueller The code for the button is Me.YourSubformName.Form.Children = "None" Me.YourSubformName.Form.Children.Requery 'so that you can see the results This assumes that the subform has only 1 record or that the record you want editing, is the first ...
no default reminder set when importing an .ics?
It looks like the Outlook setting to create a reminder (at a preset time) on any calendar items is ignored when imported from an .ics file. Any work arounds/fixes short of manually setting the reminder every time?? ...
"The note id is not set"
We receive "The note id is not set" when trying to add a note to a customer id notepad from within the Collections Management main window. If we create the note first on the customer card, it's available from within Collections Management window. Thank you.... What version of GP? I am on 10.0 SP2 (10.00.1061) and this does not happen. Can you describe the specific sequence of events leading to this message? Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "MES" wrote: > We receive "The ...
Populating FIELDS from another form(s)
I have one form were I create meetings, than another one were the individual is inputted (all works great - this is not the problem). I am trying to open a form(Travel) from that form by clicking on a button (This I already have and that part works). I want the newly opened form (Travel) to automatically populate a few fields (that you can not see on the form) that are the primary fields. example: [field1] = Me.[field1] and [field2] = Me.[field2]. Now if the record already exist then I want it to populate the form. So far I have this: Private Sub btnTravel_Click() On Error GoTo Err_btnT...
Filling bound controls in a form with calculated results
I have a form that a user may partially fill with text and numbers that could then be used to calculate numerical results that could fill in other blank fields in the same form. My idea would be to have a un-bound button in the upper title bar of the form that the user could click after filling in some of the initial fields, and this action would calculate numerical results via VBA functions or procedures, the results then being used to fill in multiple field values I know how to write the VB functions/procedures to calculate the results, but I'm not sure where to use the functions...
Append Query problem
I have an Append Quey that works fine when you right click on the query ("ReaderIndCancelProforma_Append") and choose open INSERT INTO Reader_DistrHistory ( DistrId, [Reader Id], [Date on mailing list], TypeId, ReasonId, Reason, [Date off mailing list] ) SELECT Reader_DistrCurrent.DistrId, Reader_DistrCurrent.[Reader Id], Reader_DistrCurrent.[Date on mailing list], 6 AS Type, 15 AS Reas, [Forms]![Reader_CancelProforma]![Remarks] AS Remark, Now() AS off FROM Reader_DistrCurrent WHERE (((Reader_DistrCurrent.[Reader Id])=[Forms]![Reader_DB]![Reader Id])); The problem I hav...
Error in query referring to Combo Box
I am very new to using VBA to build forms and am having a problem. I trying to embed a query within a public sub routine. The goal of this query is to use an alphanumeric code entered into a combo box on the form to retrieve an associated ID within a table. The bound column of the combo box is text. I have used the following code: Public Sub GetSturID(intSturID As Integer) Dim rsSturID As ADODB.Recordset Set rsSturID = New ADODB.Recordset rsSturID.Open "SELECT tblSturg.SturgID FROM tblSturg WHERE (tblSturg.PIT = '" & cmbPIT.Value & "')" _ &...
Worksheet protect with query-based table
I have a table with 3 columns based on a SQL db query, with a worksheet column added to the table that is a formula I want to protect. There are 3 other non-table cells I want to protect. If I protect the worksheet, I can't refresh the table, even if I unlocked the table cells that are based on the query. ...
Query to count between list of number (Predicting Start/End that may occur in data range)
Hi, I have a below list of numbers. 566667 566668 566669 566665 566666 566671 566672 566680 I want a query that would return a count between start and end of range. Like Start End Quantity 566665 566669 5 566671 566672 2 566680 566680 1 Thank you. On 2 apr, 07:17, Angela <ims...@gmail.com> wrote: > Hi, > > I have a below list of numbers. > > 566667 > 566668 > 566669 > 566665 > 566666 > 566671 > 566672 > 566680 > > I want a query that would return a coun...
Setting spelling default
I have W2kSP4 with Office XP SP3. When I write an email and prior to sending, it checks the spelling. It comes up with English (US). I have checked the default in Tools/Options/Spelling and it advises correctly that the language is English (UK). What do I do to permanently set the default to English (UK)?? Thanks R ...
Online form ~ I have word 2003 and 2007
Hello, I am trying to create an online form that will allow for a limited amount of characters per field. For example: Name: 20 Characters at length and so on Date: 15 Characters Title of the reading: 40 characters Please advise, Thanks -- astro44 ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader an...
Report or SQL Query from website
Let's say I have my own hosted website and domain with MySQL support (not sure if that matters here)... What I would like to do is pull real-time or near real-time data from my Store Ops or HQ Server database. What kind of solutions should I be looking for? If this is not possible, I was going to try a "roll-my-own" solution by scheduling SQL queries that output to text files and pushing the files to a web server via ftp using windows task scheduler. I would much prefer the "pull" solution to get real time data. I guess I would need to do some port forwarding o...
Contact Form Changed with Regional Setting Change
I recently had to change my Windows XP "Settings/Control Panel/Regional and Language Options/Advanced/Language for non-Unicode programs" to "Chinese (PRC)" in order to view Chinese characters in a translation software package. Just to be clear - my Windows system is still in English - this setting just allows my Chinese software characters to also show up. Strangely, after the above change, the form layout in Outlook 2003 changed. Most significantly, the "Full Name" button is no longer available on the main page, which makes editing of names difficult ...
Main Form
Hello, I've been searching through the forums, but haven't found anything yet. Does anyone know of a way to copy data from multiple worksheets (i.e. 30) to one master form? I do NOT want to summarize the data, I want to replicate it exactly, and have a live link so that if the individual sheets are changed, the master will update. All column headings are identical, but there are variable numbers of rows. My only thoughts so far are simply typing =Sheet1!A2 and dragging, repeating for each sheet (or I guess writing a macro to do this). However, if I insert a new r...
Query the top top ten by category?
I have a table that has a list of occurences by store locations. I use a query to count those occurences and then produce a top ten report. I am currently producing that report by market. In order to do this I have built independent queries for each market, which are the source for subreports, that are all on the same report. In effect I get one report with the top ten occurences by market. I now need to move to a regional report. The issue I have is that there are 70 regions. And I produce three unique reports. I only have 6 markets so I had no real issue producing 18 different...
Create a Query to pull a list of records from large DB
Table 1 has 1 field and 20 records (unique tracking nos.). Table 2 has 8 fields and 900+ records. Field 7 of Table 2 contains the unique tracking nos. -- 20 of which are found in and comprise Table 1. I want to do a query that will pull only the records from Table 2 (with all 8 fields) that match the 20 unique tracking numbers found in Table 1. Can anyone help? -- Mary Create a query that includes both tables. Join the field in Table 1 to Field 7 in Table 2. The query will return only the records you want. PC Datasheet Providing Customers A Resource For Help With Access, Excel And...
complex script settings
Hi all, This is a difficult one and i doubt there is a soluton but here goes.. I am currently using one note2007 for my arabic classes but unfortunately the default for arabic is font arial size 14. I would like to set this to font Traditional arabic size 20 ad bold. I know that this can be done in word 2007 but cant find an option for this in the menus for onenote ...it only allows for setting one default for normal (EN) script. There must be some settings i can change via the registry as the program is using somekind of database to reference when i change the keyboa...
Problem: Outlook 2003 and Security settings
Regarding mail from tech publications - I can't see the images anymore. Totally crippling. Under Tools --> Options --> Security --> Download Pictures both "Don't download pictures...." and "Warn me before downloading...." are TURNED OFF (boxes not checked). Actually, I never messed with those settings. Images on mails sent to me just turned off one fine day. No matter how many times I check those options on and off, it does not resolve the issue. Regards ...
Setting Out Off Office Assistant Server Side
Hi, I have an employee who is off sick. I would like to set an out of office assistant for her. The only way i can see to do is to logon to her machine as her and set it. Is there any way to set the out of office assistant on the server side. I'm running exchange 2003 and office XP. Any help would be GREAT!!! Thanks in advance. Simon Not by default, there is a third-party app that lets you do this: http://www.symprex.com/products/oom/ -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Simon" <simonrlee22@hotmail.com> wrote in messa...
DELL 5150 f1 - (to continue) / f2
cos I can ask Dell but must pay for their engineer come to my place to solve this problem. i have this Dell 5150 desktop and everytime I boot up it will show this message Floppy diskette seek failure, F1-to continue// F2- to set up. There is no floppy in the pc oly 1 sata hdd and 1 sata dvd rom drive. How can I get ti to boot straight to window. The Bios boot sequence: 1/ on board SATA hd drive 2/ on board or usb cd-rom drive 3/ on board ide hard disk (not detected) and others all not detected Under DRIVES - diskette drives // drive 0 - sata 0 // drive 1 - sata 1 // drive...
How to explicitly set the regional code of an individual spreadsheet
Hi, is there a way to explicitly set the regional code for an excel spreadsheet individually? Thanks. No, you can't set it individually for each sheet. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Scott Steiner" <big_poppa_pump3000@yahoo.com> wrote in message news:4381C328.B1DC9B0E@yahoo.com... > Hi, > > is there a way to explicitly set the regional code for an excel > spreadsheet individually? > > Thanks. ...
I need to add footnotes in a locked form. The sections where I need footnotes are unprotected when I tyr to include a footnote the footnote is greyed out. I am using Office 2002 and 2003 Thank you Footnotes are just another feature that is unavailable in protected documents (even in unprotected sections). In order to insert a footnote, you will have to unprotect the form. -- Stefan Blom Microsoft Word MVP "FMini" <FMini@discussions.microsoft.com> wrote in message news:E62C2081-7B07-4D75-8114-A1A754FE7CDB@microsoft.com... >I need to add footnotes in ...
How to send form data to tables programatically
Is there a way to programatically have a form update to the underlying tables? What I mean is... As a user types into a form, Access doesn't seem to actually send that data to the table (and thereby create autonumbers) until user moves on to another record. Is there a way to force that data to the tables (ie make the new records) while the user is still on the form? On Sun, 30 Dec 2007 11:33:02 -0800, markmarko <markmarko@discussions.microsoft.com> wrote: >Is there a way to programatically have a form update to the underlying tables? > >What I mean is... As a user ...
settings tab
New system setup, and I set myslef up a the 1st and only user so far. Using the Web-based interface, I don't see the settings tab for configuring the system. What am I missing? If you have installed the Outlook client for CRM, you can not have Outlook open at the same time as the web interface, or you won't be able to view the Settings area (there's a workaround for this which you can find on the newsgroup). Also, once you can access your user record, open it and deselect "Restricted Access." -- Matt Wittemann http://icu-mscrm.blogspot.com "leec" wro...
How to set maximizing window for Excel?
Does anyone have any suggestions on how to maximize the excel window whenever I open excel? I have set Excel application's property into maximizing window, but I get many excel files and shortcuts, which property are set into normal mode. Does anyone have any suggestions on how to open in maximizing windown for Excel? Thanks in advance for any suggestions Eric Eric expressed precisely : > Does anyone have any suggestions on how to maximize the excel window whenever > I open excel? > I have set Excel application's property into maximizing window, but I get ...
Completing field on subform from command button on main form
This is a simplistic version of what I need to do. I have a form named frmCLIENT with a subform named sfFAMILY. On the form frmCLIENT I have a command butten and when I press it I want it to automatically complete a field named CHILDREN on the subform sfFAMILY with the word "NONE". Can anyone help me. Thanks, LDMueller The code for the button is Me.YourSubformName.Form.Children = "None" Me.YourSubformName.Form.Children.Requery 'so that you can see the results This assumes that the subform has only 1 record or that the record you want editing, is the first ...
no default reminder set when importing an .ics?
It looks like the Outlook setting to create a reminder (at a preset time) on any calendar items is ignored when imported from an .ics file. Any work arounds/fixes short of manually setting the reminder every time?? ...
"The note id is not set"
We receive "The note id is not set" when trying to add a note to a customer id notepad from within the Collections Management main window. If we create the note first on the customer card, it's available from within Collections Management window. Thank you.... What version of GP? I am on 10.0 SP2 (10.00.1061) and this does not happen. Can you describe the specific sequence of events leading to this message? Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "MES" wrote: > We receive "The ...
Populating FIELDS from another form(s)
I have one form were I create meetings, than another one were the individual is inputted (all works great - this is not the problem). I am trying to open a form(Travel) from that form by clicking on a button (This I already have and that part works). I want the newly opened form (Travel) to automatically populate a few fields (that you can not see on the form) that are the primary fields. example: [field1] = Me.[field1] and [field2] = Me.[field2]. Now if the record already exist then I want it to populate the form. So far I have this: Private Sub btnTravel_Click() On Error GoTo Err_btnT...
Filling bound controls in a form with calculated results
I have a form that a user may partially fill with text and numbers that could then be used to calculate numerical results that could fill in other blank fields in the same form. My idea would be to have a un-bound button in the upper title bar of the form that the user could click after filling in some of the initial fields, and this action would calculate numerical results via VBA functions or procedures, the results then being used to fill in multiple field values I know how to write the VB functions/procedures to calculate the results, but I'm not sure where to use the functions...
Append Query problem
I have an Append Quey that works fine when you right click on the query ("ReaderIndCancelProforma_Append") and choose open INSERT INTO Reader_DistrHistory ( DistrId, [Reader Id], [Date on mailing list], TypeId, ReasonId, Reason, [Date off mailing list] ) SELECT Reader_DistrCurrent.DistrId, Reader_DistrCurrent.[Reader Id], Reader_DistrCurrent.[Date on mailing list], 6 AS Type, 15 AS Reas, [Forms]![Reader_CancelProforma]![Remarks] AS Remark, Now() AS off FROM Reader_DistrCurrent WHERE (((Reader_DistrCurrent.[Reader Id])=[Forms]![Reader_DB]![Reader Id])); The problem I hav...
Error in query referring to Combo Box
I am very new to using VBA to build forms and am having a problem. I trying to embed a query within a public sub routine. The goal of this query is to use an alphanumeric code entered into a combo box on the form to retrieve an associated ID within a table. The bound column of the combo box is text. I have used the following code: Public Sub GetSturID(intSturID As Integer) Dim rsSturID As ADODB.Recordset Set rsSturID = New ADODB.Recordset rsSturID.Open "SELECT tblSturg.SturgID FROM tblSturg WHERE (tblSturg.PIT = '" & cmbPIT.Value & "')" _ &...
Worksheet protect with query-based table
I have a table with 3 columns based on a SQL db query, with a worksheet column added to the table that is a formula I want to protect. There are 3 other non-table cells I want to protect. If I protect the worksheet, I can't refresh the table, even if I unlocked the table cells that are based on the query. ...
Query to count between list of number (Predicting Start/End that may occur in data range)
Hi, I have a below list of numbers. 566667 566668 566669 566665 566666 566671 566672 566680 I want a query that would return a count between start and end of range. Like Start End Quantity 566665 566669 5 566671 566672 2 566680 566680 1 Thank you. On 2 apr, 07:17, Angela <ims...@gmail.com> wrote: > Hi, > > I have a below list of numbers. > > 566667 > 566668 > 566669 > 566665 > 566666 > 566671 > 566672 > 566680 > > I want a query that would return a coun...
Setting spelling default
I have W2kSP4 with Office XP SP3. When I write an email and prior to sending, it checks the spelling. It comes up with English (US). I have checked the default in Tools/Options/Spelling and it advises correctly that the language is English (UK). What do I do to permanently set the default to English (UK)?? Thanks R ...
Online form ~ I have word 2003 and 2007
Hello, I am trying to create an online form that will allow for a limited amount of characters per field. For example: Name: 20 Characters at length and so on Date: 15 Characters Title of the reading: 40 characters Please advise, Thanks -- astro44 ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader an...
Report or SQL Query from website
Let's say I have my own hosted website and domain with MySQL support (not sure if that matters here)... What I would like to do is pull real-time or near real-time data from my Store Ops or HQ Server database. What kind of solutions should I be looking for? If this is not possible, I was going to try a "roll-my-own" solution by scheduling SQL queries that output to text files and pushing the files to a web server via ftp using windows task scheduler. I would much prefer the "pull" solution to get real time data. I guess I would need to do some port forwarding o...
Contact Form Changed with Regional Setting Change
I recently had to change my Windows XP "Settings/Control Panel/Regional and Language Options/Advanced/Language for non-Unicode programs" to "Chinese (PRC)" in order to view Chinese characters in a translation software package. Just to be clear - my Windows system is still in English - this setting just allows my Chinese software characters to also show up. Strangely, after the above change, the form layout in Outlook 2003 changed. Most significantly, the "Full Name" button is no longer available on the main page, which makes editing of names difficult ...
Main Form
Hello, I've been searching through the forums, but haven't found anything yet. Does anyone know of a way to copy data from multiple worksheets (i.e. 30) to one master form? I do NOT want to summarize the data, I want to replicate it exactly, and have a live link so that if the individual sheets are changed, the master will update. All column headings are identical, but there are variable numbers of rows. My only thoughts so far are simply typing =Sheet1!A2 and dragging, repeating for each sheet (or I guess writing a macro to do this). However, if I insert a new r...
Query the top top ten by category?
I have a table that has a list of occurences by store locations. I use a query to count those occurences and then produce a top ten report. I am currently producing that report by market. In order to do this I have built independent queries for each market, which are the source for subreports, that are all on the same report. In effect I get one report with the top ten occurences by market. I now need to move to a regional report. The issue I have is that there are 70 regions. And I produce three unique reports. I only have 6 markets so I had no real issue producing 18 different...
Create a Query to pull a list of records from large DB
Table 1 has 1 field and 20 records (unique tracking nos.). Table 2 has 8 fields and 900+ records. Field 7 of Table 2 contains the unique tracking nos. -- 20 of which are found in and comprise Table 1. I want to do a query that will pull only the records from Table 2 (with all 8 fields) that match the 20 unique tracking numbers found in Table 1. Can anyone help? -- Mary Create a query that includes both tables. Join the field in Table 1 to Field 7 in Table 2. The query will return only the records you want. PC Datasheet Providing Customers A Resource For Help With Access, Excel And...
complex script settings
Hi all, This is a difficult one and i doubt there is a soluton but here goes.. I am currently using one note2007 for my arabic classes but unfortunately the default for arabic is font arial size 14. I would like to set this to font Traditional arabic size 20 ad bold. I know that this can be done in word 2007 but cant find an option for this in the menus for onenote ...it only allows for setting one default for normal (EN) script. There must be some settings i can change via the registry as the program is using somekind of database to reference when i change the keyboa...
Problem: Outlook 2003 and Security settings
Regarding mail from tech publications - I can't see the images anymore. Totally crippling. Under Tools --> Options --> Security --> Download Pictures both "Don't download pictures...." and "Warn me before downloading...." are TURNED OFF (boxes not checked). Actually, I never messed with those settings. Images on mails sent to me just turned off one fine day. No matter how many times I check those options on and off, it does not resolve the issue. Regards ...
Setting Out Off Office Assistant Server Side
Hi, I have an employee who is off sick. I would like to set an out of office assistant for her. The only way i can see to do is to logon to her machine as her and set it. Is there any way to set the out of office assistant on the server side. I'm running exchange 2003 and office XP. Any help would be GREAT!!! Thanks in advance. Simon Not by default, there is a third-party app that lets you do this: http://www.symprex.com/products/oom/ -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Simon" <simonrlee22@hotmail.com> wrote in messa...
DELL 5150 f1 - (to continue) / f2
cos I can ask Dell but must pay for their engineer come to my place to solve this problem. i have this Dell 5150 desktop and everytime I boot up it will show this message Floppy diskette seek failure, F1-to continue// F2- to set up. There is no floppy in the pc oly 1 sata hdd and 1 sata dvd rom drive. How can I get ti to boot straight to window. The Bios boot sequence: 1/ on board SATA hd drive 2/ on board or usb cd-rom drive 3/ on board ide hard disk (not detected) and others all not detected Under DRIVES - diskette drives // drive 0 - sata 0 // drive 1 - sata 1 // drive...
How to explicitly set the regional code of an individual spreadsheet
Hi, is there a way to explicitly set the regional code for an excel spreadsheet individually? Thanks. No, you can't set it individually for each sheet. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Scott Steiner" <big_poppa_pump3000@yahoo.com> wrote in message news:4381C328.B1DC9B0E@yahoo.com... > Hi, > > is there a way to explicitly set the regional code for an excel > spreadsheet individually? > > Thanks. ...