Excel for Life: 23 new messages in 15 topics

microsoft.public.access.modulesdaovba
http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en

microsoft.public.access.modulesdaovba@googlegroups.com

Today's topics:

==============================================================================
TOPIC: ISAM error when trying to create tablelink
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/3f2f7276868cdcb5?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 2:46 pm
From: RobGMiller

I was able to create the table by adding ODBC at the start of the string. I
guess I got put off by the fact that the string works on an existing table
without the ODBC.

That should do it.

Thanks again for your help.

--
RobGMiller

"RobGMiller" wrote:

> Just to clarify, as a general comment about the missing ISAM.
>
> Can we assume that if the tables can be linked manually or the link can be
> updated programmatically without changing the linked database type, then the
> ISAM is present and healthy?
>
> If that is the case, then, another way to look at it would be that a table
> cannot be created from scratch in this database with the same connect string
> that will work to change an existing similar tabledef.connect string.
>
> To explore that outlook on the problem I was able to create a table from
> scratch using the same connect string that is used when the table is first
> linked manually. Then I was able to change that connect string to the one I
> want without an error.
>
> I guess we have a solution except for one thing; The manual connect string
> is using a ODBC Machine DSN and I dont really want to have to install a DSN
> on every computer using this application. So my choice is to learn how to
> create the correct Machine DSN programatically or figure out why the desired
> connect string will not allow a tabledef to be created.
>
> The SQL Server connect strings that will work to create a table
> programmatically is:
>
> "ODBC;DSN=EngManager;APP=Microsoft Office
> 2003;WSID=ROB-HOME;DATABASE=EngManager"
>
> The DSN-less one that wont work is:
>
> "Driver={SQL Native Client}; Server=BizTalkServer\OfficeServers;
> Database=EngManager;UID=limiteduser;PWD=password;Persist Security Info=True
>
> I've trieed changing {SQL Native Client} to {SQL Server} and removing the
> Persist Security Info=True
>
> Thanks for the suggestions ...
>
> --
> RobGMiller
>
>
> "Douglas J. Steele" wrote:
>
> > Sorry, I can't think of other possible solutions.
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "Mark" <Mark@discussions.microsoft.com> wrote in message
> > news:8F43BD70-AD0C-46CF-A3C3-E285EE3D4342@microsoft.com...
> > > My apologies for the late response. I had a high priority project due...
> > >
> > > I was successfully able to link and tried to copy the description in the
> > > table design, but got the same issue.
> > >
> > > -Mark
> > >
> > >
> > > "Douglas J. Steele" wrote:
> > >
> > >> Try creating a linked table through File | Get External Data | Link
> > >> Tables.
> > >> Assuming that works, examine the Connect property of the table you
> > >> created.
> > >>
> > >> --
> > >> Doug Steele, Microsoft Access MVP
> > >> http://I.Am/DougSteele
> > >> (no private e-mails, please)
> > >>
> > >>
> > >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> > >> news:91B63395-65B9-427E-A454-2540FB264D92@microsoft.com...
> > >> > Hi Doug,
> > >> >
> > >> > Tried that change and unfortunately still not working. Here's the full
> > >> > sub,
> > >> > with the password and user removed to protect the innocent. I tried
> > >> > "ODBC;"
> > >> > and "ODBC=;" and first one gave me another error, but the second one
> > >> > worked.
> > >> > I'm able to see the rs recordset, but appending the tdf tabledef causes
> > >> > that
> > >> > same ISAM issue.
> > >> >
> > >> > And thank you so much for your help.
> > >> > -Mark
> > >> >
> > >> >
> > >> > Sub ADOConnect()
> > >> > Dim rs As New ADODB.Recordset
> > >> > Dim db As Database
> > >> > Dim conn As ADODB.Connection
> > >> > Dim i As Integer, j As Integer
> > >> > Dim str As String
> > >> > Dim tdf As TableDef
> > >> >
> > >> > Set conn = New ADODB.Connection
> > >> > str = "ODBC=;DSN=Strategy;USER=;PWD="
> > >> >
> > >> > conn.ConnectionString = str
> > >> > conn.Open
> > >> >
> > >> > Set db = CurrentDb
> > >> > Set tdf = db.CreateTableDef("TEST")
> > >> > tdf.Connect = str
> > >> > tdf.SourceTableName = "PINSQUERY"
> > >> >
> > >> > 'rs opens okay with the connection.
> > >> > rs.Open "select * from usrqrycs.pinsquery", conn, adOpenStatic,
> > >> > adLockReadOnly
> > >> > Debug.Print rs(0).Name
> > >> > Debug.Print rs(0).Value
> > >> >
> > >> > 'errors out here.
> > >> > db.TableDefs.Append tdf
> > >> >
> > >> > End Sub
> > >> >
> > >> >
> > >> > "Douglas J. Steele" wrote:
> > >> >
> > >> >> I must have made a typo. That was supposed to be a semi-colon between
> > >> >> ODBC
> > >> >> and DSN=.
> > >> >>
> > >> >> Sorry about that.
> > >> >>
> > >> >> --
> > >> >> Doug Steele, Microsoft Access MVP
> > >> >> http://I.Am/DougSteele
> > >> >> (no e-mails, please!)
> > >> >>
> > >> >>
> > >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> > >> >> news:E6171895-EA3A-4F5F-9595-A804DA929B9E@microsoft.com...
> > >> >> > Hi Doug,
> > >> >> >
> > >> >> > So, I'm pretty much a newbie with this and I'm not sure I'm
> > >> >> > following
> > >> >> > your
> > >> >> > suggestion correctly. I updated str so
> > >> >> > str = "ODBClDSN=MYDSN;USER=USERNM;PWD=MYPASSWD"
> > >> >> >
> > >> >> > where the character between the ODBC and DSN is a pipe (it copies as
> > >> >> > the
> > >> >> > letter 'l'). I didn't change anything else in the code, but am still
> > >> >> > getting
> > >> >> > the same error. Was there something else I needed to change?
> > >> >> >
> > >> >> > Oh, and yes, i'm using ADO.
> > >> >> >
> > >> >> > Thanks for taking my call!
> > >> >> > -Mark
> > >> >> >
> > >> >> >
> > >> >> > "Douglas J. Steele" wrote:
> > >> >> >
> > >> >> >> I'm assuming that your recordset is using ADO.
> > >> >> >>
> > >> >> >> What you've got appears to be an Ole DB connection string
> > >> >> >> (actually,
> > >> >> >> the
> > >> >> >> MSDASQL Ole DB provider is considered obsolete by Microsoft: for
> > >> >> >> replacements, see what Carl Prothman has at
> > >> >> >> http://www.carlprothman.net/Technology/ConnectionStrings/OLEDBProviders/tabid/87/Default.aspx).
> > >> >> >>
> > >> >> >> When creating linked tables and/or pass-through queries, your only
> > >> >> >> choice
> > >> >> >> is
> > >> >> >> ODBC. Since you apparently have a DSN created, try:
> > >> >> >>
> > >> >> >> str = "ODBClDSN=MYDSN;USER=USERNM;PWD=MYPASSWD"
> > >> >> >>
> > >> >> >>
> > >> >> >> --
> > >> >> >> Doug Steele, Microsoft Access MVP
> > >> >> >> http://I.Am/DougSteele
> > >> >> >> (no e-mails, please!)
> > >> >> >>
> > >> >> >>
> > >> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> > >> >> >> news:9F1E9B97-3C67-4A87-8E34-D03D6BD1624D@microsoft.com...
> > >> >> >> > Hi,
> > >> >> >> >
> > >> >> >> > I'm using Access 2003 and trying to create a linked table from an
> > >> >> >> > AS400
> > >> >> >> > database.
> > >> >> >> >
> > >> >> >> > Here's what I have that's not working.
> > >> >> >> > ===========
> > >> >> >> > Dim db As Database
> > >> >> >> > Dim tdf As TableDef
> > >> >> >> > dim str as string
> > >> >> >> >
> > >> >> >> > str =
> > >> >> >> > "Provider=MSDASQL;DSN=MYDSN;USER=USERNM;DATABASE=DB_QRY;PWD=MYPASSWD"
> > >> >> >> >
> > >> >> >> > Set tdf = db.CreateTableDef("TEST")
> > >> >> >> > tdf.Connect = str
> > >> >> >> > tdf.SourceTableName = "TBL1"
> > >> >> >> > db.TableDefs.Append tdf
> > >> >> >> > ===========
> > >> >> >> >
> > >> >> >> > When I get to the last line, I get the error "could not find
> > >> >> >> > installable
> > >> >> >> > ISAM"
> > >> >> >> >
> > >> >> >> > Now, two things
> > >> >> >> > 1) if i use the same connectiong string (str) and open up a
> > >> >> >> > recordset
> > >> >> >> > object
> > >> >> >> > to TBL1, I can successfully get to that table, so that tells me
> > >> >> >> > the
> > >> >> >> > connection string is okay.
> > >> >> >> > 2) If I put in the wrong password in the connection string,
> > >> >> >> > tdf.connect
> > >> >> >> > doesn't give any error.
> > >> >> >> >
> > >> >> >> > Okay, any ideas how I can fix this?
> > >> >> >> >
> > >> >> >> > Thanks!
> > >> >> >> > -Mark
> > >> >> >> >
> > >> >> >> >
> > >> >> >>
> > >> >> >>
> > >> >> >>
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >

==============================================================================
TOPIC: excel cell value
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/4c9e7db119fdea74?hl=en
==============================================================================

== 1 of 3 ==
Date: Wed, Nov 14 2007 2:50 pm
From: jnewl

that did not work

i have done something like this because i had to insert a row
xlsheet.rows("1:1").Insert
xlsheet.cells(1, 1) = "aaaa"

therefore i thought i could do
if xlsheet.rows ("1:1").value = "code" then
do stuff

"John W. Vinson" wrote:

> On Wed, 14 Nov 2007 12:18:00 -0800, jnewl <jnewl@discussions.microsoft.com>
> wrote:
>
> >want to check for a specific value in col 1 and row 1. if that value is not
> >there, will cancel the operation.
> >
> >how do i check for that value?
> >
> >tried this
> >
> >xlsheet.cells("1:1") = rowvalue
> >
> >If rowvalue = "code" Then
> > GoTo contin
> > Else
> > GoTo Err_addl_update_Click
> >End If
> >contin:
> >
> >rowvalue is defined as a string
> >
> >get error msg that not valid construct
> >
> >thanks for your help
>
> Aren't Excel cells indexed by letters in the columns, not numbers? Try "A:1".
>
> If you're running this code in Excel, please post your questions in an Excel
> newsgroup.
>
> John W. Vinson [MVP]
>

== 2 of 3 ==
Date: Wed, Nov 14 2007 4:20 pm
From: John W. Vinson

On Wed, 14 Nov 2007 14:50:01 -0800, jnewl <jnewl@discussions.microsoft.com>
wrote:

>that did not work
>
>i have done something like this because i had to insert a row
>xlsheet.rows("1:1").Insert
>xlsheet.cells(1, 1) = "aaaa"
>
>therefore i thought i could do
> if xlsheet.rows ("1:1").value = "code" then
> do stuff
>

I'd ask in an Excel newsgroup, or repost under a new header - this is Excel
syntax with which I am not familiar.

John W. Vinson [MVP]

== 3 of 3 ==
Date: Wed, Nov 14 2007 11:00 pm
From: John Nurick

If you've already created an Excel worksheet object you can get the
value of cell A1 with
xlsheet.Cells(1,1).Value 'row 1, column 1
or
xlsheet.Range("A1").Value

To save launching Excel, you can do it like this (watch out for line
wrapping in the Set R... statement:

Dim R As DAO.Recordset

Set R = CurrentDB.OpenRecordset("SELECT F1 FROM [Excel8.0;" _
& "HDR=No;database=C:\folder\XXX.xls;].[Sheet1$A1:A1];")

'The value you want is now in R.Fields(0).Value
...
R.Close

On Wed, 14 Nov 2007 12:18:00 -0800, jnewl
<jnewl@discussions.microsoft.com> wrote:

>want to check for a specific value in col 1 and row 1. if that value is not
>there, will cancel the operation.
>
>how do i check for that value?
>
>tried this
>
>xlsheet.cells("1:1") = rowvalue
>
>If rowvalue = "code" Then
> GoTo contin
> Else
> GoTo Err_addl_update_Click
>End If
>contin:
>
>rowvalue is defined as a string
>
>get error msg that not valid construct
>
>thanks for your help
--
John Nurick - Access MVP

==============================================================================
TOPIC: Button in form header
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/f72483eb1388258f?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 4:12 pm
From: JamesDeckert

I think you want me.txtUID.Value

James

"mcescher" wrote:

> Help, I must be missing something basic (pardon the pun)
>
> Windows XP Pro, Access 2002
>
> Created a new form, with some unbound text boxes in the form header,
> and a couple of buttons.
>
> cmdClose just closes the form with DoCmd.Close
>
> When I click the other button I want to check the values of the text
> boxes. They don't show up with the dropdown when I type "me." But the
> controls from the detail section do show up in the dropdown list.
>
> When I try to compile, it throws an error on "If Len(Me.txtUID & "") =
> 0 Then"
> "Compile error: Method or data member now found"
>
> Both the box and the button are in the header, and I have records that
> show up in the detail section, which is set to show "Continuous Forms"
>
> I've tried:
> txtUID
> Me.txtUID
> Forms!frmcamISeriesMaint!txtUID
>
> All with bad results. What am I doing wrong? I just want to test for
> valid data in the form, and feed the values to another function that
> I've already written.
>
> Thanks so much for your time,
> Chris M.
>
>

==============================================================================
TOPIC: Code Issue
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/ef5f3b45c139008a?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 4:16 pm
From: JamesDeckert

Use debug to stop execution after the query string is built and look at the
query string to determine if there is a formatting error in the string, or a
logic problem with the SQL.
James

"Stu" wrote:

> I have an unbound form with a multiple select list box (lstClass) and a
> command button (cmdOK). I am trying to select row(s) from the list box and
> build up a Where string and then apply that "WHERE" criteria to a Select
> Query (qrySupplierTEST) to get my desired records. Once I have these
> records, I want to use this query linked to a table in an Update Query and
> run qupdInstallerTEST. I created a Function WhereString() thinking I would
> be using this in possible several forms. Can anyone assist me and straighten
> out my code in applying the "WHERE" to a query and forward. I know the
> WhereString() is working. Thanks
>
> Private Function WhereString() As String
> Dim strWhere As String
> Dim varItem As Variant
>
> On Error Resume Next
>
> ' ... build "Class" criterion expression
> If Me.lstClass.ItemsSelected.Count > 0 Then
> strWhere = strWhere & "Class IN ("
> For Each varItem In Me.lstClass.ItemsSelected
> strWhere = strWhere & "'" & _
> Me.lstClass.ItemData(varItem) & "', "
> Next varItem
> strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND "
> End If
>
> WhereString = strWhere
> If Len(WhereString) > 0 Then
> WhereString = " WHERE " & Left(WhereString, Len(WhereString) - 5)
>
> End If
>
> End Function
>
> Private Sub cmdOK_Click()
> Dim strSQL As String
> Dim strRecordSource As String
>
> On Error Resume Next
>
> strRecordSource = "qrySupplierTEST"
>
> ' build sql string for form's RecordSource
> strSQL = "SELECT * FROM " & strRecordSource & _
> WhereString()
>
> DoCmd.OpenQuery "qupdInstallerTEST", acNormal, acEdit
>
> End Sub

==============================================================================
TOPIC: Waiting for input from a form
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/f3c5d8c996864ce6?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 4:46 pm
From: "Rick Brandt"

"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
wrote in message news:Ojo0p0uJIHA.2480@TK2MSFTNGP05.phx.gbl...
> It shouldn't be, I think. At least not as far as I recall.
> I do offcourse use the acDialog Param to make sure myself, but always also add
> the (superfluos) waiting loop to make sure it works under any circumstances.

Modal won't relinquish focus, but it does not pause code from a calling form
like acDialog does.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

==============================================================================
TOPIC: no option selected
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/7b63dc9497a0b74c?hl=en
==============================================================================

== 1 of 3 ==
Date: Wed, Nov 14 2007 6:53 pm
From: MKM

I have two option btns in an option group. When the form opens I want
neither selected. What property controls that? is it a frame property or a
property of each btn? I'm using Access 2007, but it must be the same for
earlier versions...maybe!

== 2 of 3 ==
Date: Wed, Nov 14 2007 8:32 pm
From: "tina"

in A2003 and prior, you would remove the Default value in the option control
(the "frame").

hth

"MKM" <MKM@discussions.microsoft.com> wrote in message
news:098A0A17-B622-4076-BA5B-2B18C19E5C15@microsoft.com...
> I have two option btns in an option group. When the form opens I want
> neither selected. What property controls that? is it a frame property or
a
> property of each btn? I'm using Access 2007, but it must be the same for
> earlier versions...maybe!

== 3 of 3 ==
Date: Wed, Nov 14 2007 8:38 pm
From: Mike

Try setting the Default Value of the Frame to Nothing(Blank)

"MKM" wrote:

> I have two option btns in an option group. When the form opens I want
> neither selected. What property controls that? is it a frame property or a
> property of each btn? I'm using Access 2007, but it must be the same for
> earlier versions...maybe!

==============================================================================
TOPIC: Send Object Action
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e749b518a225b04a?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 7:51 pm
From: Brian

Check out ClickYes Pro from ContextMagic at www.contextmagic.com.

"tpeter" wrote:

> I have a report that the user can e-mail to a general folder. I am using the
> command button wizard and everything works fine, the only thing I would like
> to change is the report is being picked up by outlook secturity saying
> information is being automatically started. They then have the option to stop
> the email or send it. When they click the e-mail report button I would like
> for them to load in the project number they want to send and no other
> options. Here is the code that I have any help would be great.
>
> Private Sub Email_Report_Click()
> On Error GoTo Err_Email_Report_Click
>
>
> Dim stDocName As String
> stDocName = "rptEmailReport"
> DoCmd.SendObject acReport, stDocName, acFormatSNP, "PRIMARY PERSON GOING
> TO", "SECONDARY PERSON GOING TO", , "Lab Request", , 0
> Exit_Email_Report_Click:
> Exit Sub
>
> Err_Email_Report_Click:
> MsgBox Err.Description
> Resume Exit_Email_Report_Click
>
>
> End Sub

==============================================================================
TOPIC: Export to workbook by Query Criteria
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/b56716486cea8a42?hl=en
==============================================================================

== 1 of 2 ==
Date: Wed, Nov 14 2007 8:46 pm
From: Sin

I search around for qutie sometime and still cannot find a solution to this
problem.

I have a db that runs query reports with month and year as criteria and a
number of excel workbooks named "Report MMYY". What I want is a code that
will automatically run the querys using the criteria (I've got code for this
part alread), then the code will search through a particular folder location
for the respective report correspond to the month & year criteria, if the
workbook exist, it will transfer the result to the spreadsheet, if the
workbook does not exist, the system will create a new report using the
"Report template" in the drive and name it "Report MMYY". Is this possible?

I'm currently using earling blinding to run the criteria and transfer file
to excel.

Thanks in advance.

== 2 of 2 ==
Date: Thurs, Nov 15 2007 12:07 am
From: John Nurick

Hi Sin,

You can use the Dir() function to discover if the workbook already
exists.

When you say "transfer the result to the spreadsheet", I presume you
mean creating a new sheet in the workbook containing the data from the
report. Otherwise you could simply delete the existing workbook and
let the export create a new one.

See http://accessblog.net/2006/07/export-to-excel-range.html for
information on how to control the name of the new sheet that is
created.

On Wed, 14 Nov 2007 20:46:02 -0800, Sin
<Sin@discussions.microsoft.com> wrote:

>I search around for qutie sometime and still cannot find a solution to this
>problem.
>
>I have a db that runs query reports with month and year as criteria and a
>number of excel workbooks named "Report MMYY". What I want is a code that
>will automatically run the querys using the criteria (I've got code for this
>part alread), then the code will search through a particular folder location
>for the respective report correspond to the month & year criteria, if the
>workbook exist, it will transfer the result to the spreadsheet, if the
>workbook does not exist, the system will create a new report using the
>"Report template" in the drive and name it "Report MMYY". Is this possible?
>
>I'm currently using earling blinding to run the criteria and transfer file
>to excel.
>
>Thanks in advance.
--
John Nurick - Access MVP

==============================================================================
TOPIC: Export location determine by Query Criteria
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/de5b660fcb2c52ca?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 8:48 pm
From: Sin

I search around for qutie sometime and still cannot find a solution to this
problem.

I'm currently using earling blinding to run the criteria and transfer file
to excel.

Thanks in advance.

==============================================================================
TOPIC: better way
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/c9a3ae4953ff499a?hl=en
==============================================================================

== 1 of 4 ==
Date: Wed, Nov 14 2007 8:50 pm
From: MarkS

Hi,
I use this code to get the hours in a contracts

Set rst1 = CurrentDb.OpenRecordset("Select count(PeriodNo)/2 as hours from
IERSPROD_TOFFERSLOT Where OfferCD = '" & sOfferCD & "'")
rst1.MoveFirst
lHours = rst1(0)

there can only be one answer. Is there a way to load the hours into 'lHours'
with out using a recordset

Thanks

== 2 of 4 ==
Date: Wed, Nov 14 2007 8:59 pm
From: "Allen Browne"

Perhaps:

Me.lHours = DCount("PeriodNo", "IERSPROD_TOFFERSLOT", _
"OfferCD =""" & sOfferCD & """") / 2

I doubt this will execute any more quickly than your recordset does.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MarkS" <MarkS@discussions.microsoft.com> wrote in message
news:867135C7-89BD-467E-99AA-5D8FE4E89B22@microsoft.com...
> Hi,
> I use this code to get the hours in a contracts
>
> Set rst1 = CurrentDb.OpenRecordset("Select count(PeriodNo)/2 as hours from
> IERSPROD_TOFFERSLOT Where OfferCD = '" & sOfferCD & "'")
> rst1.MoveFirst
> lHours = rst1(0)
>
> there can only be one answer. Is there a way to load the hours into
> 'lHours'
> with out using a recordset
>
> Thanks

== 3 of 4 ==
Date: Wed, Nov 14 2007 10:03 pm
From: "Dirk Goldgar"

In news:867135C7-89BD-467E-99AA-5D8FE4E89B22@microsoft.com,
MarkS <MarkS@discussions.microsoft.com> wrote:
> Hi,
> I use this code to get the hours in a contracts
>
> Set rst1 = CurrentDb.OpenRecordset("Select count(PeriodNo)/2 as hours
> from IERSPROD_TOFFERSLOT Where OfferCD = '" & sOfferCD & "'")
> rst1.MoveFirst
> lHours = rst1(0)
>
> there can only be one answer. Is there a way to load the hours into
> 'lHours' with out using a recordset

Even if you call DLookup or DCount, there'll still be a recordset at
work; it'll just be inside the function instead of outside it. I think
you'll do better with your own recordset.

Note, though, that this line:

> rst1.MoveFirst

... is unnecessary. When you first open a recordset, you're at the
first record already, unless the recordset is empty.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

== 4 of 4 ==
Date: Wed, Nov 14 2007 10:22 pm
From: MarkS

Hi,
This answers my question. I need to the same basic thing in several places
and I want to reduce the code to a standed one line piece of code. This will
work fine

Thanks

"Dirk Goldgar" wrote:

> In news:867135C7-89BD-467E-99AA-5D8FE4E89B22@microsoft.com,
> MarkS <MarkS@discussions.microsoft.com> wrote:
> > Hi,
> > I use this code to get the hours in a contracts
> >
> > Set rst1 = CurrentDb.OpenRecordset("Select count(PeriodNo)/2 as hours
> > from IERSPROD_TOFFERSLOT Where OfferCD = '" & sOfferCD & "'")
> > rst1.MoveFirst
> > lHours = rst1(0)
> >
> > there can only be one answer. Is there a way to load the hours into
> > 'lHours' with out using a recordset
>
> Even if you call DLookup or DCount, there'll still be a recordset at
> work; it'll just be inside the function instead of outside it. I think
> you'll do better with your own recordset.
>
> Note, though, that this line:
>
> > rst1.MoveFirst
>
> .... is unnecessary. When you first open a recordset, you're at the
> first record already, unless the recordset is empty.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
>

==============================================================================
TOPIC: ~~~~ Natural Penis enlargement IS NOW POSSIBLE AND 100% SAFE ~~~~~~
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/6a93490919f3b377?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 11:01 pm
From: jhon

######################################################################

Natural Penis enlargement IS NOW POSSIBLE AND 100% SAFE

New clinically proven device for Bigger penis, Harder Erections and
Sexual Stamina?

Know More visit to: http://xadvice2men.blogspot.com/

#######################################################################

==============================================================================
TOPIC: method 'range' of object'_global' failed on XL sort
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/3da41c6385ca6abc?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 11:02 pm
From: John Nurick

You need to qualify the call to the Range method with the object on
which you want to invoke the method. Maybe
.Selection.Sort Key1:= .ActiveSheet.Range("AK2"), ...

On Wed, 14 Nov 2007 22:44:16 GMT, "ragtopcaddy via AccessMonster.com"
<u9289@uwe> wrote:

>I am getting the error msg "method 'range' of object'_global' failed "
>sporadically in my code. I read elsewhere not to use "With ObjXL", but
>there's no explanation of why.
>
>I get it off and on with this line:
>
> With objXL
> (lots of other code...)
> .ActiveSheet.Range("A1:AO" & iRow).Select
> 'Here's where I get the error:
> .Selection.Sort Key1:=Range("AK2"), Order1:=xlAscending, Header:= _
> xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:
>=xlTopToBottom, _
> DataOption1:=xlSortNormal
> End With
>
>I have also seen another strange error with something like "RPL" in it's text
>at the same point in my code.
>
>Thanks,
>
>Bill R
--
John Nurick - Access MVP

==============================================================================
TOPIC: Require some help with MSXML2.DOMDocument.4.0 capturing the header
infos??
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/a7b810937b8ee3c4?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 11:21 pm
From: Norman

Hi there

I want to get the data entry <CREATE_DATE_AND_TIME> </
CREATE_DATE_AND_TIME>
from the root entry of an xml tag using code.

<MMXX_PAYMENTS>
<HEADER>
<SOURCE_SYSTEM>MMXX Ver:2.6.04.02</SOURCE_SYSTEM>
<SOURCE_ORG>PEEL</SOURCE_ORG>
<PURPOSE>Payment Record File</PURPOSE>
<CREATE_DATE_AND_TIME>20071102 14:33:16</CREATE_DATE_AND_TIME>
</HEADER>
</MMXX_PAYMENTS>

There are further tags which import fine.
I just don't know how to capture the header infos? Any help is much
appreciated.

thank you very much in advance.
regards Norman

'I use the following code:
Function Importxml()
Dim objXMLDOM
Dim objNodes
Dim objxmlNode
Dim objNodesHeader
Dim objxmlNodesHeader
Dim objNodesType
Dim objxmlNodeTypeFee
Dim objNodesInstallment
Dim objxmlNodeType
Dim objNodesTypeFee
Dim objxmlNodesTypeFee
Dim Strsql As String, strorg As String, strfilename As String,
strdatetime
Dim objADORS
Dim objADOCnn
Dim rst As DAO.Recordset
Dim db As DAO.Database

Const adOpenKeyset = 1
Const adLockOptimistic = 3

Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
objXMLDOM.async = False
objXMLDOM.validateOnParse = False
objXMLDOM.Load sFileName

Set objNodes = objXMLDOM.selectNodes("MMXX_PAYMENTS")

For Each objxmlNode In objNodes
' get the header information
Set objNodesHeader = objxmlNode.selectNodes("Header")

For Each objxmlNodesHeader In objNodesHeader
strorg =
objxmlNodesHeader.selectSingleNode("SOURCE_ORG").nodeTypedValue
strdatetime =
objxmlNodesHeader.selectSingleNode("CREATE_DATE_AND_TIME").nodeTypedValue
Next

' next level
Set objNodesType = objxmlNode.selectNodes("PAYMENTS/
INSTALLMENT_PAYMENT")
For Each objxmlNodeType In objNodesType
With rst
.AddNew
.update
end with
next
next
end function

==============================================================================
TOPIC: Delete the last record
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/b82b56f29e8ef7cb?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 11:45 pm
From: morgan

Hi John, sometimes i try to complicate my life....
Tks

"John Spencer" wrote:

> Why not run a delete query that deletes any record that has the Phrase "This
> List has been created "
>
> You can probably just add the following line (all one line) of code to your
> current code.
>
> CurrentDb().Execute "DELETE FROM db_MP3 WHERE [Somefield] Like 'This List
> has been created*'"
>
> --
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
>
> "morgan" <morgan@discussions.microsoft.com> wrote in message
> news:034FFDE9-3AF9-4948-981E-EC2A498ABD3A@microsoft.com...
> > Hello everybody, I'm making my MP3 collection db importing xls files
> > created
> > by an MP3 tag editor. I have the following code to append the xls file to
> > my
> > access table and everything work fine. Since the last row of the xls file
> > is
> > always "this list has been created with..." I'd like to modify the code to
> > delete the last record appended on my access table and then refresh the
> > form,
> > in order to get immeditely the new records. I'm using access 2003.
> > Tks in advance
> >
> > Private Sub Comando10_Click()
> >
> > Dim dlg As FileDialog
> > Dim Importfile As String
> >
> > Set dlg = Application.FileDialog(msoFileDialogFilePicker)
> > With dlg
> > .Title = "Select the Excelfile to import"
> > .AllowMultiSelect = False
> > .Filters.Clear
> > .Filters.Add "Excel Files", "*.xls", 1
> > .Filters.Add "All Files", "*.*", 2
> > If .Show = -1 Then
> > Importfile = .SelectedItems(1)
> >
> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
> > "Db_MP3", Importfile, True, "A1:E1000"
> > Else
> > Exit Sub
> > End If
> > End With
> >
> > End Sub
> >
>
>
>

==============================================================================
TOPIC: qty in table1 minus qty in table2 equal to result column in table2
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/b91831dc8e3c6998?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 15 2007 12:18 am
From: hisham

Any can help me to correct my coding ?
**********************************************
Dim dbs As Database, rst As Recordset
Dim strSelect As String
Dim strSelect2 As String

Set dbs = CurrentDb
strSelect = "SELECT * FROM table1"
strSelect2 = "Select * FROM table2"

Set rst = dbs.OpenRecordset(strSelect)
Set rst2 = dbs.OpenRecordset(strSelect2)

Do While Not rst.EOF
rst.Edit
rst2.Edit
If rst![Item] = rst2![Item] Then
rst2![Result] = rst2![P1ST] - rst![Qty]
Else
rst2![Result] = "NOT OK"
End If
rst.Update
rst2.Update

rst.MoveNext
rst2.MoveNext

Loop
rst.close
rst2.close

MsgBox (" compare Done!!!")
End Sub
***************************************
Pls help me

==============================================================================

You received this message because you are subscribed to the Google Groups "microsoft.public.access.modulesdaovba"
group.

To post to this group, visit http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en

To unsubscribe from this group, send email to microsoft.public.access.modulesdaovba-unsubscribe@googlegroups.com

To change the way you get mail from this group, visit:
http://groups.google.com/group/microsoft.public.access.modulesdaovba/subscribe?hl=en

To report abuse, send email explaining the problem to abuse@googlegroups.com

==============================================================================
Google Groups: http://groups.google.com?hl=en

Excel for Life

Kamis, 15 November 2007

23 new messages in 15 topics - digest

Tidak ada komentar:

Arsip Blog

Blogeroll

Mengenai Saya