Excel for Life

26 new messages in 15 topics - digest

2008-08-28T13:27:00.000-07:00

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: Front End connectivity to the network is always trouble
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/ccee521d3a73bb3e?hl=en
==============================================================================

== 1 of 4 ==
Date: Thurs, Aug 28 2008 10:17 am
From: dch3

So if we're running on Windows Server 2003, there shouldn't be any issues
with more than 10 users accessing the backend?

"Michel Walsh" wrote:

> yes to both questions.
>
> Vanderghast, Access MVP
>
>
>

== 2 of 4 ==
Date: Thurs, Aug 28 2008 10:21 am
From: "Michel Walsh"

Sure, if the limit is waved out (as raising the maximum number of users to
99, as example, for that share directory / file ).

Vanderghast, Access MVP

"dch3" <dch3@discussions.microsoft.com> wrote in message
news:D5EA3BEC-765E-41EE-9E26-0FCB42B1A971@microsoft.com...
> So if we're running on Windows Server 2003, there shouldn't be any issues
> with more than 10 users accessing the backend?
>
> "Michel Walsh" wrote:
>
>> yes to both questions.
>>
>> Vanderghast, Access MVP
>>
>>
>>
>

== 3 of 4 ==
Date: Thurs, Aug 28 2008 11:40 am
From: RD

On Wed, 27 Aug 2008 22:06:25 -0700, Frank Situmorang
<hfsitumo2001@yahoo.com> wrote:

>Thanks Michel for your response, may be we need to give more information. The
>OS in our server is Windows 2000 professional. Does it have also limit for
>the user? and coulcd it be the problem, because as I said as soon as I
>recreated again for the same user and untick the option, it works again.

I just checked ... yes, W2K also has the 10 user limit.

== 4 of 4 ==
Date: Thurs, Aug 28 2008 12:24 pm
From: dch3

Any issues we'll just upsize to SQLServer sooner than later.

"Michel Walsh" wrote:

> Sure, if the limit is waved out (as raising the maximum number of users to
> 99, as example, for that share directory / file ).
>
>
> Vanderghast, Access MVP
>
>
> "dch3" <dch3@discussions.microsoft.com> wrote in message
> news:D5EA3BEC-765E-41EE-9E26-0FCB42B1A971@microsoft.com...
> > So if we're running on Windows Server 2003, there shouldn't be any issues
> > with more than 10 users accessing the backend?
> >
> > "Michel Walsh" wrote:
> >
> >> yes to both questions.
> >>
> >> Vanderghast, Access MVP
> >>
> >>
> >>
> >
>
>
>

==============================================================================
TOPIC: FindFirst Problem
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/780ad3d1e4a0534a?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Aug 28 2008 10:25 am
From: Marshall Barton

Jeefgeorge wrote:

>I fixed it.....
>
>I replced
> .FindFirst "Right([ProjNo],4) =" & """ & Proj & """
>with
> .FindFirst "Right([ProjNo],4) =" & Chr(34) & Proj & Chr(34)
>AND
> .FindFirst "[ProjNo] =" & """ & Proj & """
>with
> .FindFirst "[ProjNo] =" & Chr(34) & Proj & Chr(34)

That is certainly a valid way to do that so no need to make
any other changes.

Your original problem was that you did not have the correct
number of quotes. The basic rule of putting a quote inside
quotes is to use two quotes where you want one quote in the
result.

Note that Chr(34) is the same as """" so you could have
solved the problem by using:
.FindFirst "[ProjNo] =" & """" & Proj & """"

You could also have combined the first set of quotes into
the initial part of the expression:

.FindFirst "[ProjNo] =""" & Proj & """"

--
Marsh
MVP [MS Access]

==============================================================================
TOPIC: If Else Statement in VBA
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/a742da2d52b68eb7?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Aug 28 2008 10:47 am
From: John Spencer

You could write that as

Public Function getquarter(Mnth)

If IsNumeric(Mnth) = False then
'Note the first comparison must return
'results in another line
getQuarter = Null
ElseIf Mnth = 1 Then:getquarter = 1
ElseIf Mnth = 2 Then: getquarter = 1
ElseIf Mnth = 3 Then: getquarter = 1
ElseIf Mnth = 4 Then: getquarter = 2
ElseIf Mnth = 5 Then: getquarter = 2
ElseIf Mnth = 6 Then: getquarter = 2
ElseIf Mnth = 7 Then: getquarter = 3
ElseIf Mnth = 8 Then: getquarter = 3
ElseIf Mnth = 9 Then: getquarter = 3
ElseIf Mnth = 10 Then: getquarter = 4
ElseIf Mnth = 11 Then: getquarter = 4
ElseIf Mnth = 12 Then: getquarter = 4
Else: getquarter = Null
End If

End Function

As noted elsewhere there Are better methods to get the quarter.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Victoria wrote:
> Unfortunately, it's been 9 months since I've really worked in access and now
> for some reason I can't get a simple statement to work! Help! What am I doing
> wrong?
>
> I took a date field, calculated month from it, which is a numerical value
> and am now trying to translate it to quarter. What am I doing wrong?
>
> Public Function getquarter(Mnth)
>
> If Mnth = 1 Then getquarter = 1
> ElseIf Mnth = 2 Then getquarter = 1
> ElseIf Mnth = 3 Then getquarter = 1
> ElseIf Mnth = 4 Then getquarter = 2
> ElseIf Mnth = 5 Then getquarter = 2
> ElseIf Mnth = 6 Then getquarter = 2
> ElseIf Mnth = 7 Then getquarter = 3
> ElseIf Mnth = 8 Then getquarter = 3
> ElseIf Mnth = 9 Then getquarter = 3
> ElseIf Mnth = 10 Then getquarter = 4
> ElseIf Mnth = 11 Then getquarter = 4
> ElseIf Mnth = 12 Then getquarter = 4
> End If
> End Function
>
>
> Thanks!
> -Victoria
> avvigil@yahoo.com

==============================================================================
TOPIC: Help with "dll" files.
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/2de9a73de2226a17?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Aug 28 2008 10:54 am
From: "Tony Toews [MVP]"

FatMan <FatMan@discussions.microsoft.com> wrote:

>If I was to move up to Access 2007 would the chance of having this type of
>problem decrease? I have been told that the run time for Access 2007 is free
>for distribution. Is this correct?

Yes. Also see Microsoft Access (Office) Developer Edition FAQ
http://www.granite.ab.ca/access/developereditionfaq.htm

>If my database program runs in Access
>2000 is it safe to assume that it should run in Access 2007 with out a
>problem?

No. There are little quirks you have to be aware of especially if
Microsoft has tightened up the behavior of some things. Allen Browne
has a page on these quirks. www.allenbrowne.com. But once you're
past the quirks it won't be too bad.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

==============================================================================
TOPIC: Name AutoCorrect Save Failures
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/dbb4e2b3f5992a21?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Aug 28 2008 11:27 am
From: Jeefgeorge

Thanks, Worked Great

"Klatuu" wrote:

> Turn AutoCorrect off. Don't use it. It is buggy and causes problems.
>
> You may want to read the info at this site:
> http://allenbrowne.com/bug-03.html
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Jeefgeorge" wrote:
>
> > When I run a TransferSpreadsheet command, a [Name AutoCorrect Save Failures]
> > table is created.
> >
> > Object Name: BidTabData
> > Object Type: Table
> > Failure Reason: Could not save the object
> > Time:
> >
> > Code:
> > ...
> > Tble = "BidTabData"
> > Path = ahtCommonFileOpenSave...
> > CurBid = 1
> > Do While CurBid <= 7
> > Rnge = "Bid" & CurBid
> > DoCmd.TransferSpreadsheet acImport, 8, Tble, Path, -1, Rnge
> > CurBid = CurBid + 1
> > Loop
> > ...
> >
> > I think this table is created since the range is 250 rows long, but some
> > rows are blank. Some spreadsheets have 7 rows, some have 100. I used a range
> > of 250 rows because that is an extreme max to insert. The blank rows are not
> > imported, because no primary key is present. I think the error is because
> > these blank rows are not added to the table. Is there a way to not flag not
> > saving blank rows as an error? Or is there something else generateing this
> > table?

==============================================================================
TOPIC: Search Check Boxes on a Form Using Check boxes on a search For
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/1581abdd7c486e13?hl=en
==============================================================================

== 1 of 6 ==
Date: Thurs, Aug 28 2008 11:38 am
From: BIS-SISA

Hi,

Happy to find this thread and I was successful in getting the code to work
in my form but am puzzled by the filtered results.

I have 1276 records that when I filter them it shows 1265 records. My goal
is to see the 11 records filtered out. Any suggestions??

Thanks!

"Klatuu" wrote:

> Sure, no problem.
>
> First, add a command button to your form. Give it the name cmdSetFilter.
> Set the Caption to Set Filter
> Now, to add the code, select the Events tab of the properties dialog. Find
> the text box labled On Click.
> Click on the small command button just to the right of the text box.
> Choose Code Builder from the list that will pop up.
> The Visual Basic Editor (VBE) will open and you will see:
> Paste the code into the editor where the cursor is.
>
> Private Sub cmdSetFilter_Click()
> |
> End Sub
>
> Now, to use you check box name as an example. I put it in as the first
> field name. Now let's get clear on terminology. When you say Field, that
> means a field in a table or query. When you refer to a check box on a form,
> that is a Control. So based on your statement, I am assuming [Business and
> Enterprise] is the name of a field in the form's record source. Each of your
> Check Boxes is probably bound to a field in the form's record source, so you
> use the name of the field and the name of the control in the comparison. You
> do it for each check box you want to include in th your filter. So:
> v----Name of Field---------v v--Name of Control bound to field
> "[Business and Enterprise] = " & Me.chkOne
>
>
> Dim strFitler As String
>
> If Me.cmdSetFilter.Caption = "Set Filter" Then
> strFilter = "[Business and Enterprise] = " & Me.chkOne & " AND
> [Field2] = " &
> Me.chkTwo & " AND [Field3] = " & Me.chkThree
> Me.Filter = strFilter
> Me.FilterOn = True
> Me.cmdSetFilter.Caption = "Clear Filter"
> Else
> Me.FilterOn = False
> Me.cmdSetFilter.Caption = "Set Filter"
> End If
>
> --
> Dave Hargis, Microsoft Access MVP

== 2 of 6 ==
Date: Thurs, Aug 28 2008 11:44 am
From: Klatuu

Well, you obviously have the logic working correctly, it would just be how
you are identify the filtering values.

If you can give me some details on what you are doing, perhaps we can make
it work for you.
--
Dave Hargis, Microsoft Access MVP

"BIS-SISA" wrote:

> Hi,
>
> Happy to find this thread and I was successful in getting the code to work
> in my form but am puzzled by the filtered results.
>
> I have 1276 records that when I filter them it shows 1265 records. My goal
> is to see the 11 records filtered out. Any suggestions??
>
> Thanks!
>
> "Klatuu" wrote:
>
> > Sure, no problem.
> >
> > First, add a command button to your form. Give it the name cmdSetFilter.
> > Set the Caption to Set Filter
> > Now, to add the code, select the Events tab of the properties dialog. Find
> > the text box labled On Click.
> > Click on the small command button just to the right of the text box.
> > Choose Code Builder from the list that will pop up.
> > The Visual Basic Editor (VBE) will open and you will see:
> > Paste the code into the editor where the cursor is.
> >
> > Private Sub cmdSetFilter_Click()
> > |
> > End Sub
> >
> > Now, to use you check box name as an example. I put it in as the first
> > field name. Now let's get clear on terminology. When you say Field, that
> > means a field in a table or query. When you refer to a check box on a form,
> > that is a Control. So based on your statement, I am assuming [Business and
> > Enterprise] is the name of a field in the form's record source. Each of your
> > Check Boxes is probably bound to a field in the form's record source, so you
> > use the name of the field and the name of the control in the comparison. You
> > do it for each check box you want to include in th your filter. So:
> > v----Name of Field---------v v--Name of Control bound to field
> > "[Business and Enterprise] = " & Me.chkOne
> >
> >
> > Dim strFitler As String
> >
> > If Me.cmdSetFilter.Caption = "Set Filter" Then
> > strFilter = "[Business and Enterprise] = " & Me.chkOne & " AND
> > [Field2] = " &
> > Me.chkTwo & " AND [Field3] = " & Me.chkThree
> > Me.Filter = strFilter
> > Me.FilterOn = True
> > Me.cmdSetFilter.Caption = "Clear Filter"
> > Else
> > Me.FilterOn = False
> > Me.cmdSetFilter.Caption = "Set Filter"
> > End If
> >
> > --
> > Dave Hargis, Microsoft Access MVP

== 3 of 6 ==
Date: Thurs, Aug 28 2008 12:00 pm
From: BIS-SISA

Well first let me say that you are dealing with the coding challenged here
(hence my joy at finding your thread laying out all the steps). Now that I
have disclosed that grievous fact let me try and provide some info that will
be what you need to help me:-)

I have inherited an AC 2000 database that is used to track IT assets. There
are multiple Yes/No controls to identify location/hardware/use. Also the form
is multi-tabbed to breakout information by type (i.e. hardware, software,
maintenance, etc.).

Here is what is working in my dyslexic world, and yes I am dyslexic):

Private Sub cmdFilterBIS_Click()
Dim strFilter As String

If Me.cmdFilterBIS.Caption = "Set BIS Filter" Then
strFilter = "[BIS PC] = " & Me.ysnBIS_PC
Me.Filter = strFilter
Me.FilterOn = True
Me.cmdFilterBIS.Caption = "Clear BIS Filter"
Else
Me.FilterOn = False
Me.cmdFilterBIS.Caption = "Set BIS Filter"
End If

End Sub

The reason I have inserted "BIS" is due to the need that once I have this
filter working I need to create two additional filters for "Education" and
"Health Care".

Any help will be appreciated and your mentoring is very much valued!

Uriel

"Klatuu" wrote:

> Well, you obviously have the logic working correctly, it would just be how
> you are identify the filtering values.
>
> If you can give me some details on what you are doing, perhaps we can make
> it work for you.
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "BIS-SISA" wrote:
>
> > Hi,
> >
> > Happy to find this thread and I was successful in getting the code to work
> > in my form but am puzzled by the filtered results.
> >
> > I have 1276 records that when I filter them it shows 1265 records. My goal
> > is to see the 11 records filtered out. Any suggestions??
> >
> > Thanks!
> >
> > "Klatuu" wrote:
> >
> > > Sure, no problem.
> > >
> > > First, add a command button to your form. Give it the name cmdSetFilter.
> > > Set the Caption to Set Filter
> > > Now, to add the code, select the Events tab of the properties dialog. Find
> > > the text box labled On Click.
> > > Click on the small command button just to the right of the text box.
> > > Choose Code Builder from the list that will pop up.
> > > The Visual Basic Editor (VBE) will open and you will see:
> > > Paste the code into the editor where the cursor is.
> > >
> > > Private Sub cmdSetFilter_Click()
> > > |
> > > End Sub
> > >
> > > Now, to use you check box name as an example. I put it in as the first
> > > field name. Now let's get clear on terminology. When you say Field, that
> > > means a field in a table or query. When you refer to a check box on a form,
> > > that is a Control. So based on your statement, I am assuming [Business and
> > > Enterprise] is the name of a field in the form's record source. Each of your
> > > Check Boxes is probably bound to a field in the form's record source, so you
> > > use the name of the field and the name of the control in the comparison. You
> > > do it for each check box you want to include in th your filter. So:
> > > v----Name of Field---------v v--Name of Control bound to field
> > > "[Business and Enterprise] = " & Me.chkOne
> > >
> > >
> > > Dim strFitler As String
> > >
> > > If Me.cmdSetFilter.Caption = "Set Filter" Then
> > > strFilter = "[Business and Enterprise] = " & Me.chkOne & " AND
> > > [Field2] = " &
> > > Me.chkTwo & " AND [Field3] = " & Me.chkThree
> > > Me.Filter = strFilter
> > > Me.FilterOn = True
> > > Me.cmdSetFilter.Caption = "Clear Filter"
> > > Else
> > > Me.FilterOn = False
> > > Me.cmdSetFilter.Caption = "Set Filter"
> > > End If
> > >
> > > --
> > > Dave Hargis, Microsoft Access MVP

== 4 of 6 ==
Date: Thurs, Aug 28 2008 12:38 pm
From: Klatuu

With this line:
strFilter = "[BIS PC] = " & Me.ysnBIS_PC

What you are doing is filtering your form's recordset based on a field in
the recodset named [BIS PC] being equal to a control on your form name
ysnBIS_PC.

I am guessing from the name of the control it is a check box and that [BIS
PC] is a Yes/No field in your form's recordset. If all of this is correct
then here is what would happen.
If ysnBIS_PC is checked and you apply the filter, then only records where
the field [BIS PC] is True (the value is actually -1) will be included. If,
when you click the button to set the filter and ysnBIS_PC is not checked, you
would have only rows where [BIS PC] is False (0).

If the field and control are not as I assume, please let me know what data
types they are and how you want the filtering to work.
--
Dave Hargis, Microsoft Access MVP

"BIS-SISA" wrote:

> Well first let me say that you are dealing with the coding challenged here
> (hence my joy at finding your thread laying out all the steps). Now that I
> have disclosed that grievous fact let me try and provide some info that will
> be what you need to help me:-)
>
> I have inherited an AC 2000 database that is used to track IT assets. There
> are multiple Yes/No controls to identify location/hardware/use. Also the form
> is multi-tabbed to breakout information by type (i.e. hardware, software,
> maintenance, etc.).
>
> Here is what is working in my dyslexic world, and yes I am dyslexic):
>
> Private Sub cmdFilterBIS_Click()
> Dim strFilter As String
>
> If Me.cmdFilterBIS.Caption = "Set BIS Filter" Then
> strFilter = "[BIS PC] = " & Me.ysnBIS_PC
> Me.Filter = strFilter
> Me.FilterOn = True
> Me.cmdFilterBIS.Caption = "Clear BIS Filter"
> Else
> Me.FilterOn = False
> Me.cmdFilterBIS.Caption = "Set BIS Filter"
> End If
>
> End Sub
>
> The reason I have inserted "BIS" is due to the need that once I have this
> filter working I need to create two additional filters for "Education" and
> "Health Care".
>
> Any help will be appreciated and your mentoring is very much valued!
>
> Uriel
>
> "Klatuu" wrote:
>
> > Well, you obviously have the logic working correctly, it would just be how
> > you are identify the filtering values.
> >
> > If you can give me some details on what you are doing, perhaps we can make
> > it work for you.
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "BIS-SISA" wrote:
> >
> > > Hi,
> > >
> > > Happy to find this thread and I was successful in getting the code to work
> > > in my form but am puzzled by the filtered results.
> > >
> > > I have 1276 records that when I filter them it shows 1265 records. My goal
> > > is to see the 11 records filtered out. Any suggestions??
> > >
> > > Thanks!
> > >
> > > "Klatuu" wrote:
> > >
> > > > Sure, no problem.
> > > >
> > > > First, add a command button to your form. Give it the name cmdSetFilter.
> > > > Set the Caption to Set Filter
> > > > Now, to add the code, select the Events tab of the properties dialog. Find
> > > > the text box labled On Click.
> > > > Click on the small command button just to the right of the text box.
> > > > Choose Code Builder from the list that will pop up.
> > > > The Visual Basic Editor (VBE) will open and you will see:
> > > > Paste the code into the editor where the cursor is.
> > > >
> > > > Private Sub cmdSetFilter_Click()
> > > > |
> > > > End Sub
> > > >
> > > > Now, to use you check box name as an example. I put it in as the first
> > > > field name. Now let's get clear on terminology. When you say Field, that
> > > > means a field in a table or query. When you refer to a check box on a form,
> > > > that is a Control. So based on your statement, I am assuming [Business and
> > > > Enterprise] is the name of a field in the form's record source. Each of your
> > > > Check Boxes is probably bound to a field in the form's record source, so you
> > > > use the name of the field and the name of the control in the comparison. You
> > > > do it for each check box you want to include in th your filter. So:
> > > > v----Name of Field---------v v--Name of Control bound to field
> > > > "[Business and Enterprise] = " & Me.chkOne
> > > >
> > > >
> > > > Dim strFitler As String
> > > >
> > > > If Me.cmdSetFilter.Caption = "Set Filter" Then
> > > > strFilter = "[Business and Enterprise] = " & Me.chkOne & " AND
> > > > [Field2] = " &
> > > > Me.chkTwo & " AND [Field3] = " & Me.chkThree
> > > > Me.Filter = strFilter
> > > > Me.FilterOn = True
> > > > Me.cmdSetFilter.Caption = "Clear Filter"
> > > > Else
> > > > Me.FilterOn = False
> > > > Me.cmdSetFilter.Caption = "Set Filter"
> > > > End If
> > > >
> > > > --
> > > > Dave Hargis, Microsoft Access MVP

== 5 of 6 ==
Date: Thurs, Aug 28 2008 12:55 pm
From: BIS-SISA

Yes I think that is right. In the table "System Computer" the field name "BIS
PC" has a data type of "Yes/No". In the form "frmSystemComputerBIS" the
control name is "ysnBIS_PC" and the control source is "BIS PC".

So is that right? I know it's awful that I don't know for sure but there you
have it, the coding challenged live on!

Thanks for the on-going help!!

Uriel

"Klatuu" wrote:

> With this line:
> strFilter = "[BIS PC] = " & Me.ysnBIS_PC
>
> What you are doing is filtering your form's recordset based on a field in
> the recodset named [BIS PC] being equal to a control on your form name
> ysnBIS_PC.
>
> I am guessing from the name of the control it is a check box and that [BIS
> PC] is a Yes/No field in your form's recordset. If all of this is correct
> then here is what would happen.
> If ysnBIS_PC is checked and you apply the filter, then only records where
> the field [BIS PC] is True (the value is actually -1) will be included. If,
> when you click the button to set the filter and ysnBIS_PC is not checked, you
> would have only rows where [BIS PC] is False (0).
>
> If the field and control are not as I assume, please let me know what data
> types they are and how you want the filtering to work.
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "BIS-SISA" wrote:
>
> > Well first let me say that you are dealing with the coding challenged here
> > (hence my joy at finding your thread laying out all the steps). Now that I
> > have disclosed that grievous fact let me try and provide some info that will
> > be what you need to help me:-)
> >
> > I have inherited an AC 2000 database that is used to track IT assets. There
> > are multiple Yes/No controls to identify location/hardware/use. Also the form
> > is multi-tabbed to breakout information by type (i.e. hardware, software,
> > maintenance, etc.).
> >
> > Here is what is working in my dyslexic world, and yes I am dyslexic):
> >
> > Private Sub cmdFilterBIS_Click()
> > Dim strFilter As String
> >
> > If Me.cmdFilterBIS.Caption = "Set BIS Filter" Then
> > strFilter = "[BIS PC] = " & Me.ysnBIS_PC
> > Me.Filter = strFilter
> > Me.FilterOn = True
> > Me.cmdFilterBIS.Caption = "Clear BIS Filter"
> > Else
> > Me.FilterOn = False
> > Me.cmdFilterBIS.Caption = "Set BIS Filter"
> > End If
> >
> > End Sub
> >
> > The reason I have inserted "BIS" is due to the need that once I have this
> > filter working I need to create two additional filters for "Education" and
> > "Health Care".
> >
> > Any help will be appreciated and your mentoring is very much valued!
> >
> > Uriel
> >
> > "Klatuu" wrote:
> >
> > > Well, you obviously have the logic working correctly, it would just be how
> > > you are identify the filtering values.
> > >
> > > If you can give me some details on what you are doing, perhaps we can make
> > > it work for you.
> > > --
> > > Dave Hargis, Microsoft Access MVP
> > >
> > >
> > > "BIS-SISA" wrote:
> > >
> > > > Hi,
> > > >
> > > > Happy to find this thread and I was successful in getting the code to work
> > > > in my form but am puzzled by the filtered results.
> > > >
> > > > I have 1276 records that when I filter them it shows 1265 records. My goal
> > > > is to see the 11 records filtered out. Any suggestions??
> > > >
> > > > Thanks!
> > > >
> > > > "Klatuu" wrote:
> > > >
> > > > > Sure, no problem.
> > > > >
> > > > > First, add a command button to your form. Give it the name cmdSetFilter.
> > > > > Set the Caption to Set Filter
> > > > > Now, to add the code, select the Events tab of the properties dialog. Find
> > > > > the text box labled On Click.
> > > > > Click on the small command button just to the right of the text box.
> > > > > Choose Code Builder from the list that will pop up.
> > > > > The Visual Basic Editor (VBE) will open and you will see:
> > > > > Paste the code into the editor where the cursor is.
> > > > >
> > > > > Private Sub cmdSetFilter_Click()
> > > > > |
> > > > > End Sub
> > > > >
> > > > > Now, to use you check box name as an example. I put it in as the first
> > > > > field name. Now let's get clear on terminology. When you say Field, that
> > > > > means a field in a table or query. When you refer to a check box on a form,
> > > > > that is a Control. So based on your statement, I am assuming [Business and
> > > > > Enterprise] is the name of a field in the form's record source. Each of your
> > > > > Check Boxes is probably bound to a field in the form's record source, so you
> > > > > use the name of the field and the name of the control in the comparison. You
> > > > > do it for each check box you want to include in th your filter. So:
> > > > > v----Name of Field---------v v--Name of Control bound to field
> > > > > "[Business and Enterprise] = " & Me.chkOne
> > > > >
> > > > >
> > > > > Dim strFitler As String
> > > > >
> > > > > If Me.cmdSetFilter.Caption = "Set Filter" Then
> > > > > strFilter = "[Business and Enterprise] = " & Me.chkOne & " AND
> > > > > [Field2] = " &
> > > > > Me.chkTwo & " AND [Field3] = " & Me.chkThree
> > > > > Me.Filter = strFilter
> > > > > Me.FilterOn = True
> > > > > Me.cmdSetFilter.Caption = "Clear Filter"
> > > > > Else
> > > > > Me.FilterOn = False
> > > > > Me.cmdSetFilter.Caption = "Set Filter"
> > > > > End If
> > > > >
> > > > > --
> > > > > Dave Hargis, Microsoft Access MVP

== 6 of 6 ==
Date: Thurs, Aug 28 2008 1:08 pm
From: Klatuu

I just set up a test for for this situation using a yes/no field and a check
box on a form. It worked just fine for me. I don't see a problem with the
code you posted. Here is my version. Note, I would not use default Access
names normally, this was just a test:

Private Sub Command10_Click()
With Me.Command10
If .Caption = "Filter" Then
.Caption = "Filter Off"
Me.Filter = "[iscorporate] = " & Me.chkIsCorporate
Me.FilterOn = True
Else
.Caption = "Filter"
Me.FilterOn = False
End If
End With
End Sub

Functionally, I don't see a difference, but I thought I would send this
along so you can compare to see if I am missing something.
--
Dave Hargis, Microsoft Access MVP

"BIS-SISA" wrote:

> Yes I think that is right. In the table "System Computer" the field name "BIS
> PC" has a data type of "Yes/No". In the form "frmSystemComputerBIS" the
> control name is "ysnBIS_PC" and the control source is "BIS PC".
>
> So is that right? I know it's awful that I don't know for sure but there you
> have it, the coding challenged live on!
>
> Thanks for the on-going help!!
>
> Uriel
>
>
> "Klatuu" wrote:
>
> > With this line:
> > strFilter = "[BIS PC] = " & Me.ysnBIS_PC
> >
> > What you are doing is filtering your form's recordset based on a field in
> > the recodset named [BIS PC] being equal to a control on your form name
> > ysnBIS_PC.
> >
> > I am guessing from the name of the control it is a check box and that [BIS
> > PC] is a Yes/No field in your form's recordset. If all of this is correct
> > then here is what would happen.
> > If ysnBIS_PC is checked and you apply the filter, then only records where
> > the field [BIS PC] is True (the value is actually -1) will be included. If,
> > when you click the button to set the filter and ysnBIS_PC is not checked, you
> > would have only rows where [BIS PC] is False (0).
> >
> > If the field and control are not as I assume, please let me know what data
> > types they are and how you want the filtering to work.
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "BIS-SISA" wrote:
> >
> > > Well first let me say that you are dealing with the coding challenged here
> > > (hence my joy at finding your thread laying out all the steps). Now that I
> > > have disclosed that grievous fact let me try and provide some info that will
> > > be what you need to help me:-)
> > >
> > > I have inherited an AC 2000 database that is used to track IT assets. There
> > > are multiple Yes/No controls to identify location/hardware/use. Also the form
> > > is multi-tabbed to breakout information by type (i.e. hardware, software,
> > > maintenance, etc.).
> > >
> > > Here is what is working in my dyslexic world, and yes I am dyslexic):
> > >
> > > Private Sub cmdFilterBIS_Click()
> > > Dim strFilter As String
> > >
> > > If Me.cmdFilterBIS.Caption = "Set BIS Filter" Then
> > > strFilter = "[BIS PC] = " & Me.ysnBIS_PC
> > > Me.Filter = strFilter
> > > Me.FilterOn = True
> > > Me.cmdFilterBIS.Caption = "Clear BIS Filter"
> > > Else
> > > Me.FilterOn = False
> > > Me.cmdFilterBIS.Caption = "Set BIS Filter"
> > > End If
> > >
> > > End Sub
> > >
> > > The reason I have inserted "BIS" is due to the need that once I have this
> > > filter working I need to create two additional filters for "Education" and
> > > "Health Care".
> > >
> > > Any help will be appreciated and your mentoring is very much valued!
> > >
> > > Uriel
> > >
> > > "Klatuu" wrote:
> > >
> > > > Well, you obviously have the logic working correctly, it would just be how
> > > > you are identify the filtering values.
> > > >
> > > > If you can give me some details on what you are doing, perhaps we can make
> > > > it work for you.
> > > > --
> > > > Dave Hargis, Microsoft Access MVP
> > > >
> > > >
> > > > "BIS-SISA" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > Happy to find this thread and I was successful in getting the code to work
> > > > > in my form but am puzzled by the filtered results.
> > > > >
> > > > > I have 1276 records that when I filter them it shows 1265 records. My goal
> > > > > is to see the 11 records filtered out. Any suggestions??
> > > > >
> > > > > Thanks!
> > > > >
> > > > > "Klatuu" wrote:
> > > > >
> > > > > > Sure, no problem.
> > > > > >
> > > > > > First, add a command button to your form. Give it the name cmdSetFilter.
> > > > > > Set the Caption to Set Filter
> > > > > > Now, to add the code, select the Events tab of the properties dialog. Find
> > > > > > the text box labled On Click.
> > > > > > Click on the small command button just to the right of the text box.
> > > > > > Choose Code Builder from the list that will pop up.
> > > > > > The Visual Basic Editor (VBE) will open and you will see:
> > > > > > Paste the code into the editor where the cursor is.
> > > > > >
> > > > > > Private Sub cmdSetFilter_Click()
> > > > > > |
> > > > > > End Sub
> > > > > >
> > > > > > Now, to use you check box name as an example. I put it in as the first
> > > > > > field name. Now let's get clear on terminology. When you say Field, that
> > > > > > means a field in a table or query. When you refer to a check box on a form,
> > > > > > that is a Control. So based on your statement, I am assuming [Business and
> > > > > > Enterprise] is the name of a field in the form's record source. Each of your
> > > > > > Check Boxes is probably bound to a field in the form's record source, so you
> > > > > > use the name of the field and the name of the control in the comparison. You
> > > > > > do it for each check box you want to include in th your filter. So:
> > > > > > v----Name of Field---------v v--Name of Control bound to field
> > > > > > "[Business and Enterprise] = " & Me.chkOne
> > > > > >
> > > > > >
> > > > > > Dim strFitler As String
> > > > > >
> > > > > > If Me.cmdSetFilter.Caption = "Set Filter" Then
> > > > > > strFilter = "[Business and Enterprise] = " & Me.chkOne & " AND
> > > > > > [Field2] = " &
> > > > > > Me.chkTwo & " AND [Field3] = " & Me.chkThree
> > > > > > Me.Filter = strFilter
> > > > > > Me.FilterOn = True
> > > > > > Me.cmdSetFilter.Caption = "Clear Filter"
> > > > > > Else
> > > > > > Me.FilterOn = False
> > > > > > Me.cmdSetFilter.Caption = "Set Filter"
> > > > > > End If
> > > > > >
> > > > > > --
> > > > > > Dave Hargis, Microsoft Access MVP

==============================================================================
TOPIC: In a report -Using a calculated field in Select Case causes Error
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/68b28ec3d6158796?hl=en
==============================================================================

== 1 of 4 ==
Date: Thurs, Aug 28 2008 12:02 pm
From: Maverick

I am trying to have certain boxes become visible or not based on the value of
a calculated field in a report. However, it gives me Error 2424 and points to
the calculated field Me.calcOverallRating as the problem. Any ideas why or
how to fix this?

My code is as follows:

Select Case Me.calcOverallRating

Case 0
Me.boxDoesNot.Visible = True

Case 0.5 To 1.4
Me.boxMeets.Visible = True

Case 1.5 To 2.4
Me.boxExceeds.Visible = True

Case 2.5 To 3
Me.boxOutstanding.Visible = True

Case Else
Me.boxDoesNot.Visible = False
Me.boxMeets.Visible = False
Me.boxExceeds.Visible = False
Me.boxOutstanding.Visible = False

End Select

== 2 of 4 ==
Date: Thurs, Aug 28 2008 1:01 pm
From: "Alex Dybenko"

Hi,
where do you run this code? You have to run it in corresponding report's
section format event

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"Maverick" <Maverick@discussions.microsoft.com> wrote in message
news:100A6A7C-D6A8-453E-92B8-B886932C027E@microsoft.com...
>I am trying to have certain boxes become visible or not based on the value
>of
> a calculated field in a report. However, it gives me Error 2424 and points
> to
> the calculated field Me.calcOverallRating as the problem. Any ideas why or
> how to fix this?
>
> My code is as follows:
>
> Select Case Me.calcOverallRating
>
> Case 0
> Me.boxDoesNot.Visible = True
>
> Case 0.5 To 1.4
> Me.boxMeets.Visible = True
>
> Case 1.5 To 2.4
> Me.boxExceeds.Visible = True
>
> Case 2.5 To 3
> Me.boxOutstanding.Visible = True
>
> Case Else
> Me.boxDoesNot.Visible = False
> Me.boxMeets.Visible = False
> Me.boxExceeds.Visible = False
> Me.boxOutstanding.Visible = False
>
> End Select

== 3 of 4 ==
Date: Thurs, Aug 28 2008 1:08 pm
From: Maverick

I've tried On Current, On Load and On Open. None work. The control is
available from the list after I type Me. so I know it's a valid control and
not typed incorrectly.

"Alex Dybenko" wrote:

> Hi,
> where do you run this code? You have to run it in corresponding report's
> section format event
>
> --
> Best regards,
> ___________
> Alex Dybenko (MVP)
> http://accessblog.net
> http://www.PointLtd.com
>
> "Maverick" <Maverick@discussions.microsoft.com> wrote in message
> news:100A6A7C-D6A8-453E-92B8-B886932C027E@microsoft.com...
> >I am trying to have certain boxes become visible or not based on the value
> >of
> > a calculated field in a report. However, it gives me Error 2424 and points
> > to
> > the calculated field Me.calcOverallRating as the problem. Any ideas why or
> > how to fix this?
> >
> > My code is as follows:
> >
> > Select Case Me.calcOverallRating
> >
> > Case 0
> > Me.boxDoesNot.Visible = True
> >
> > Case 0.5 To 1.4
> > Me.boxMeets.Visible = True
> >
> > Case 1.5 To 2.4
> > Me.boxExceeds.Visible = True
> >
> > Case 2.5 To 3
> > Me.boxOutstanding.Visible = True
> >
> > Case Else
> > Me.boxDoesNot.Visible = False
> > Me.boxMeets.Visible = False
> > Me.boxExceeds.Visible = False
> > Me.boxOutstanding.Visible = False
> >
> > End Select
>
>

== 4 of 4 ==
Date: Thurs, Aug 28 2008 1:23 pm
From: "Alex Dybenko"

Hmm, are we talking about report? not sure it has current event. try Format
even

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"Maverick" <Maverick@discussions.microsoft.com> wrote in message
news:6B0DB2CE-62E5-4D34-80BC-8CCD80269266@microsoft.com...
> I've tried On Current, On Load and On Open. None work. The control is
> available from the list after I type Me. so I know it's a valid control
> and
> not typed incorrectly.
>
> "Alex Dybenko" wrote:
>
>> Hi,
>> where do you run this code? You have to run it in corresponding report's
>> section format event
>>
>> --
>> Best regards,
>> ___________
>> Alex Dybenko (MVP)
>> http://accessblog.net
>> http://www.PointLtd.com
>>
>> "Maverick" <Maverick@discussions.microsoft.com> wrote in message
>> news:100A6A7C-D6A8-453E-92B8-B886932C027E@microsoft.com...
>> >I am trying to have certain boxes become visible or not based on the
>> >value
>> >of
>> > a calculated field in a report. However, it gives me Error 2424 and
>> > points
>> > to
>> > the calculated field Me.calcOverallRating as the problem. Any ideas why
>> > or
>> > how to fix this?
>> >
>> > My code is as follows:
>> >
>> > Select Case Me.calcOverallRating
>> >
>> > Case 0
>> > Me.boxDoesNot.Visible = True
>> >
>> > Case 0.5 To 1.4
>> > Me.boxMeets.Visible = True
>> >
>> > Case 1.5 To 2.4
>> > Me.boxExceeds.Visible = True
>> >
>> > Case 2.5 To 3
>> > Me.boxOutstanding.Visible = True
>> >
>> > Case Else
>> > Me.boxDoesNot.Visible = False
>> > Me.boxMeets.Visible = False
>> > Me.boxExceeds.Visible = False
>> > Me.boxOutstanding.Visible = False
>> >
>> > End Select
>>
>>

==============================================================================
TOPIC: Access to Outlook Via Excel
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/96746cf9b3eb0195?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Aug 28 2008 12:08 pm
From: dch3

First, what is the intent? Are you trying to generate HTML-formatted emails
from Access? When your user says they don't want to see grid-lines are you
trying to send data formatted as a table in the message?

Second, you cannot, ever, never-in-a-million years, absolutely control what
the user sees due to the wide variety of machines, browsers, email programs
and other what not. You can greatly influence, but absolute control is an
absolute impossibility.

(Now that I'm off my soapbox...)
If you're trying to send HTML formatted email (which I think you are but may
not know it), you can create the HTML directly in Access using the
appropriate HTML tags and then set the .HTMLBody of the MailItem. I'm
assuming that theres a HTML table in the message based on the 'no gridlines'
comment. If so, you'll be using <table><tr><td></td></tr></table> to create
said. In the <table> tag using border=0 to supress the border. At any rate if
you are trying to send an HTML formatted email its not necessary to use
Excel, however you will need a familarity with HTML in order to create the
string to set the .HTML body to. Be aware though MS has chosen to modify the
HTML rendering engine used for Outlook 2007. (You *do not* have to go through
Word to create a HTML message as setting the .HTMLBody is all you have to
do.) As such there are certain tags that can't be used. The basic tags are
still usable, but its the tags such as <object></object> that can no longer
be used. For more information see these articles...

http://msdn.microsoft.com/en-us/library/aa338201.aspx
http://msdn.microsoft.com/en-us/library/aa171418(office.11).aspx
http://msdn.microsoft.com/en-us/library/bb175500.aspx
http://msdn.microsoft.com/en-us/library/bb207133.aspx
http://www.w3schools.com/html/default.asp
Also, you

"Pradeep" wrote:

> Hello,
>
> I am using Ms Access 2003. Per user requirement, I need to format the data
> in a table and paste the same in outlook in such a way the gridlines etc do
> not appear.
>
> So i have written code in VBA (MS Access) which takes bits of information,
> copies it to an excel sheet, formats it per the requirement specifications
> and then moves it to Outlook.
>
> Essentially i am trying to paste data into a outlook mailitem with a word
> document viewer. The pre-requisite for this is:
>
> 1. Ensure that the Edit messages with Microsoft Word 2003 is selected in
> Outlook under the Mail Format tab.
>
> Here is the main segment of code i am using:
>
> Dim objOutlook as object, objMailWindow as object, objInspector as object,
> ObjDoc as word.Document, objExcel as object
>
> set objOutlook= CreateObject("Outlook.Application")
>
> set objMailWindow=objOutlook.MailItem(0)
>
> with ObjMailWindow
> set objInspector=.GetInspector
> set objDoc=objInspector.WordEditor
> End with
>
> Now i move bits of information from Ms Access table into an Excel Workbook
> object, format it per user requirements and then move it to out look with the
> following statement.
>
> objExcel.Selection.Copy
>
> with objDoc
> .Activate
> .Selection.PasteExcelTable False,False,False
> End with
>
> Now the formatting is perfect and it appears as intended in Outllook. But
> when the email is sent, the recepient receives it in a different format where
> in he sees the grid lines.
>
> How do i avoid it. The user wants to see it as it appears without any grid
> lines.
>
> Any help in this regard would be highly appreciated.
>
> Thanks,
>
> Pradeep

==============================================================================
TOPIC: Change recordsource of susequent subforms-TJ
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/5e247a6c6176bb8a?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Aug 28 2008 12:12 pm
From: ambushsinger

I have a subform with a combo box containing User ID's ("CKENT"). The next
suform will be the user profile with listbox ("Coordinator" or "Inspector")
etc...based on the user id and a check box to show the current profile of the
selected user.
The next subform will be Contract number which shows the current contract
number being worked on.
When I change the User ID ("BWAYNE") the user profile recordsource should
update and populate the user profile and the contract number.
Any ideas???

==============================================================================
TOPIC: Possible to have each procedure called listed?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/2ed74e7b351f1992?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Aug 28 2008 12:18 pm
From: markmarko

> Sure, you can always get the history, if you insert a:
>
> Debug.Print "subroutineName here -- start "
>
> at the start of each of your subroutine where their start is to be tracked.

So simple... for some reason, I was fixated on some sort of built-in
functionality.

That would work nicely. Thanks!

==============================================================================
TOPIC: Sending email from access - Reference Stephen Lebans code
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/4790bc6a818ef8d5?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Aug 28 2008 12:21 pm
From: dch3

I'm not familar with the code that you refer to, however if its using the
Outlook object model to create a MailItem, the .Send Property is the one that
actually sends the email. If its in the code, replace it with .Display.

See...
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.outlook._mailitem.display.aspx

If you need code to actually create the MailItem see...
http://msdn.microsoft.com/en-us/library/aa220082(office.11).aspx

To attach the file, see
http://msdn.microsoft.com/en-us/library/aa210901(office.11).aspx

"Irshad Alam" wrote:

> I have used the code and dll files after downloading from Mr. Stephen Lebans
> website for converting the report to pdf (which have also dynapdf.dll &
> strstorage.dll) . The following code I used which worked perfectly :
>
> Private Sub Label59_Click()
> RepoToPdfWC
> End Sub
>
> Function RepoToPdfWC()
> Dim blRet As Boolean
> blRet = ConvertReportToPDF(Me.Text60, vbNullString,
> "C:\WsImpFiles\MyClaimedPDF\" & "Job-" & Me.JobNo & "-ClaimRef." &
> Me.ClaimRefNo.Value & ".PDF", False)
> End Function
>
> Note : Me.Text60 contains the report name
>
> The above sucessfully converts to pdf and open the pdf into acrobat 5
>
> I want to add more codes and edit on above to get my below requirement
>
> 1 It should not open the acrobat for preview
> 2. it should open outlook express 6 - new email message and attach the above
> created pdf and according to below BUT should not send, so that I can have a
> final view before sending :
>
> Email as below :
>
> From: = mirshadalam@hotmail.com
> To: = irshad2.gthe@ggcompany.com
> CC: = naaeems.gthe@ggcompany.com
> Subject: = "Job-" & Me.JobNo & "-ClaimRef." & Me.ClaimRefNo.Value
> Body = "Reference is made to subject claim, attached pls. find the claim
> form as pdf"
> Bodysecondline="Thanks"
> Bodythirdline="Irshad"
> AttachmentFile="C:\WsImpFiles\MyClaimedPDF\" & "Job-" & Me.JobNo &
> "-ClaimRef." & Me.ClaimRefNo.Value & ".PDF"
>
>
> Note2 : I am using Windows XP svcpack2 and outlook express 6 , ms office 2000
>
> Please advice, it will help me to reduce job time of hours.
>
> Regards
>
> Irshad
>
>
>
>
>
>
>

==============================================================================
TOPIC: Parse Table Names from Query Def SQL
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/17e9d9c3e5f0f561?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Aug 28 2008 12:23 pm
From: Leon J

Thanks for the reply, and it's great as far as it goes. What I'm really
looking for (and couldn't find after searching for over an hour myself) is if
someome has already written the parsing code, especially with whatever
recursion or similar method that is needed to get to table names within
subqueries.

Thanks again for getting me going.

"Alex Dybenko" wrote:

> Hi,
> I think I saw such routine in Access developers handbook, basic idea is that
> you parse query SQL and get tables/queries name from there.
> perhaps this will help:
> http://www.vbforums.com/archive/index.php/t-314247.html
>
> --
> Best regards,
> ___________
> Alex Dybenko (MVP)
> http://accessblog.net
> http://www.PointLtd.com
>
> "Leon J" <Leon J@discussions.microsoft.com> wrote in message
> news:703644D7-E23C-47D3-A396-30888FE7FF02@microsoft.com...
> >I would like to be able to determine all the tables that are referenced
> >with
> > in a querydef (and any subqueries within it, too). Is there a written
> > library for this or has someone written a routine to do it?
> >
> > Thanks for any help,
> >
> > Leon
> >
>
>

==============================================================================
TOPIC: Access and Web Development
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/62e7f93a47b4ec79?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Aug 28 2008 12:57 pm
From: "Alex Dybenko"

Hi,
not exactly. With SharePoint you can create tables (called lists in
SharePoint) and make web forms so users can enter information there.
Then you can link access mdb to that lists, so same time you can enter data
in access.
of course there are some limitations with these lists.
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"FatMan" <FatMan@discussions.microsoft.com> wrote in message
news:C021DFB4-B764-40A5-BB77-CBD307FD75CE@microsoft.com...
> Alex:
> Are you saying that I can use SharePoint server on a server that is
> available to my users (outside of the company) so they can gain access to
> my
> database program? What would the meechanics be to accomplish something
> like
> this? I have no experience with SharPoint server.
>
> Thanks for your comments,
> FatMan
>
> "Alex Dybenko" wrote:
>
>> Hi,
>> you can also consider using SharePoint server and then link its tables to
>> access. no need to program at all.
>>
>> --
>> Best regards,
>> ___________
>> Alex Dybenko (MVP)
>> http://accessblog.net
>> http://www.PointLtd.com
>>
>> "FatMan" <FatMan@discussions.microsoft.com> wrote in message
>> news:6EFCAFD2-3EC5-404E-B429-A75E610D0BDB@microsoft.com...
>> > Rick:
>> > Thanks for the comments.
>> >
>> > Yes, it is a new web server and whatever language will be used to
>> > complete
>> > the interface between the web and my existing access database will need
>> > to
>> > be
>> > learnt. I have done some searches at the local (Canadian)
>> > universities/colleges and have found courses being offered in Java, C,
>> > C++
>> > and C# but not ASP.net. As I have said I have searched Canadian
>> > universities/colleges for online courses but do not/will not limit
>> > myself
>> > to
>> > Canadian schools if someone knows of a good language and course to go
>> > with
>> > it.
>> >
>> > Do you know of a good course in ASP.net?
>> >
>> > Thanks,
>> > FatMan
>> >
>> > "Rick Brandt" wrote:
>> >
>> >> FatMan wrote:
>> >> > Hi all:
>> >> > I have a database program that I would like to add a web interface
>> >> > for. That is to say I would like users to be able to navigate to my
>> >> > database through the web, log into the interface and
>> >> > add/modify/delete records that pertain only to them.
>> >> >
>> >> > The database and code is ready to go but I would like to hear what
>> >> > peoples thoughts and opinions are as to what programming language is
>> >> > best to use for the web interface and why? Should I use Java, C,
>> >> > C++, C#, etc.
>> >> >
>> >> > All replies are greatly appreciated.
>> >>
>> >> What is already in place? Do you already have a web server or will
>> >> you
>> >> be
>> >> setting up a new one? If an existing one you will need to see which
>> >> of
>> >> those it will support and which would require changes.
>> >>
>> >> Which technology/language are you already familiar with? That would
>> >> be
>> >> the
>> >> best choice. If you are familiar with none of them I'm sure
>> >> representatives/advocates of each would provide many reasons why their
>> >> preferred platform is the one you should use. I imagine that ASP.Net
>> >> would
>> >> be among the easiest to learn from scratch. At least for someone
>> >> already
>> >> famiiar with other MS development tools.
>> >>
>> >> --
>> >> Rick Brandt, Microsoft Access MVP
>> >> Email (as appropriate) to...
>> >> RBrandt at Hunter dot com
>> >>
>> >>
>> >>
>>
>>

==============================================================================
TOPIC: Create Code to have a table field formatted
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/81a0748e72af3ae7?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Aug 28 2008 1:10 pm
From: "Dirk Goldgar"

"Sondra" <Sondra@discussions.microsoft.com> wrote in message
news:651EC9DF-40A4-427B-B68A-A6C170073EB3@microsoft.com...
> Dirk:
>
> Only the time shows in the mailmerge. I'm usinhg the tbl_DSCRMerge for
> the
> word document mailmerge process.
>
> I've checked the formatting of the field in tbl_DSCRMerger and it is a
> date/time field and the formatting is blank.

At this point I'm at a loss. *Something* is formatting the field as a time,
not as a date. My best guess now is that it's something in the options of
the mail-merge. One possibility is that there's a formatting option applied
to the merge field in the Word document. I'm not an expert on Word, but I
gather that you can select the merge fields in the document and press
Shift+F9 to see their coding. Then you can add or remove a format string.
See the discussion thread at this link:

http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.word.mailmerge.fields&mid=4edd6841-3608-43d1-8509-eee097d736c2&sloc=en-us

That would be one possibility.

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

(please reply to the newsgroup)

==============================================================================
TOPIC: DCount multiple criteria (date range and text field)
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0bfcc448f5065a8b?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Aug 28 2008 1:23 pm
From: KenR

I am trying to count the number of records in a table that meet a specified
date/time range in one field (EntryDate) and the username in a second field
(EntryBy).

'***********
Dim dtMin As Date
Dim dtmax As Date
Dim stUser As String
Dim intArrived As Integer
stUser = CurrentUser()
dtMin = #8/28/2008 9:05:01 AM#
dtmax = #8/28/2008 9:59:59 AM#
intArrived = DCount("*", "[Details]", "[EntryDate] Between #' & dtMin & '#
And #' & dtmax & '#' And [EntryBy] = stUser)
'******************

I get a syntax error when I try it, and can't figure out the correct syntax.

I'm using these static values in dtMin and dtMax for testing purposes; the
live code uses dMin and dMax functions to determine live values.

"Details" is the table
"EntryDate" is a date/time field with a general date format
"EntryBy" is a text field with 15 characters max

This code:
*********
intArrived = DCount("ID", "Details", "ShortEntryDate Between #" & dtMin & "#
And #" & dtmax & "#")
*********
works fine , but doesn't add the second criterion (current user) to find the
correct records, so the count includes records entered in that date range by
another person.

Can anyone help me with the syntax?

Thanks

KenR

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

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

25 new messages in 13 topics - digest

2008-02-28T15:19:00.000-08:00

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: Merging with Word
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/6d6999668610699e?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Feb 28 2008 9:41 am
From: mikelee101@hotmail.com

Hello,
I am trying to do something that I'm not sure is possible, but was
hoping someone could show me a way. I have a database on a shared
drive that multiple users access concurrently. The database is not
split. I am trying to allow a "one click" generation of various form
letters that my team needs to fax/mail/email. In searching the forum,
I've found and tried the following possible solutions:

--Reports don't seem to work, as we have images that need to be
output. We don't have Acrobat standard, nor do we have admin rights
to our machines, so installing a pdf writer would be difficult. Our
only real choice of output is rtf (Word), which won't carry the
images.
--I also checked out Albert Kallal's WordMerge utility but, per the
info on his site, multiple users "will trip over each other" if the
database isn't split. I know almost nothing about splitting
databases, but want to maintain continuity in Forms, Reports, Queries,
etc., so it doesn't sound like that's something I want to do either.

So, what I tried is to have Access open a Word Doc and fill in the
information that I want carried over. I have code in Access that will
open the Word Doc. I have code in the Word Doc that will insert the
necessary information in the correct places. What I don't have is a
way to pass the variables between applications. Here is how I'm
opening the Word Doc:

Set WordApp = CreateObject("Word.Application")
WordApp.Visible = True
Set WordDoc = WordApp.Documents.Open("~File path and file name~")

What I'm looking for is some sort of line like this:

WordDoc.Call.ChangeFields(DomNme, ContNme, ExpDte, EmlAddr)

However, none of the syntax that I've tried has worked, and I couldn't
find much in the VBA help files of either Access or Word. Access2000,
Word2003, XPPro SP2.

If anyone has any ideas, or can tell me that I'm chasing shadows and
can therefore stop, I'd really appreciate it.

Thanks to all.

Mike Lee
Coppell, TX, USA

==============================================================================
TOPIC: Access xp - Why does debugger break in when I have no breakpoi
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/a8a243abe3a1ed94?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Feb 28 2008 9:49 am
From: JDRaven

Thanks. I tried that first, and it did not resolve my particular problem.

And thanks for not pointing out my basic math miscalc: the original post was
3 years old and not 2 ;o)

Have a great day!

"Robert Morley" wrote:

> Another common method is to set and clear a breakpoint on the line that has
> the phantom breakpoint. I've had fairly good luck with that method so far,
> and it's nice and quick.
>
>
> Rob
>
> JDRaven wrote:
> > 2 years later, and this post just helped me resolve a problem that cropped up
> > today with an Access database somebody else wrote. Your post has withstood
> > the test of time.
> >
> > Thanks!
> >
> > "Ken Snell [MVP]" wrote:
> >
> >> VBE sometimes remembers "phantom" breakpoints.
> >>
> >> Two ways that I use to fix this problem.
> >>
> >> (1) Comment out the line on which the breakpoint occurs. If the code won't
> >> compile without that line, then comment out a block of code so that the code
> >> can compile without it. Compile the code. Close the database. Open the
> >> database. Uncomment out the code. Compile the code.
> >>
> >> (2) Use the undocumented decompile switch when opening the database so that
> >> all compiled code is uncompiled.
> >> "PathToAccessExecutable\msaccess.exe" /decompile
> >> "PathToYourDataBaseFile\DatabaseFileName.mdb"
> >>
> >>
> >> --
> >>
> >> Ken Snell
> >> <MS ACCESS MVP>
> >>
> >>
> >> "DJ Becker" <DJ Becker@discussions.microsoft.com> wrote in message
> >> news:DA0E9301-0716-4E1E-86C3-04C2D15D2F34@microsoft.com...
> >>> I've made a change to an app that's been running for years. After adding
> >>> some code, now the debugger breaks in at a particular line even though I
> >>> have
> >>> no breakpoint set there - I've Cleared All Breakpoints from the Debug
> >>> menu.
> >>> If I click continue, the app continues without error. I need this code to
> >>> keep running - the users will not know what to do and shouldn't have to
> >>> deal
> >>> with it anyway. Any ideas?
> >>
> >>
>

==============================================================================
TOPIC: how to enter data simultaneously to two different tables in Access
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0d72131113b6ee88?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Feb 28 2008 10:19 am
From: John W. Vinson

On Thu, 28 Feb 2008 01:56:02 -0800, Kate <Kate@discussions.microsoft.com>
wrote:

>I would like to know how to program the database so the data can be entered
>simultaneously to two different columns in two different tables at the same
>time. For example, I have Table 1, and one column within that table called
>"proejct #". I have Table 2, again with one column called project #. How do
>I set up the database so I can enter projec numbers to these two tables
>simultaneously. Any help would be greatly appreciated.
>
>Kate

You wouldn't, generally. Creating an empty "placeholder" record in Table2 is
very rarely a good idea!

If table1 is related one-to-many to table2, typically you would use a Form
based on table1 with a Subform based on table2, using [Project #] as the
master/link field. This will ensure that Table2 inherits the project number as
soon as there is any data entered into Table2 (but not before, because it
isn't needed before!).

Just a side note - you may want to avoid the use of special characters such as
blanks and # in fieldnames. # is a date delimiter and Access can get confused.
A field named ProjectNo will be less hassle and will let you upgrade more
easily to SQL/Server if that should ever come to pass. If you do use Project #
then you must ALWAYS enclose the fieldname in brackets - [Project #].
--
John W. Vinson [MVP]

==============================================================================
TOPIC: Are there specific compilers required for C programming?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/a64256192545373e?hl=en
==============================================================================

== 1 of 3 ==
Date: Thurs, Feb 28 2008 10:20 am
From: John W. Vinson

On Thu, 28 Feb 2008 07:22:01 -0800, Michaela <granyausom@aol.com> wrote:

Yes. There are many C compilers on the market, and C code is utterly useless
unless it's compiled. Different compilers will often have subtle dialect
differences in the syntax and features of the language.

I'm curious that you're asking this question in a newsgroup supporting the
altogether different program Microsoft Access.
--
John W. Vinson [MVP]

== 2 of 3 ==
Date: Thurs, Feb 28 2008 1:17 pm
From: Michaela

Thank you for your response. a few years ago I had taken a course in C++
programming to determine the basis of most programs. It was very interesting
and gave me a little background [very basic] information on programming set
up for accomplishing a way to provide a source for our company to handle
orders etc. After that experience I handled our orders, inventory and
various other information with Lotus Smart Suite and QuickBookPro. My
husband and I had a Company that was abnormal and did not handle ordinary
"widgets". It is a semi service company that reclaims industrial oils.
Basically it acquires oil from the customer and reprocesses it to a "like
new" oil and returns it to the customer at the cost of restoration. This
company is currently being run and handled by our children. I still help
with refining their processes and hoped to learn to use Microsoft Access
enough to change over to confirm to the majority of the users in the
industries.
--
Mickey

"John W. Vinson" wrote:

> On Thu, 28 Feb 2008 07:22:01 -0800, Michaela <granyausom@aol.com> wrote:
>
> Yes. There are many C compilers on the market, and C code is utterly useless
> unless it's compiled. Different compilers will often have subtle dialect
> differences in the syntax and features of the language.
>
> I'm curious that you're asking this question in a newsgroup supporting the
> altogether different program Microsoft Access.
> --
> John W. Vinson [MVP]
>

== 3 of 3 ==
Date: Thurs, Feb 28 2008 3:15 pm
From: John W. Vinson

On Thu, 28 Feb 2008 13:17:03 -0800, Michaela <granyausom@aol.com> wrote:

>Thank you for your response. a few years ago I had taken a course in C++
>programming to determine the basis of most programs. It was very interesting
>and gave me a little background [very basic] information on programming set
>up for accomplishing a way to provide a source for our company to handle
>orders etc. After that experience I handled our orders, inventory and
>various other information with Lotus Smart Suite and QuickBookPro. My
>husband and I had a Company that was abnormal and did not handle ordinary
>"widgets". It is a semi service company that reclaims industrial oils.
>Basically it acquires oil from the customer and reprocesses it to a "like
>new" oil and returns it to the customer at the cost of restoration. This
>company is currently being run and handled by our children. I still help
>with refining their processes and hoped to learn to use Microsoft Access
>enough to change over to confirm to the majority of the users in the
>industries.

ok... that's interesting... but irrelevant.

C is one language (actually a family of older languages).

C++ is a QUITE DIFFERENT language, one which grew out of C but is distinct.

Neither one is used in ordinary Access database developement (though I believe
large parts of the Access executable were written in C, C++ or C# or some
combination of the above).

In any case, there is no C (or C++ or C#) compiler in Access, and Access would
not be a suitable programming enviornment for such programs.

Do you want to develop applications for this business in Access (for which the
programming language of choice is Visual Basic for Applictions, aka VBA)? or
in C? or in C++? Either of the latter will be a VAST amount more work.

--
John W. Vinson [MVP]

==============================================================================
TOPIC: How to access an instance of a report that is generated?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/d0ed78e507f14137?hl=en
==============================================================================

== 1 of 2 ==
Date: Thurs, Feb 28 2008 10:58 am
From: Wayne-I-M

Not sure I understand this. Anything that can be created in a report can
also be create on a form. So instead of running the 1st report you could
create a form and run the "2nd" (?) report from that

Just an idea

--
Wayne
Manchester, England.

"Archer" wrote:

> I need to work on data that's created once the report's generated and based
> on it either generate a new report.
> or
> write a macro to generate a report based on the conditions I set based on a
> query I run on the database tables
>
> Could anyone happen to know anything related to this?
>

== 2 of 2 ==
Date: Thurs, Feb 28 2008 1:23 pm
From: John W. Vinson

On Thu, 28 Feb 2008 09:47:02 -0800, Archer <Archer@discussions.microsoft.com>
wrote:

>I need to work on data that's created once the report's generated and based
>on it either generate a new report.
>or
>write a macro to generate a report based on the conditions I set based on a
>query I run on the database tables
>
>Could anyone happen to know anything related to this?

Well, both Forms and Reports are just tools. There is no data stored in a
report, and you can't search or reuse the "generated report"'s data! The data
exists solely in the table, perhaps combined with calculated fields in a
Query.

Could you explain more about the nature of your data and of the report you
want to generate?
--
John W. Vinson [MVP]

==============================================================================
TOPIC: Access 97 => 2003
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/285c89a0db9cb6aa?hl=en
==============================================================================

== 1 of 6 ==
Date: Thurs, Feb 28 2008 11:21 am
From: KarenFio

I converted an Access 97 database to 2003 and a form that uses the following
code to choose a start date for a report did not work:

Private Sub TDDReportTime_AfterUpdate()
Select Case Me.TDDReportTime
Case 1
MyStartDate = DateAdd("d", -1, Date)
Case 2
MyStartDate = DateAdd("d", -2, Date)
Case 3
MyStartDate = DateAdd("d", -7, Date)
Case 4
MyStartDate = DateAdd("d", -14, Date)
Case 5
MyStartDate = DateAdd("m", -1, Date)
Case 6
MyStartDate = DateAdd("m", -2, Date)
Case 7
MyStartDate = DateAdd("m", -3, Date)
Case 8
MyStartDate = #10/1/2004#

Case 9
MyStartDate = #10/1/2005#
Case 10
MyStartDate = #10/1/2006#
Case 11
MyStartDate = #4/15/2006#
Case 12
MyStartDate = #10/1/2007#

End Select
Me.Requery

The text box that should display the date has the message "#Name?".

Access 2003 keeps crashing with the converted database. I have tried
turning of Name AutoCorrect.

Any suggstions would be greatly appreciated. Thanks.

== 2 of 6 ==
Date: Thurs, Feb 28 2008 11:32 am
From: Wayne-I-M

this works fine when testing - I just ran it on a small unbound form and it
seems OK. They may be another problem not connected with this option group

Option Compare Database
Option Explicit

Private Sub TDDReportTime_AfterUpdate()
Select Case Me.TDDReportTime
Case 1
MyStartDate = DateAdd("d", -1, Date)
Case 2
MyStartDate = DateAdd("d", -2, Date)
Case 3
MyStartDate = DateAdd("d", -7, Date)
Case 4
MyStartDate = DateAdd("d", -14, Date)
Case 5
MyStartDate = DateAdd("m", -1, Date)
Case 6
MyStartDate = DateAdd("m", -2, Date)
Case 7
MyStartDate = DateAdd("m", -3, Date)
Case 8
MyStartDate = #10/1/2004#
Case 9
MyStartDate = #10/1/2005#
Case 10
MyStartDate = #10/1/2006#
Case 11
MyStartDate = #4/15/2006#
Case 12
MyStartDate = #10/1/2007#
End Select
End Sub

--
Wayne
Manchester, England.

"KarenFio" wrote:

> I converted an Access 97 database to 2003 and a form that uses the following
> code to choose a start date for a report did not work:
>
> Private Sub TDDReportTime_AfterUpdate()
> Select Case Me.TDDReportTime
> Case 1
> MyStartDate = DateAdd("d", -1, Date)
> Case 2
> MyStartDate = DateAdd("d", -2, Date)
> Case 3
> MyStartDate = DateAdd("d", -7, Date)
> Case 4
> MyStartDate = DateAdd("d", -14, Date)
> Case 5
> MyStartDate = DateAdd("m", -1, Date)
> Case 6
> MyStartDate = DateAdd("m", -2, Date)
> Case 7
> MyStartDate = DateAdd("m", -3, Date)
> Case 8
> MyStartDate = #10/1/2004#
>
> Case 9
> MyStartDate = #10/1/2005#
> Case 10
> MyStartDate = #10/1/2006#
> Case 11
> MyStartDate = #4/15/2006#
> Case 12
> MyStartDate = #10/1/2007#
>
>
>
> End Select
> Me.Requery
>
>
> The text box that should display the date has the message "#Name?".
>
> Access 2003 keeps crashing with the converted database. I have tried
> turning of Name AutoCorrect.
>
> Any suggstions would be greatly appreciated. Thanks.
>

== 3 of 6 ==
Date: Thurs, Feb 28 2008 11:42 am
From: KarenFio

Thanks for testing it. I'll have to see what else it might be.

Another thing I forgot to mention which is probably more urgent is that I
can't open, go into design view, or print preview any of my reports in other
databases created in Access 2003 after I converted this database. I can
still do everything in Access 97.

Any suggestions or ideas would be appreciated.

"Wayne-I-M" wrote:

> Hi
>
> this works fine when testing - I just ran it on a small unbound form and it
> seems OK. They may be another problem not connected with this option group
>
>
>
>
> Option Compare Database
> Option Explicit
>
> Private Sub TDDReportTime_AfterUpdate()
> Select Case Me.TDDReportTime
> Case 1
> MyStartDate = DateAdd("d", -1, Date)
> Case 2
> MyStartDate = DateAdd("d", -2, Date)
> Case 3
> MyStartDate = DateAdd("d", -7, Date)
> Case 4
> MyStartDate = DateAdd("d", -14, Date)
> Case 5
> MyStartDate = DateAdd("m", -1, Date)
> Case 6
> MyStartDate = DateAdd("m", -2, Date)
> Case 7
> MyStartDate = DateAdd("m", -3, Date)
> Case 8
> MyStartDate = #10/1/2004#
> Case 9
> MyStartDate = #10/1/2005#
> Case 10
> MyStartDate = #10/1/2006#
> Case 11
> MyStartDate = #4/15/2006#
> Case 12
> MyStartDate = #10/1/2007#
> End Select
> End Sub
>
> --
> Wayne
> Manchester, England.
>
>
>
> "KarenFio" wrote:
>
> > I converted an Access 97 database to 2003 and a form that uses the following
> > code to choose a start date for a report did not work:
> >
> > Private Sub TDDReportTime_AfterUpdate()
> > Select Case Me.TDDReportTime
> > Case 1
> > MyStartDate = DateAdd("d", -1, Date)
> > Case 2
> > MyStartDate = DateAdd("d", -2, Date)
> > Case 3
> > MyStartDate = DateAdd("d", -7, Date)
> > Case 4
> > MyStartDate = DateAdd("d", -14, Date)
> > Case 5
> > MyStartDate = DateAdd("m", -1, Date)
> > Case 6
> > MyStartDate = DateAdd("m", -2, Date)
> > Case 7
> > MyStartDate = DateAdd("m", -3, Date)
> > Case 8
> > MyStartDate = #10/1/2004#
> >
> > Case 9
> > MyStartDate = #10/1/2005#
> > Case 10
> > MyStartDate = #10/1/2006#
> > Case 11
> > MyStartDate = #4/15/2006#
> > Case 12
> > MyStartDate = #10/1/2007#
> >
> >
> >
> > End Select
> > Me.Requery
> >
> >
> > The text box that should display the date has the message "#Name?".
> >
> > Access 2003 keeps crashing with the converted database. I have tried
> > turning of Name AutoCorrect.
> >
> > Any suggstions would be greatly appreciated. Thanks.
> >

== 4 of 6 ==
Date: Thurs, Feb 28 2008 11:48 am
From: Wayne-I-M

I think (just my opinion) that the best site you can look at for 97 -> 2K3 is
this

http://allenbrowne.com/ser-48.html

Have a look and see what you can find. Sorry can't be much help as I have
never seen your problem with the design view. When we converted (back in the
day) it seemed to go quite smoothly

--
Wayne
Manchester, England.

"KarenFio" wrote:

> Thanks for testing it. I'll have to see what else it might be.
>
> Another thing I forgot to mention which is probably more urgent is that I
> can't open, go into design view, or print preview any of my reports in other
> databases created in Access 2003 after I converted this database. I can
> still do everything in Access 97.
>
> Any suggestions or ideas would be appreciated.
>
> "Wayne-I-M" wrote:
>
> > Hi
> >
> > this works fine when testing - I just ran it on a small unbound form and it
> > seems OK. They may be another problem not connected with this option group
> >
> >
> >
> >
> > Option Compare Database
> > Option Explicit
> >
> > Private Sub TDDReportTime_AfterUpdate()
> > Select Case Me.TDDReportTime
> > Case 1
> > MyStartDate = DateAdd("d", -1, Date)
> > Case 2
> > MyStartDate = DateAdd("d", -2, Date)
> > Case 3
> > MyStartDate = DateAdd("d", -7, Date)
> > Case 4
> > MyStartDate = DateAdd("d", -14, Date)
> > Case 5
> > MyStartDate = DateAdd("m", -1, Date)
> > Case 6
> > MyStartDate = DateAdd("m", -2, Date)
> > Case 7
> > MyStartDate = DateAdd("m", -3, Date)
> > Case 8
> > MyStartDate = #10/1/2004#
> > Case 9
> > MyStartDate = #10/1/2005#
> > Case 10
> > MyStartDate = #10/1/2006#
> > Case 11
> > MyStartDate = #4/15/2006#
> > Case 12
> > MyStartDate = #10/1/2007#
> > End Select
> > End Sub
> >
> > --
> > Wayne
> > Manchester, England.
> >
> >
> >
> > "KarenFio" wrote:
> >
> > > I converted an Access 97 database to 2003 and a form that uses the following
> > > code to choose a start date for a report did not work:
> > >
> > > Private Sub TDDReportTime_AfterUpdate()
> > > Select Case Me.TDDReportTime
> > > Case 1
> > > MyStartDate = DateAdd("d", -1, Date)
> > > Case 2
> > > MyStartDate = DateAdd("d", -2, Date)
> > > Case 3
> > > MyStartDate = DateAdd("d", -7, Date)
> > > Case 4
> > > MyStartDate = DateAdd("d", -14, Date)
> > > Case 5
> > > MyStartDate = DateAdd("m", -1, Date)
> > > Case 6
> > > MyStartDate = DateAdd("m", -2, Date)
> > > Case 7
> > > MyStartDate = DateAdd("m", -3, Date)
> > > Case 8
> > > MyStartDate = #10/1/2004#
> > >
> > > Case 9
> > > MyStartDate = #10/1/2005#
> > > Case 10
> > > MyStartDate = #10/1/2006#
> > > Case 11
> > > MyStartDate = #4/15/2006#
> > > Case 12
> > > MyStartDate = #10/1/2007#
> > >
> > >
> > >
> > > End Select
> > > Me.Requery
> > >
> > >
> > > The text box that should display the date has the message "#Name?".
> > >
> > > Access 2003 keeps crashing with the converted database. I have tried
> > > turning of Name AutoCorrect.
> > >
> > > Any suggstions would be greatly appreciated. Thanks.
> > >

== 5 of 6 ==
Date: Thurs, Feb 28 2008 11:53 am
From: Wayne-I-M

I just found this which may be of some use

http://www.access-programmers.co.uk/forums/archive/index.php/t-93958.html

--
Wayne
Manchester, England.

"KarenFio" wrote:

== 6 of 6 ==
Date: Thurs, Feb 28 2008 11:53 am
From: Klatuu

I think you have a VBA Library Reference problem. When you change version,
the library files the old version was using may be no longer on your system
or they may not work with the current version.

When you change versions, check your references to ensure they are not
missing and that they are using the correct version of the library.
--
Dave Hargis, Microsoft Access MVP

"KarenFio" wrote:

==============================================================================
TOPIC: Printing Sub Reports in Access
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/c6c09f52dcba93a9?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Feb 28 2008 12:36 pm
From: dimpie

Both the subreports have data in Print Preview mode. But when i have to
print, then it prints a blank page.

There is no data printer by the printer ever though i see data in the print
prview mode

Thanks!!

"NetworkTrade" wrote:

> you are saying that one of the subreports shows data on screen; but is blank
> when printed?
>
> or are you saying that there is a blank page appended to the end of the
> report when printed?
> --
> NTC
>
>
> "dimpie" wrote:
>
> > Ya i have tried printing on 2 diff printers and also a Pdf writer. Non works.
> >
> > Any ideas Please!!
> >
> > "NetworkTrade" wrote:
> >
> > > are you able to sanity check and compare behavior on a different PC/printer?
> > > --
> > > NTC
> > >
> > >
> > > "dimpie" wrote:
> > >
> > > > Hi - I have 2 sub reports included in one main report. Two sub reports run on
> > > > different queries.
> > > >
> > > > In the Print Preview of the main report, it is fine. But when i try to print
> > > > on a printer, it prints a blank page.
> > > >
> > > > Please HELP!!!!!
> > > >
> > > > Really appreciate your time.
> > > >
> > > > Thanks.

==============================================================================
TOPIC: Using Excel from Access
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/47873f5aa8db5ce8?hl=en
==============================================================================

== 1 of 2 ==
Date: Thurs, Feb 28 2008 1:01 pm
From: Daniel Pineault

Try the following code,

Function Print_TOC_Chart()
Dim xlApp As Object
Dim xlWrkBk As Object
Dim xlWrkSht As Object

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application") 'Bind to existing instance
of Excel

If Err.Number <> 0 Then
'Could not get instance of Excel, so create a new one
Err.Clear
'On Error GoTo Err_Handler
Set xlApp = CreateObject("excel.application")
Else
'On Error GoTo Err_Handler
End If

'xlApp.Visible = True 'make excel visible to the user
Set xlWrkBk = xlApp.Workbooks.Open("\\hn-s-fileserv1\sharedmfg\mfg" & _
"'\CFM\Reports\TOC_Reports\Workcenter TOC Reports.xls")

'Call Set_Printer(ExcelApp, Me.ComboBox_prtSelect.Value)

Set xlWrkSht = xlApp.Worksheets("WorkBookSheetName")

With xlWrkSht.PageSetup
.PrintArea = "B2:D4"
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
.Orientation = xlLandscape
End With

xlWrkSht.PrintOut Copies:=1

ExcelApp.WkBk.Close False
End Function

It work for me, but I did not try the change printer aspect of it. If I
have the time I will look at it and post back with the solution, but this
should get you one step closer to the solution.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com
If this post was helpful, please rate it by using the vote buttons.

"john.fuller@gatech.edu" wrote:

> Inside an Access DB I am trying to open up and print a selected range
> from an Excel File (long reason on why I ahve to do it this way, but
> there is no work around). I wrote the following code in Excel (and it
> works great there). I then transferred it into access, added teh
> excelapp object lines, and basically put excelapp in front of every
> command line. This, however, did not work. The 2 places I find it
> crashing now is trying to set the active printer and set the wkbk. It
> will open the file, but won't set the wkbk variable. Also, right now
> the set printer code runs (due to the on error resume next), but it
> doesn't change the printer. I tried giving set
> excelapp.application.activeprinter a good string (as opposed to
> looping through all the possible Ne##), and it still won't set it.
> Any help is appreciated.
>
> Sub Print_TOC_Chart()
> Dim WkBk As Workbook, WkSht As Worksheet, ExcelApp As Object
>
> Set ExcelApp = CreateObject("Excel.Application")
> ExcelApp.Visible = True
>
> Call Set_Printer(ExcelApp, Me.ComboBox_prtSelect.Value)
>
> Set WkBk = ExcelApp.Workbooks.Open("\\hn-s-fileserv1\sharedmfg\mfg
> \CFM\Reports\TOC_Reports\Workcenter TOC Reports.xls", 0)
>
> Set WkSht = ExcelApp.WkBk.Worksheets("Workcenter Charts")
>
> With ExcelApp.WkSht.PageSetup
> .PrintArea = "B112:AW122"
> .Zoom = False
> .FitToPagesTall = 1
> .FitToPagesWide = 1
> .Orientation = xlLandscape
> End With
>
> ExcelApp.curWkSht.PrintOut Copies:=1
>
> ExcelApp.WkBk.Close False
> End Sub
>
> Sub Set_Printer(ExcelApp As Object, PrinterName As String)
> 'On Error Resume Next
>
> For i = 0 To 99
> If i < 10 Then
> ExcelApp.Application.ActivePrinter = PrinterName & " on
> Ne0" & i & ":"
> If ExcelApp.Err.Number = 0 Then Exit Sub
> ExcelApp.Err.Clear
> Else
> ExcelApp.Application.ActivePrinter = PrinterName & " on
> Ne" & i & ":"
> If ExcelApp.Err.Number = 0 Then Exit Sub
> ExcelApp.Err.Clear
> End If
> Next i
> End Sub
>

== 2 of 2 ==
Date: Thurs, Feb 28 2008 1:21 pm
From: Daniel Pineault

It's actually an easy fix, see below.

Function Print_TOC_Chart()
Dim xlApp As Object
Dim xlWrkBk As Object
Dim xlWrkSht As Object

'xlApp.Visible = True 'make excel visible to the user
Set xlWrkBk = xlApp.Workbooks.Open("\\hn-s-fileserv1\sharedmfg\mfg" & _
"'\CFM\Reports\TOC_Reports\Workcenter TOC Reports.xls")

Set xlWrkSht = xlApp.Worksheets("Workcenter Charts")

With xlWrkSht.PageSetup
.PrintArea = "B2:D4"
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
.Orientation = xlLandscape
End With

xlWrkSht.PrintOut Copies:=1, ActivePrinter:=Me.ComboBox_prtSelect.Value

xlWrkBk.Close False
xlApp.Close

Set xlWrkSht = Nothing
Set xlWrkBk = Nothing
Set xlApp = Nothing

End Function

you can simply add the ActivePrinter parameter to the PrintOut line such as:
xlWrkSht.PrintOut Copies:=1, ActivePrinter:=Me.ComboBox_prtSelect.Value

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com
If this post was helpful, please rate it by using the vote buttons.

"john.fuller@gatech.edu" wrote:

==============================================================================
TOPIC: Formatting Text in VBA
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/b917bbf2b4e6e9e3?hl=en
==============================================================================

== 1 of 3 ==
Date: Thurs, Feb 28 2008 1:04 pm
From: Sash

I need to reformat a field via VBA. But it's an odd situation.

I have a field where I store code and it may be

E800.0
E801
E8002

ONLY in the cases where it is E8002, I need to reformat it to be E800.2

In the same field I need to strip off leading zeros. Could I do something
like:

If field like 0* then
mid([field],2,10)
End if

The reason for using mid is that the length of the field varies, but is
never more than 10.

== 2 of 3 ==
Date: Thurs, Feb 28 2008 1:37 pm
From: Sash

Okay. I figured out the E problem and stripping off the leading zero, but
how would I strip of a trailing 0 if each field is different lengths? Can
someone help???

"Sash" wrote:

> I need to reformat a field via VBA. But it's an odd situation.
>
> I have a field where I store code and it may be
>
> E800.0
> E801
> E8002
>
> ONLY in the cases where it is E8002, I need to reformat it to be E800.2
>
> In the same field I need to strip off leading zeros. Could I do something
> like:
>
> If field like 0* then
> mid([field],2,10)
> End if
>
> The reason for using mid is that the length of the field varies, but is
> never more than 10.

== 3 of 3 ==
Date: Thurs, Feb 28 2008 3:04 pm
From: Klatuu

This will change E8002 to E800.2:
replace([field],"E8002","E800.2")

As to removing a trailing zero:

If Right([field],1) = "0" Then
[field] = Left([field], Len([field]) -1)
End If

--
Dave Hargis, Microsoft Access MVP

"Sash" wrote:

> Okay. I figured out the E problem and stripping off the leading zero, but
> how would I strip of a trailing 0 if each field is different lengths? Can
> someone help???
>
> "Sash" wrote:
>
> > I need to reformat a field via VBA. But it's an odd situation.
> >
> > I have a field where I store code and it may be
> >
> > E800.0
> > E801
> > E8002
> >
> > ONLY in the cases where it is E8002, I need to reformat it to be E800.2
> >
> > In the same field I need to strip off leading zeros. Could I do something
> > like:
> >
> > If field like 0* then
> > mid([field],2,10)
> > End if
> >
> > The reason for using mid is that the length of the field varies, but is
> > never more than 10.

==============================================================================
TOPIC: Linked Excel File
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/2fa5574cee47ac93?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Feb 28 2008 2:49 pm
From: Klatuu

I believe it is a timing issue.
It can take a little time for the copy from the pc to the network to take
place. I suspect the when access tries to open the table, the copy process
has not yet completed.
--
Dave Hargis, Microsoft Access MVP

"Weste" wrote:

> I have an MS Access 2003 app that has a linked Excel file. There is a
> process that copies the Excel file from the user's pc to the network, then
> refreshes the link. If this process is run, then a user makes a change in
> the file and runs the copy/refresh process again, the updated file doesn't
> get copied to the network if this is done within about a minute of the first
> copy/refresh. It seems like the app is holding a copy of the old Excel file
> temporarily in memory. I have tried deleting/creating a new link vs. refresh
> and this doesn't fix the problem. I have also killed the file on the network
> before copying the updated file, and the old file still gets copied. If I
> wait approx 1 minute, the updated Excel file gets copied. Is there a way to
> flush the old copy out of memory? Thanks.

==============================================================================
TOPIC: queries
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/31e56ff86c16e9b5?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Feb 28 2008 2:53 pm
From: Klatuu

You are suffering the first symptom of inaccurate relational database design.
The correct answer is combine both tables and add a field to identify new
and used. Then you wont have this problem.

You could solve the immediate problem with a Union query, but Union queries
are not updatable, so you can't use it for a form record source unless you
want it to be read only.

But, as I said before, you need to redesign your database. This is only the
first of many problems you will have using two tables than have the same data.
--
Dave Hargis, Microsoft Access MVP

"hogan" wrote:

> Let me add a few things. The tables have the same field names other than the
> primary key. I have two seperate tables because my primary keys are
> autonumbers with the format of "DFN"000 for new parts and "DFU"000 for used
> parts.
>
> "hogan" wrote:
>
> > I have two tables in a parts database called "new parts" & "used parts". The
> > primary keys are "used part number" & "new part number". How do I create a
> > query so I can have both used and new parts listed together. If you need
> > additional info please let me know.

==============================================================================
TOPIC: Email attachments
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/31fe0214c298d430?hl=en
==============================================================================

== 1 of 2 ==
Date: Thurs, Feb 28 2008 3:00 pm
From: Daniel Pineault

Sadly, the SendObject method only supports a single filename being passed as
an argument. So you cannot send both reports in 1 email. that said you have
two choices.

1. perform to send objects, 1 per report
DoCmd.SendObject acReport, "Record of Complaint",
"SnapshotFormat(*.snp)", "", "", "", "Consumer Complaint", "", True
DoCmd.SendObject acReport, "AckLetter",
"SnapshotFormat(*.snp)", "", "", "", "Letter", "", True

Also, you do not need to open the report to send the e-mail.

2. automate Outlook to create a mail, with both attachement and fire it off.

You can find an example (need to be modified slightly for your needs) at

http://msdn2.microsoft.com/en-us/library/aa159619(office.11).aspx#odc_ac_olauto_sendanoutlookmessageusingaccess

If you need more help, post back indicating which method you've chosen to
adopt and how I can further assist you.
--
Hope this helps,

Daniel Pineault
If this post was helpful, please rate it by using the vote buttons.

"DeanT" wrote:

> I want to send email with 2 report attachments. My coding is below. The
> problem I have is this coding only attaches the first report. How do I add in
> the 2nd report into the coding. I only want one email sent with both reports
> attached.
>
>
>
> Function Send_RC()
>
> On Error GoTo Send_RC_Err
>
>
>
> DoCmd.OpenReport "Record of Complaint", acViewPreview, "",
> "[Complaints]![ComplaintNumber]=[Forms]![ComplaintForm]![ComplaintNumber]",
> acNormal
>
> DoCmd.OpenReport "AckLetter", acViewPreview, "",
> "[Complaints]![ComplaintNumber]=[Forms]![ComplaintForm]![ComplaintNumber]",
> acNormal
>
> DoCmd.SendObject acReport, "Record of Complaint",
> "SnapshotFormat(*.snp)", "", "", "", "Consumer Complaint", "", True, ""
>
>
>
> Send_RC_Exit:
>
> Exit Function
>
> Send_RC_Err:
>
> MsgBox Error$
>
> Resume Send_RC_Exit
>
> End Function
>
>
>
> Thanks for any help.
>
>

== 2 of 2 ==
Date: Thurs, Feb 28 2008 3:01 pm
From: Daniel Pineault

Just came across a really good post on the subject with another solution.
Check out

http://groups.google.ca/group/microsoft.public.access.macros/browse_thread/thread/ac7e96df8b3a280e/c60eaa86563f3e19?hl=en&lnk=st&q=vba+sendobject+multiple+attachments#c60eaa86563f3e19
--
Hope this helps,

Daniel Pineault
If this post was helpful, please rate it by using the vote buttons.

"DeanT" wrote:

==============================================================================
TOPIC: Data import
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/57e28cad12b11e59?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Feb 28 2008 3:05 pm
From: Daniel Pineault

Why not create a function in Access that would, 1 by 1, read the file and add
the info to the appropriate table fields?

You're going to need to explain your table structure vs. text file content
for us to be able to help design the function.

Might I also ask, why you are proposing going through a txt orExcel first?
The cse file can be open with a standard text editor? if so, then my original
solution stands.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com
If this post was helpful, please rate it by using the vote buttons.

"Irina" wrote:

> I have a question:
> I have a folder with 3,700 .cse files, and i need to dump those somehow into
> either a .txt file or excel to import those into Access db later.
> The text of the files looks like this:
> {A41F6C3E-2643-11D2-A02E-00A0FFFF9114}]
> {1}
> 50=Philistin BYSSAINTHE^Arial@9@0@
> 120=500 Fernwood Avenue^Arial@9@0@
> 130=Rochester^Arial@9@0@
> 150=14609^Arial@9@0@
> 340=Philistin BYSSAINTHE^Arial@9@0@
> 180=Y,,
> 190=New York Court of Appeals^Arial@9@1@
> 330=All immigration matters regarding Philistin BYSSAINTHE.^@
> 30=^Arial@9@0@
> 40=^Arial@9@0@
> 80=N,
> 60=N,
> 90=N,
> 70=Y,
> 110=^Arial@9@0@
> 140=^Arial@9@0@
> 210=^Arial@9@0@
> 220=N,,
> 260=Harter Secrest & Emery LLP^Arial@9@1@
> 270=1600 Bausch & Lomb Place^Arial@9@1@
> 280=Rochester, New York 14604^Arial@9@1@
> 290=Margaret A. Catillaz, Esq.^Arial@9@1@
> 320=Margaret A. Catillaz, Esq.^Arial@9@0@
> 300=(716)231-1122^Arial@9@1@
> 310=(716)232-2152^Arial@8@1@
> 200=,,
> 240=,,
> 10=Form I-765 and Related Documents^Arial@9@1@<OVERRIDE>@
> 20=^Arial@9@1@
> {2}
> [{A41F6C41-2643-11D2-A02E-00A0FFFF9114}]
> {1}
> 220=Y,,
> 240=,,
> 260=N,,
> 280=,,
> 360=Harter, Secrest & Emery LLP^Arial@9@1@
> 330=Margaret A. Catillaz, Esq.^Arial@9@1@<OVERRIDE>@
> 370=^@
> 340=^Arial@9@1@
> {2}
>
> So my question is, how do I dump those files into a readable format
> for access automatically - if I do this manually, it would take me months....
> is there some utility that would do such a thing?
> please anyone advise
>
> appreciate your expertise.
>
>

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

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

25 new messages in 11 topics - digest

2007-11-21T11:08:00.000-08:00

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: get response from command button
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/88196da848e7eafa?hl=en
==============================================================================

== 1 of 2 ==
Date: Wed, Nov 21 2007 12:50 am
From: Bruce

Hi - I am trying to run a report with the function below. I wish to open the
form 'frm_Trade_Param' to select some query filters and then run the report.

Basically I wish to get a response from this form and continue my code as
follows;

cb_A is a command button. If this is pressed the report is run.
cb_B is also a command button. If this is pressed then the macro exits and
the form is closed.

I can do this from the form directly but I don't want to hard code this into
the form because I wish to reuse for other reports. Basically this is similar
to a vb yes/no response. How can I adjust my code to do this?

Bruce

Function rpt_Trades_Complete()
DoCmd.OpenForm "frm_Trade_Param", acNormal, "", "", , acNormal

If cb_A is pressed Then
DoCmd.OpenReport "rpt_Trades_Complete", acViewNormal, "", "", acNormal
ElseIf cb_B is pressed Then
DoCmd.Close acForm, "frm_Trade_Param"
End If
End Function

== 2 of 2 ==
Date: Wed, Nov 21 2007 2:05 am
From: "Albert D. Kallal"

I explain in detail how to do this here:

http://www.members.shaw.ca/AlbertKallal/Dialog/Index.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

==============================================================================
TOPIC: MS Access 2002 problem with subform
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/04d9b0be493dbc86?hl=en
==============================================================================

== 1 of 3 ==
Date: Wed, Nov 21 2007 2:52 am
From: mirceaar

I have an Access 2002 database.
On a form there is a subform containing a query that selects data from one
single table according to a few criterias on the parent form.
I have started this database a while ago and at that time I could select
and/or edit values in that subform that were reflected in the source table,
with no problem whatsoever.
Now, with no changes to this database - from my knolwledge at least - I
cannot edit any record from the subform. I can delete them.
If I go into RecordSource property of the subform I can edit the query, I
can also run it and edit the values within it, and the values change the
source table.
If I try to do this from the (sub)form, no change.
Any ideas...?

== 2 of 3 ==
Date: Wed, Nov 21 2007 4:21 am
From: "Allen Browne"

Check the properties of the form (i.e. the form that is the subform, looking
at the properties of the Form, not of some control.)

Particularly:
Allow Edits Yes
Recordset Type Dynaset

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

"mirceaar" <mirceaar@discussions.microsoft.com> wrote in message
news:C89EF827-6E59-4DA4-8026-B089DE341102@microsoft.com...
>I have an Access 2002 database.
> On a form there is a subform containing a query that selects data from one
> single table according to a few criterias on the parent form.
> I have started this database a while ago and at that time I could select
> and/or edit values in that subform that were reflected in the source
> table,
> with no problem whatsoever.
> Now, with no changes to this database - from my knolwledge at least - I
> cannot edit any record from the subform. I can delete them.
> If I go into RecordSource property of the subform I can edit the query, I
> can also run it and edit the values within it, and the values change the
> source table.
> If I try to do this from the (sub)form, no change.
> Any ideas...?

== 3 of 3 ==
Date: Wed, Nov 21 2007 6:02 am
From: mirceaar

It seems the subform detail was locked.
I discovered this just before I read your post.
Strangely, I don't remember locking it, nor have I had any reason to do such
a dumb thing, either by hand or through code...
Thanks anyway

"Allen Browne" wrote:

> Check the properties of the form (i.e. the form that is the subform, looking
> at the properties of the Form, not of some control.)
>
> Particularly:
> Allow Edits Yes
> Recordset Type Dynaset
>
> --
> 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.
>
> "mirceaar" <mirceaar@discussions.microsoft.com> wrote in message
> news:C89EF827-6E59-4DA4-8026-B089DE341102@microsoft.com...
> >I have an Access 2002 database.
> > On a form there is a subform containing a query that selects data from one
> > single table according to a few criterias on the parent form.
> > I have started this database a while ago and at that time I could select
> > and/or edit values in that subform that were reflected in the source
> > table,
> > with no problem whatsoever.
> > Now, with no changes to this database - from my knolwledge at least - I
> > cannot edit any record from the subform. I can delete them.
> > If I go into RecordSource property of the subform I can edit the query, I
> > can also run it and edit the values within it, and the values change the
> > source table.
> > If I try to do this from the (sub)form, no change.
> > Any ideas...?
>
>

==============================================================================
TOPIC: Database opens slowly.
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/b13aaafc45780317?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 21 2007 2:59 am
From: RobGMiller

Hi Tony,

Yes, I did that as well. I just did it again to be sure because there is the
issue of the "Visual Basic for Applications project in the database is
corrupt" that keeps comming up. After reading about it, I gather that the
message is eroneous and probably has something to do with the fact that this
is a 2003.

In any case, I used a blank database and decompiled first. I got the same
error message. That proves, I suppose, that the message is eroneous. Then I
Imported all the objects, they I decompiled it, then I compacted it. They I
linked all the tables.

Now its a bit faster to open but still takes about 15 seconds before the
autoexec runs or a display form shows but there are all kinds of the types of
errors that are not obvious and difficult to debug.

So I go back to the original and wonder if I can make that splash screen
come up any sooner somehow.

I gather that there is nothing I can do other than try to figure out what
objects break when the above technique is used to speed it up and why it
really doesn't speed it up enough.

If the application compiles without error and does work well then what kinds
of things can keep it from loading fast and what kinds of things can cause a
decompile to break the objects to the point where they wont work any longer.
--
RobGMiller

"Tony Toews [MVP]" wrote:

> RobGMiller <RobGMiller@discussions.microsoft.com> wrote:
>
> >Yes, it did speed it up a bit but it still takes about 15 seconds before
> >either the autoexec runs or the assigned display form comes up.
>
> Ok, now try the decompile on the new MDB. After making a copy of it.
>
> Tony
> --
> Tony Toews, Microsoft Access MVP
> Please respond only in the newsgroups so that others can
> read the entire thread of messages.
> Microsoft Access Links, Hints, Tips & Accounting Systems at
> http://www.granite.ab.ca/accsmstr.htm
> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
>

==============================================================================
TOPIC: Creating a query with Multi select list boxes
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/62d81110c3598798?hl=en
==============================================================================

== 1 of 4 ==
Date: Wed, Nov 21 2007 4:05 am
From: BWD

Thanks for your reponse. I made the changes to the text field value
and then inserted the "IN" statement to the query's SQL. This did not
produce results.

I then tried to manually enter the string values:
"Value1","Value2","Value3" etc. into the paramter field in the QBD
grid. The query did not like this format it would only produce
results when I specifically entered values in the fomat of; "Value1"
OR "Value2" OR "Value3" etc.

Perhaps it is the way I am writing the "IN" part of the SQL
statement?

Again, any help would be appreciated.

== 2 of 4 ==
Date: Wed, Nov 21 2007 5:10 am
From: BWD

On Nov 21, 7:05 am, BWD <sa...@businesswebdesigns.net> wrote:
> Thanks for your reponse. I made the changes to the text field value
> and then inserted the "IN" statement to the query's SQL. This did not
> produce results.
>
> I then tried to manually enter the string values:
> "Value1","Value2","Value3" etc. into the paramter field in the QBD
> grid. The query did not like this format it would only produce
> results when I specifically entered values in the fomat of; "Value1"
> OR "Value2" OR "Value3" etc.
>
> Perhaps it is the way I am writing the "IN" part of the SQL
> statement?
>
> Again, any help would be appreciated.

ADDITION TO LAST MESSAGE:

Perhaps it will be helpful to add the SQL that I generated for this:

SELECT *
FROM [Table Name]
WHERE (((Table].FieldName)="In (" & FormName.FieldName & ")"));

This SQL is still not working for me.
Thanks

== 3 of 4 ==
Date: Wed, Nov 21 2007 6:53 am
From: LarryP

Let me get my head around this. You have four list boxes, each of which
generates a selected value. Are all those values to be applied as criteria
against ONE field in your query, or against DIFFERENT fields? In my
experience the latter seems more likely. Please clarify.

"BWD" wrote:

> Hi Group,
>
> In a bind here; I have a form with 4 multi select list boxes. What I
> would like to do is create a query that utilizes the values selected
> in the 4 list boxes. I have gotten as far as creating a text box on
> my form that records the values selected in the multi list. This text
> box value is in the format of "Value1" or "Value2" or "Value3" etc.
>
> However, setting the query parameter to my text box value does not
> yield the results I am looking for. Can anyone provide some
> guidance??
>
> Thanks much!
>

== 4 of 4 ==
Date: Wed, Nov 21 2007 10:33 am
From: "Stuart McCall"

"BWD" <sales@businesswebdesigns.net> wrote in message
news:48544e24-bf11-4960-be53-9e2b90b658c0@c29g2000hsa.googlegroups.com...
> On Nov 21, 7:05 am, BWD <sa...@businesswebdesigns.net> wrote:
>> Thanks for your reponse. I made the changes to the text field value
>> and then inserted the "IN" statement to the query's SQL. This did not
>> produce results.
>>
>> I then tried to manually enter the string values:
>> "Value1","Value2","Value3" etc. into the paramter field in the QBD
>> grid. The query did not like this format it would only produce
>> results when I specifically entered values in the fomat of; "Value1"
>> OR "Value2" OR "Value3" etc.
>>
>> Perhaps it is the way I am writing the "IN" part of the SQL
>> statement?
>>
>> Again, any help would be appreciated.
>
> ADDITION TO LAST MESSAGE:
>
> Perhaps it will be helpful to add the SQL that I generated for this:
>
> SELECT *
> FROM [Table Name]
> WHERE (((Table].FieldName)="In (" & FormName.FieldName & ")"));
>
> This SQL is still not working for me.
> Thanks

The problem here is the equals sign. Get rid of it. "In", used in this
context, is an operator, as is the equals sign.

SELECT *
FROM [Table Name]
WHERE Table].FieldName In (" & FormName.FieldName & ")";

For clarity, I've removed the parantheses that the query designer adds. It's
ok to remove them because the designer only uses them internally, to make
its parsing more precise.

Hope that cracks it.

==============================================================================
TOPIC: determining if this is the first new record in a subform
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/32f8708d3a999813?hl=en
==============================================================================

== 1 of 2 ==
Date: Wed, Nov 21 2007 4:18 am
From: "Allen Browne"

Tables look right. I would probably use AutoNumber for the ID fields rather
than text, but it would work as you have it.

There is no direct relationships between:
tblProjectTypeStep.StepNum : tblProjectStep.StepNum

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

"ArielZusya" <ArielZusya@discussions.microsoft.com> wrote in message
news:A809FE4E-5BC1-44F5-90B3-FAAE090CA71A@microsoft.com...
> Hmm... well... before I get too deep in here, I want to make sure I
> follow.
> Also, so we're speaking the same language, instead of having you guess at
> the
> structure of my tables I'll just use your example tables to learn how to
> do
> this and then I'll adapt it later to fit my db (which may be the wrong
> structure and needs revamping anyhow *GRIN*). So... I think I followed
> most
> of the table structure you wrote about but I want to be sure... so...
> here's
> what I understood your post to need:
>
> Tables:
>
> tblProject.ProjectID (Primary Key - Text - holds the description of the
> project)
> tblProject.ProjectType (Text - project name from tblProjectType)
> tblProject.ClientID (Text - holds the name of the client)
> tblProject.DueDate (Date/Time - holds the date the project is due)
>
> tblProjectStep.ProjectID (Primary Key - Text - description from
> tblProject)
> tblProjectStep.StepNum (Primary Key - Text - StepNum from tblProjectStep)
> tblProjectStep.ToolID (Primary Key - Text - ToolID from tblTool)
> tblProjectStep.StepDate (Date/Time - holds the date that the step
> happened)
>
> tblProjectType.ProjectTypeID (Primary Key - Text - ex: "chair")
>
> tblProjectTypeStep.ProjectTypeID (Primary Key - Text)
> tblProjectTypeStep.StepNum (Primary Key - Text)
> tblProjectTypeStep.ToolID (Text - ToolID from tblTool)
>
> tblTool.ToolID (Primary Key - Text - holds the name of the tool)
>
> Relationships:
>
> One : Many
>
> tblProjectType.ProjectTypeID : tblProjectTypeStep.ProjectTypeID
> tblTool.ToolID : tblProjectTypeStep.ToolID
> tblProjectTypeStep.StepNum : tblProjectStep.StepNum
> tblTool.ToolID : tblProjectStep.ToolID
> tblProject.ProjectID : tblProjectStep.ProjectID
>
>
> Is that right so far? I didn't want to start teaching myself what the
> code
> means before getting the tables right. Incidentally, this is also my
> first
> experience with using two fields as primary keys (I feel like such a
> n00b).
> Anyhow... let me know. Thanks for your help (and patience *GRIN*)!
>
> --Ariel
>
> "Allen Browne" wrote:
>
>> The answer to your question will depend on how you have set your tables
>> up.
>>
>> It sounds like you need to teach Access the steps required to complete a
>> project. You could define that with tables like this:
>>
>> Tool table (one record for each type of tool), with fields:
>> - ToolID (primary key)
>>
>> ProjectType (one record for each type of thing you build.) Fields:
>> - ProjectTypeID (e.g. chair)
>>
>> ProjectTypeStep table
>> - ProjectTypeID
>> - StepNum
>> - ToolID
>> (Primary key is ProjectTypeID + StepNum.)
>>
>> Now that the database knows the steps for each type of project, you can
>> create the tables for the actual projects you do, like this:
>>
>> Project table (one record for each project you take on):
>> - ProjectID
>> - ProjectType
>> - ClientID (who this project is for.)
>> - DueDate (when you're supposed to finish this.)
>>
>> ProjectStep table (one record for each step of a project):
>> - ProjectID Which project
>> - StepNum Which Step of the project.
>> - ToolID: Tool for this step
>> - StepDate: Date/Time (when you started this step.)
>>
>> I suspect you already have something like these 2 tables, with the form
>> and
>> subform. If so, you can use the BeforeInsert event procedure of the
>> subform
>> to look up the last step, figure out the next step and tool number, and
>> assign them to this new record.
>>
>> This is untested aircode only, and needs debugging, but should give you
>> the
>> idea:
>>
>> Private Sub Form_BeforeInsert(Cancel As Integer)
>> Dim rs As DAO.Recordset
>> Dim strWhere As String
>> Dim strSql As String
>> Dim varResult As Variant
>> Const strcStub = "SELECT StepNum, ToolID " & vbCrLf & _
>> "FROM ProjectTypeStep INNER JOIN Project " & _
>> "ON ProjectTypeStepID = Project.ProjectTypeID " & vbCrLf & _
>> "WHERE ("
>> Const strcTail = ") " & vbCrLf & "ORDER BY StepNum;"
>>
>> With Me.Parent
>> If IsNull(!ProjectID) Then
>> Cancel = True
>> MsgBox "Enter the project in the main form first."
>> Else
>> strWhere = "ProjectID = " & !ProjectID
>> varResult = DMax("StepNum", "ProjectStep", strWhere)
>> If Not IsNull(varResult) Then
>> strWhere = strWhere & ") AND (StepNum > " & varResult
>> End If
>> strSql = strcStub & strWhere & strcTail
>> 'Debug.Print strSql
>> Set rs = dbEngine(0)(0).OpenRecordset(strSql)
>> If Not rs.RecordCount = 0 Then
>> Me.StepNum = rs!StepNum
>> Me.ToolID = rs!ToolID
>> End If
>> rs.Close
>> Set rs = Nothing
>> End If
>> End With
>> End Sub
>>
>> "ArielZusya" <ArielZusya@discussions.microsoft.com> wrote in message
>> news:0DBEF3D6-4F75-4FA0-8107-AFDBCDA505EB@microsoft.com...
>> > I've got a form that uses subforms to display/enter data associated
>> > with a
>> > parent record. The "parent" is each day on a project and the "child"
>> > is
>> > each
>> > tool used for the project on that day. So... if project number 37,
>> > building
>> > a chair, requires green hammer and a blue saw on 11/27/07 the form
>> > would
>> > tell
>> > me that "chair" with ID 37 is associated with green hammer (tool1) and
>> > blue
>> > saw (tool2). And then if project number 37, building a char, requires
>> > a
>> > yellow level (tool3) and a purple tape measure (tool4) on 11/28/07,
>> > there
>> > would be a new record for 11/28 with that information in the submenu. I
>> > want
>> > to write code that will determine if this is the first new child record
>> > associated with the parent project or if not what the last child record
>> > entered associated with this project is and then populate the child's
>> > tool#
>> > with the next sequential tool# but I'm not sure where to even begin (I
>> > probably don't even have the lingo right). In other words, if on
>> > 11/29/07
>> > I
>> > go to enter the tools needed that day for the chair project (#37) I'd
>> > like
>> > it
>> > to know that the next sequential tool # is tool5 even though the
>> > current
>> > record is only linked to the 11/28 record by that 37. This sounds so
>> > much
>> > more complicated than it actually is... hopefully someone will be able
>> > to
>> > understand what I'm trying to do. Any suggestions? Thanks!

== 2 of 2 ==
Date: Wed, Nov 21 2007 8:05 am
From: ArielZusya

Hi Allen,

Thanks for all your help. Maybe it's the recent drop in temp or the
upcoming trip to tryptophan land, but I'm feeling lost and confused. So...
first... I tried changing the ID fields to autonumber and it gave me all
sorts of errors in the subform... so I switched back to text. Second, I got
rid of the relationship you suggested should not have been there and added
one more relationship that was missing from my original list:

One : Many
tblProjectType.ProjectTypeID : tblProject.ProjectType

Third, to avoid confusion I made the following changes:

tblProjectStep.StepNum is now tblProjectStep.ProjectStepNum
tblProjectTypeStep.StepNum is now tblProjectTypeStep.TypeStepNum

Then, I changed the code accordingly:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rs As DAO.Recordset
Dim strWhere As String
Dim strSql As String
Dim varResult As Variant
Const strcStub = "SELECT ProjectStepNum, ToolID " & vbCrLf & _
"FROM ProjectTypeStep INNER JOIN Project " & _
"ON ProjectTypeStepID = Project.ProjectTypeID " & vbCrLf & "WHERE ("
Const strcTail = ") " & vbCrLf & "ORDER BY ProjectStepNum;"
With Me.Parent
If IsNull(!ProjectID) Then
Cancel = True
MsgBox "Enter the project in the main form first."
Else
strWhere = "ProjectID = " & !ProjectID
varResult = DMax("ProjectStepNum", "tblProjectStep", strWhere)
If Not IsNull(varResult) Then
strWhere = strWhere & ") AND (ProjectStepNum > " & varResult
End If
strSql = strcStub & strWhere & strcTail
'Debug.Print strSql
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
If Not rs.RecordCount = 0 Then
Me.ProjectStepNum = rs!ProjectStepNum
Me.ToolID = rs!ToolID
End If
rs.Close
Set rs = Nothing
End If
End With
End Sub

In terms of forms I just used the form wizard to create a justified form
based on tblProject for the main form (frmProject). I then used the wizard
to create a justified form based on tblProjectStep for the subform
(frmProjectStep). I then set the sub as a continuous form and added your
code to the "BeforeInsert" function. Finally, I added the subform to the main
form. When I open the main form and start typing something into the subform,
your code catches the "enter something in the main form first" errors. The
trouble is, when I have something in the main form and then start entering
something in the subform it gets caught at:

Set rs = DBEngine(0)(0).OpenRecordset(strSql)

with an error of:

Run-time error '3296':

Join expression not supported.

What am I doing wrong? do I have all of this setup properly now? Thanks
again for all your help!

Ariel

"Allen Browne" wrote:

> Tables look right. I would probably use AutoNumber for the ID fields rather
> than text, but it would work as you have it.
>
> There is no direct relationships between:
> tblProjectTypeStep.StepNum : tblProjectStep.StepNum
>
> --
> 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.
>
> "ArielZusya" <ArielZusya@discussions.microsoft.com> wrote in message
> news:A809FE4E-5BC1-44F5-90B3-FAAE090CA71A@microsoft.com...
> > Hmm... well... before I get too deep in here, I want to make sure I
> > follow.
> > Also, so we're speaking the same language, instead of having you guess at
> > the
> > structure of my tables I'll just use your example tables to learn how to
> > do
> > this and then I'll adapt it later to fit my db (which may be the wrong
> > structure and needs revamping anyhow *GRIN*). So... I think I followed
> > most
> > of the table structure you wrote about but I want to be sure... so...
> > here's
> > what I understood your post to need:
> >
> > Tables:
> >
> > tblProject.ProjectID (Primary Key - Text - holds the description of the
> > project)
> > tblProject.ProjectType (Text - project name from tblProjectType)
> > tblProject.ClientID (Text - holds the name of the client)
> > tblProject.DueDate (Date/Time - holds the date the project is due)
> >
> > tblProjectStep.ProjectID (Primary Key - Text - description from
> > tblProject)
> > tblProjectStep.StepNum (Primary Key - Text - StepNum from tblProjectStep)
> > tblProjectStep.ToolID (Primary Key - Text - ToolID from tblTool)
> > tblProjectStep.StepDate (Date/Time - holds the date that the step
> > happened)
> >
> > tblProjectType.ProjectTypeID (Primary Key - Text - ex: "chair")
> >
> > tblProjectTypeStep.ProjectTypeID (Primary Key - Text)
> > tblProjectTypeStep.StepNum (Primary Key - Text)
> > tblProjectTypeStep.ToolID (Text - ToolID from tblTool)
> >
> > tblTool.ToolID (Primary Key - Text - holds the name of the tool)
> >
> > Relationships:
> >
> > One : Many
> >
> > tblProjectType.ProjectTypeID : tblProjectTypeStep.ProjectTypeID
> > tblTool.ToolID : tblProjectTypeStep.ToolID
> > tblProjectTypeStep.StepNum : tblProjectStep.StepNum
> > tblTool.ToolID : tblProjectStep.ToolID
> > tblProject.ProjectID : tblProjectStep.ProjectID
> >
> >
> > Is that right so far? I didn't want to start teaching myself what the
> > code
> > means before getting the tables right. Incidentally, this is also my
> > first
> > experience with using two fields as primary keys (I feel like such a
> > n00b).
> > Anyhow... let me know. Thanks for your help (and patience *GRIN*)!
> >
> > --Ariel
> >
> > "Allen Browne" wrote:
> >
> >> The answer to your question will depend on how you have set your tables
> >> up.
> >>
> >> It sounds like you need to teach Access the steps required to complete a
> >> project. You could define that with tables like this:
> >>
> >> Tool table (one record for each type of tool), with fields:
> >> - ToolID (primary key)
> >>
> >> ProjectType (one record for each type of thing you build.) Fields:
> >> - ProjectTypeID (e.g. chair)
> >>
> >> ProjectTypeStep table
> >> - ProjectTypeID
> >> - StepNum
> >> - ToolID
> >> (Primary key is ProjectTypeID + StepNum.)
> >>
> >> Now that the database knows the steps for each type of project, you can
> >> create the tables for the actual projects you do, like this:
> >>
> >> Project table (one record for each project you take on):
> >> - ProjectID
> >> - ProjectType
> >> - ClientID (who this project is for.)
> >> - DueDate (when you're supposed to finish this.)
> >>
> >> ProjectStep table (one record for each step of a project):
> >> - ProjectID Which project
> >> - StepNum Which Step of the project.
> >> - ToolID: Tool for this step
> >> - StepDate: Date/Time (when you started this step.)
> >>
> >> I suspect you already have something like these 2 tables, with the form
> >> and
> >> subform. If so, you can use the BeforeInsert event procedure of the
> >> subform
> >> to look up the last step, figure out the next step and tool number, and
> >> assign them to this new record.
> >>
> >> This is untested aircode only, and needs debugging, but should give you
> >> the
> >> idea:
> >>
> >> Private Sub Form_BeforeInsert(Cancel As Integer)
> >> Dim rs As DAO.Recordset
> >> Dim strWhere As String
> >> Dim strSql As String
> >> Dim varResult As Variant
> >> Const strcStub = "SELECT StepNum, ToolID " & vbCrLf & _
> >> "FROM ProjectTypeStep INNER JOIN Project " & _
> >> "ON ProjectTypeStepID = Project.ProjectTypeID " & vbCrLf & _
> >> "WHERE ("
> >> Const strcTail = ") " & vbCrLf & "ORDER BY StepNum;"
> >>
> >> With Me.Parent
> >> If IsNull(!ProjectID) Then
> >> Cancel = True
> >> MsgBox "Enter the project in the main form first."
> >> Else
> >> strWhere = "ProjectID = " & !ProjectID
> >> varResult = DMax("StepNum", "ProjectStep", strWhere)
> >> If Not IsNull(varResult) Then
> >> strWhere = strWhere & ") AND (StepNum > " & varResult
> >> End If
> >> strSql = strcStub & strWhere & strcTail
> >> 'Debug.Print strSql
> >> Set rs = dbEngine(0)(0).OpenRecordset(strSql)
> >> If Not rs.RecordCount = 0 Then
> >> Me.StepNum = rs!StepNum
> >> Me.ToolID = rs!ToolID
> >> End If
> >> rs.Close
> >> Set rs = Nothing
> >> End If
> >> End With
> >> End Sub
> >>
> >> "ArielZusya" <ArielZusya@discussions.microsoft.com> wrote in message
> >> news:0DBEF3D6-4F75-4FA0-8107-AFDBCDA505EB@microsoft.com...
> >> > I've got a form that uses subforms to display/enter data associated
> >> > with a
> >> > parent record. The "parent" is each day on a project and the "child"
> >> > is
> >> > each
> >> > tool used for the project on that day. So... if project number 37,
> >> > building
> >> > a chair, requires green hammer and a blue saw on 11/27/07 the form
> >> > would
> >> > tell
> >> > me that "chair" with ID 37 is associated with green hammer (tool1) and
> >> > blue
> >> > saw (tool2). And then if project number 37, building a char, requires
> >> > a
> >> > yellow level (tool3) and a purple tape measure (tool4) on 11/28/07,
> >> > there
> >> > would be a new record for 11/28 with that information in the submenu. I
> >> > want
> >> > to write code that will determine if this is the first new child record
> >> > associated with the parent project or if not what the last child record
> >> > entered associated with this project is and then populate the child's
> >> > tool#
> >> > with the next sequential tool# but I'm not sure where to even begin (I
> >> > probably don't even have the lingo right). In other words, if on
> >> > 11/29/07
> >> > I
> >> > go to enter the tools needed that day for the chair project (#37) I'd
> >> > like
> >> > it
> >> > to know that the next sequential tool # is tool5 even though the
> >> > current
> >> > record is only linked to the 11/28 record by that 37. This sounds so
> >> > much
> >> > more complicated than it actually is... hopefully someone will be able
> >> > to
> >> > understand what I'm trying to do. Any suggestions? Thanks!
>
>

==============================================================================
TOPIC: Search Form
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e59eb5434290efac?hl=en
==============================================================================

== 1 of 2 ==
Date: Wed, Nov 21 2007 4:26 am
From: "Allen Browne"

Presumably you have a one-to-many relationship between buyers and emails, so
your email table has a foreign key named BuyerNum.

If so, you need to DLookup() the BuyerNum for the particular email. You can
then use this number in your string.

This kind of thing:

Dim strWhere As String
Dim varResult As Variant
strWhere = "EmailID = " & Me.[EmailID]
varResult = DLookup("BuyerNum", "EmailTable", strWhere)
If IsNull(varResult) then
MsgBox "Email ID not found"
Else
stLinkCriteria = "BuyerNum = " & varResult
'Now use the string as you expect to.
End If

If you need help with DLookup(), see:

http://allenbrowne.com/casu-07.html

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

"mattieflo" <mattieflo@discussions.microsoft.com> wrote in message
news:C37F8B79-F4D1-4658-AEC4-ACA0B90D1FD9@microsoft.com...
> Hello, I'm trying to write a search form that will search a main form for
> fields. The main form consists of several of several subforms. The
> following
> piece of code successfully searches for the Buyer Field on the main field.
>
> Private Sub cmdSearchBuyer_Click()
>
> Dim stbuyernumber As String
> Dim stDocName As String
> Dim stLinkCriteria As String
>
>
> stbuyernumber = InputBox("Enter Buyer Number", "Search Buyer
> Number")
> If stbuyernumber <> "" Then
> stDocName = "frmMain2"
> stLinkCriteria = "[tblBuyer].[BuyerNum] = " & stbuyernumber
> DoCmd.OpenForm stDocName, , , stLinkCriteria
> DoCmd.Close acForm, "frmSearch"
> End If
>
>
> A buyer can have many emails. I have an EMAIL subform that is linked to
> the
> BUYER mainform. I want to search for the primary key of the email subform
> (EmailID) so that it brings up the appropriate parent buyer number. Is
> there
> a way to adjust this line in the code so it does that?
>
> "stLinkCriteria = "[tblBuyer].[BuyerNum] = " & stbuyernumber"
>
> Thanks!
>
>
> End Sub

== 2 of 2 ==
Date: Wed, Nov 21 2007 8:25 am
From: mattieflo

Thanks for that Allen. It worked like a charm for this and will come in handy
for the future. Greatly appreciated!

"Allen Browne" wrote:

> Presumably you have a one-to-many relationship between buyers and emails, so
> your email table has a foreign key named BuyerNum.
>
> If so, you need to DLookup() the BuyerNum for the particular email. You can
> then use this number in your string.
>
> This kind of thing:
>
> Dim strWhere As String
> Dim varResult As Variant
> strWhere = "EmailID = " & Me.[EmailID]
> varResult = DLookup("BuyerNum", "EmailTable", strWhere)
> If IsNull(varResult) then
> MsgBox "Email ID not found"
> Else
> stLinkCriteria = "BuyerNum = " & varResult
> 'Now use the string as you expect to.
> End If
>
> If you need help with DLookup(), see:
>

http://allenbrowne.com/casu-07.html
>
> --
> 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.
>
> "mattieflo" <mattieflo@discussions.microsoft.com> wrote in message
> news:C37F8B79-F4D1-4658-AEC4-ACA0B90D1FD9@microsoft.com...
> > Hello, I'm trying to write a search form that will search a main form for
> > fields. The main form consists of several of several subforms. The
> > following
> > piece of code successfully searches for the Buyer Field on the main field.
> >
> > Private Sub cmdSearchBuyer_Click()
> >
> > Dim stbuyernumber As String
> > Dim stDocName As String
> > Dim stLinkCriteria As String
> >
> >
> > stbuyernumber = InputBox("Enter Buyer Number", "Search Buyer
> > Number")
> > If stbuyernumber <> "" Then
> > stDocName = "frmMain2"
> > stLinkCriteria = "[tblBuyer].[BuyerNum] = " & stbuyernumber
> > DoCmd.OpenForm stDocName, , , stLinkCriteria
> > DoCmd.Close acForm, "frmSearch"
> > End If
> >
> >
> > A buyer can have many emails. I have an EMAIL subform that is linked to
> > the
> > BUYER mainform. I want to search for the primary key of the email subform
> > (EmailID) so that it brings up the appropriate parent buyer number. Is
> > there
> > a way to adjust this line in the code so it does that?
> >
> > "stLinkCriteria = "[tblBuyer].[BuyerNum] = " & stbuyernumber"
> >
> > Thanks!
> >
> >
> > End Sub
>
>

==============================================================================
TOPIC: Commndbars collection does not contain menus?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/2c72c6e2e92ddb08?hl=en
==============================================================================

== 1 of 3 ==
Date: Wed, Nov 21 2007 4:28 am
From: redtux

running the folloing code (from helpfiles)

For Each cb In CommandBars("Menu bar")
If cb.name = "Forms" Then
' cb.Protection = msoBarNoChangeDock
' cb.Visible = True
foundflag = True
Debug.Print cb.name
End If
Next cb
If Not foundflag Then
MsgBox "The collection does not contain a Forms command bar."
End If

results in the popup box coming up

How do I look through available menus?

== 2 of 3 ==
Date: Wed, Nov 21 2007 6:58 am
From: "Albert D. Kallal"

Try:

Dim cb As CommandBarControl
Dim FOUNDFLAG As Boolean

For Each cb In CommandBars("Menu Bar").Controls
Debug.Print cb.Caption
Next

For a "contorl", you use the caption setting...there is no "name" setting..

above output is:

&File
&Edit
&View
&Relationships
&Insert
&Query
&Diagram
F&ormat
&Records
&PivotTable
&PivotChart
Filte&r
&Run
&Tools
&Window
&Help

The menu bar "menu bar" does not contain the "forms" menu.

you can all via

Dim cb As CommandBar <---- note
Dim FOUNDFLAG As Boolean

For Each cb In CommandBars
Debug.Print cb.Name
Next

We get:

Task Pane

Database
Menu Bar
Report Design
Form Design
Toolbox
Source Code Control
test1
Font/Fore Color
Fill/Back Color
Line/Border Style
Line/Border Width
Line/Border Color
Special Effect
Form View
etc. etc.

If you not seeing the additional data types you need to set a references (in
tools->references in code) to:

Microsoft Office 11.0 Object library (office 2003 = 11, office 2002 = 10
...etc).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

== 3 of 3 ==
Date: Wed, Nov 21 2007 8:56 am
From: redtux

"Albert D. Kallal" wrote:

> Try:
>
> Dim cb As CommandBarControl
> Dim FOUNDFLAG As Boolean
>
> For Each cb In CommandBars("Menu Bar").Controls
> Debug.Print cb.Caption
> Next
>
> For a "contorl", you use the caption setting...there is no "name" setting..
>
> above output is:
>
> &File
> &Edit
> &View
> &Relationships
> &Insert
> &Query
> &Diagram
> F&ormat
> &Records
> &PivotTable
> &PivotChart
> Filte&r
> &Run
> &Tools
> &Window
> &Help
>
> The menu bar "menu bar" does not contain the "forms" menu.
>
> you can all via
>
>
> Dim cb As CommandBar <---- note
> Dim FOUNDFLAG As Boolean
>
> For Each cb In CommandBars
> Debug.Print cb.Name
> Next
>
> We get:
>
> Task Pane
>
> Database
> Menu Bar
> Report Design
> Form Design
> Toolbox
> Source Code Control
> test1
> Font/Fore Color
> Fill/Back Color
> Line/Border Style
> Line/Border Width
> Line/Border Color
> Special Effect
> Form View
> etc. etc.
>
> If you not seeing the additional data types you need to set a references (in
> tools->references in code) to:
>
> Microsoft Office 11.0 Object library (office 2003 = 11, office 2002 = 10
> ....etc).
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com
>
>
>
>

thanks

==============================================================================
TOPIC: exif data from jpg's
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/4b577c7fec3e2b7e?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 21 2007 5:21 am
From: "Albert D. Kallal"

"CDev" <CDev@discussions.microsoft.com> wrote in message
news:51E75D73-8761-442E-B397-E424F62CC1FA@microsoft.com...
> Albert
>
> Once again, your assistance has been greatly appreciated!
>
> In addition to your instruction below, I had to do the following:
> - Add the text in the IFDData.cls file to a new Class Module in my Access
> 2003 app.

I don't know where the above IFDDdata.cls file comes from. You don't need
it.

The download I was
talking about is here:

http://sourceforge.net/projects/exifclass/

From the above, the zip file reuslts in TWO files

ExifReader.cls

and

Readme.txt

You simply do the cut/paste into a new "class", module, and you should be
fine.

>- Change clsPic.picFile = "c:\1.jpg" to clsPic.Load "c:\1.jpg"

Yes, that seems like a mistake on my part (sorry).

I suggest you use load aways...

>Tag(DateTimeOriginal)

I not looked as to what the difference is between DateTime, and Datetime
Original......

I suspect this will depend on the original camera...and how it is setup....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

==============================================================================
TOPIC: How do I return a Percentile Rank in Microsoft Access?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/ae9f54d2818421f3?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 21 2007 5:33 am
From: "Gary Walter"

Hi bob,

Unless this is for homework....

{meaning no offense}

please stop what you are doing and
go out and get a good book on database
and table design...honestly.

Do you anticipate "making" tables Product_2,
Product_3, Product_4, etc.? For what?

...or maybe I just don't understand...

In the calc for

Percentile = (Rank/TotCnt) * 100

is TotCnt over entire DataSet,

or over the filtered DataSet?

If "over filtered DataSet"....

Method 1) Divide and conquer
(recommend)

qryPreFilter:

SELECT Category, [Name], [Value]
FROM DataSet
WHERE (((Category)="Product_1"));

SELECT
Q.Category,
Q.[Name],
Q.[Value],
DCount("*","qryPreFilter", "[Value]<" & Q.[Value] ) AS ValRank,
DCount("*","qryPreFilter") AS TotCnt,
([ValRank]/[TotCnt])*100 AS Percentile
INTO Product_1
FROM qryPreFilter AS Q;

Method 2) Add filter to (one or both?) DCount

SELECT
D.Category,
D.[Name],
D.[Value],
DCount("*","DataSet", "[Value]<" & D.[Value],
"[Category] = 'Product_1'" ) AS ValRank,
DCount("*","DataSet","[Category]='Product_1'") AS TotCnt,
([ValRank]/[TotCnt])*100 AS Percentile
INTO Product_1
FROM DataSet AS D
WHERE D.Category = "Product_1";

Method 3) Update after make table

SELECT
D.Category,
D.[Name],
D.[Value],
IIf(True,Null,CDbl(0)) AS Percentile
INTO Product_1
FROM DataSet AS D
WHERE D.Category = "Product_1";

UPDATE Product_1 AS P SET
P.Percentile =
( DCount("*","Product_1", "[Value]<" & P.[Value]) /
DCount("*","Product_1") ) * 100;

good luck (with your homework?),

gary

"bobmount" wrote:
>....It seems like your query WOULD achieve what I'm after, but I
> can't seem to get the syntax right for use within an existing "Make Table"
> query in Access.
>
> Basically, I'd like to simply add the percentile_rank fields (as, I think,
> you've defined it) to the Access Query that's currently defined by the
> following SQL:
>
> SELECT Category, Name, Value INTO Product_1
> FROM DataSet
> WHERE (((Category)="Product_1"));
>
> Would you mind integrating your SQL into this structure? I've tried
> fiddling with it for hours, and can't seem to get it right....
>
> Thank you sooooo much (in advance!)....
>
> B
>
> "Gary Walter" wrote:
>
>>
>> "bobmount" wrote:
>> > I'm trying to add a field to a "Make Table" query in Access which will
>> > add
>> > a
>> > column representing the percentile rank of the [Value] field, based on
>> > the
>> > other values in that column (i.e., 1%-100%)....similar to the
>> > PERCENTILE
>> > RANK
>> > feature in Excel. Does anyone know how to do this? (Assume my table
>> > name
>> > is 'DataSet', and the field with the values to rank is [Value])....
>> >
>> Hi Bob,
>>
>> Are we talking about:
>>
>> Percentile = (Rank/TotalCount) * 100
>>
>> The following solution requires that you
>> be able to use DCount to produce a rank
>> and a total count over the group.
>>
>>
>> SELECT
>> D.[Value],
>> DCount("*","DataSet", "[Value]<" & D.[Value] ) AS ValRank,
>> DCount("*","DataSet") AS TotCnt,
>> ([ValRank]/[TotCnt])*100 AS Percentile
>> FROM DataSet AS D;
>>
>> the above won't resolve ties though (but can with
>> some other field added to DCount for ValRank...
>>
>> good luck,
>>
>> gary
>>
>>
>>

==============================================================================
TOPIC: Dynamic INSERT INTO command
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/b86524aeb23f77e0?hl=en
==============================================================================

== 1 of 3 ==
Date: Wed, Nov 21 2007 6:17 am
From: Luis

Hello.
I'm trying to create a SQL Insert Into string where one of the fields is the
value of a certain variable.

How can i do this?
What i've done is the following:

INSERT INTO P (Btsm, Bts, Data, Hora," & pa_Out & ") VALUES ('" & btsm &
"','" & bts & "','" & dia & "','" & hora & "','" & pa_Out_value & "');"

Pa_Out is the name of the field.

== 2 of 3 ==
Date: Wed, Nov 21 2007 6:49 am
From: LarryP

There are people here who do this imbedded SQL stuff way better than I do,
but let's take a shot at it: are you trying to make the FIELD you're
inserting into dynamic, or the DATA you're inserting into it dynamic?

If the field you're inserting into is always the same then this should work;
the incoming data (pa_Out_value) would vary dynamically, but it would always
go into the pa_Out field:

"INSERT INTO P (Btsm, Bts, Data, Hora, pa_Out) VALUES ('" & btsm &
> "','" & bts & "','" & dia & "','" & hora & "','" & pa_Out_value & "');"

(assuming all your values are text; I see you have them all set off with
single quotes)

((((By the way, is the field really Data, since what you're trying to put
into it is dia? Maybe a typo there?))))

On the other hand, if you're trying to put the pa_out_Value bit of
information into a different field depending on ???whatever???, the
following would seem to be what you need. pa_Out would be the dynamic field
name you want to put pa_Out_value into; the incoming data would always be the
same, but the field it goes into would vary depending on pa_Out.

"INSERT INTO P (Btsm, Bts, Data, Hora," & pa_Out & ") VALUES ('" & btsm &
> "','" & bts & "','" & dia & "','" & hora & "','" & pa_Out_value & "');"

"Luis" wrote:

> Hello.
> I'm trying to create a SQL Insert Into string where one of the fields is the
> value of a certain variable.
>
> How can i do this?
> What i've done is the following:
>
> INSERT INTO P (Btsm, Bts, Data, Hora," & pa_Out & ") VALUES ('" & btsm &
> "','" & bts & "','" & dia & "','" & hora & "','" & pa_Out_value & "');"
>
> Pa_Out is the name of the field.
>
>
>

== 3 of 3 ==
Date: Wed, Nov 21 2007 7:11 am
From: "Douglas J. Steele"

On the surface, that SQL statement looks correct.

What happens when you try to run it? What should happen instead? How are you
running it?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Luis" <Luis@discussions.microsoft.com> wrote in message
news:5C169854-A072-4C8C-9B96-0810F83EF466@microsoft.com...
> Hello.
> I'm trying to create a SQL Insert Into string where one of the fields is
> the
> value of a certain variable.
>
> How can i do this?
> What i've done is the following:
>
> INSERT INTO P (Btsm, Bts, Data, Hora," & pa_Out & ") VALUES ('" & btsm &
> "','" & bts & "','" & dia & "','" & hora & "','" & pa_Out_value & "');"
>
> Pa_Out is the name of the field.
>
>
>

==============================================================================
TOPIC: help with replace function
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/8d3f93c1a139047b?hl=en
==============================================================================

== 1 of 3 ==
Date: Wed, Nov 21 2007 8:18 am
From: <@discussions.microsoft.com>

i would like to replace substrings in txt files in folder and its subfolders

i have the sring to find and the string to replace in access table

it is my first time to do anything with vba

the function i found is here

Function Replace(ByVal Valuein As String, ByVal WhatToReplace As _
String, ByVal Replacevalue As String) As String
Dim Temp As String, P As Long
Temp = Valuein
P = InStr(Temp, WhatToReplace)
Do While P > 0
Temp = Left(Temp, P - 1) & Replacevalue & _
Mid(Temp, P + Len(WhatToReplace))
P = InStr(P + Len(Replacevalue), Temp, WhatToReplace, 1)
Loop
Replace = Temp
End Function

can someone help me with that?

thank you

== 2 of 3 ==
Date: Wed, Nov 21 2007 10:41 am
From: John W. Vinson

On Wed, 21 Nov 2007 08:18:03 -0800, ???? <@discussions.microsoft.com> wrote:

>hi
>
>i would like to replace substrings in txt files in folder and its subfolders
>
>i have the sring to find and the string to replace in access table
>
>it is my first time to do anything with vba
>
>the function i found is here
>
>
> Function Replace(ByVal Valuein As String, ByVal WhatToReplace As _
> String, ByVal Replacevalue As String) As String
> Dim Temp As String, P As Long
> Temp = Valuein
> P = InStr(Temp, WhatToReplace)
> Do While P > 0
> Temp = Left(Temp, P - 1) & Replacevalue & _
> Mid(Temp, P + Len(WhatToReplace))
> P = InStr(P + Len(Replacevalue), Temp, WhatToReplace, 1)
> Loop
> Replace = Temp
> End Function
>
>can someone help me with that?
>
>thank you
>

What version of Access are you using? There is now a builtin Replace()
function; this looks like a function which was written for Access97 or
earlier, which didn't have the function built in.

If you could describe what you're trying to accomplish (replace what, with
what, in which records???) we may be able to give you more specific help.

John W. Vinson [MVP]

== 3 of 3 ==
Date: Wed, Nov 21 2007 11:05 am
From: <@discussions.microsoft.com>

thank you for reply

i use access 2003

i would like to replace substrings that located in other folders and sub
folders on my pc such as names and adresses.

since they located in few files and there are many records in each file i
want to do it with macro from the db

in the db i have a table with 2 columns that contains the substring to find
and the substring to replace.

i hope i am clear now

thanks again

"John W. Vinson" wrote:

> On Wed, 21 Nov 2007 08:18:03 -0800, ???? <@discussions.microsoft.com> wrote:
>
> >hi
> >
> >i would like to replace substrings in txt files in folder and its subfolders
> >
> >i have the sring to find and the string to replace in access table
> >
> >it is my first time to do anything with vba
> >
> >the function i found is here
> >
> >
> > Function Replace(ByVal Valuein As String, ByVal WhatToReplace As _
> > String, ByVal Replacevalue As String) As String
> > Dim Temp As String, P As Long
> > Temp = Valuein
> > P = InStr(Temp, WhatToReplace)
> > Do While P > 0
> > Temp = Left(Temp, P - 1) & Replacevalue & _
> > Mid(Temp, P + Len(WhatToReplace))
> > P = InStr(P + Len(Replacevalue), Temp, WhatToReplace, 1)
> > Loop
> > Replace = Temp
> > End Function
> >
> >can someone help me with that?
> >
> >thank you
> >
>
> What version of Access are you using? There is now a builtin Replace()
> function; this looks like a function which was written for Access97 or
> earlier, which didn't have the function built in.
>
> If you could describe what you're trying to accomplish (replace what, with
> what, in which records???) we may be able to give you more specific help.
>
> John W. Vinson [MVP]
>

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

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

12 new messages in 8 topics - digest

2007-11-21T00:27:00.001-08:00

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: Rotate Tab Control
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/a45bbceaeb9a204f?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 4:31 pm
From: Marshall Barton

KJ-clueless wrote:

>Is there any way to rotate or otherwise change orientation of a tab control
>so that it's tabs appear on the left side rather than across the top? I've
>looked in several forumns with no luck.

What Doug said.

The obvious(?) workaround is to set the tab control's style
to None and use a bunch of buttons on the left. Each
button's Click event just needs to set the tab control's
Value to the corresponding page number. Unfortunately, the
focus can then become very tricky (in one case impossible)
to manage.

The fallback approach is what we used to do way back before
there was such a thing as a tab control. It's more work but
just as viable as a real tab control. Place the "page"
command buttons next to the area for the pseudo tab control.
I like to use an option group with option or radio buttons
with the OptionValues set to 1, 2, .... Use a rectangle
control to define the outline of the area.

Then add one page of controls into the rectangle area and
set each control's Tag property to a string such as "Page1".
Repeat this step for each set of controls, setting their Tag
tp "Page2" and so on. This will look like a mess with
controls on top of other controls, but you can't have
everything ;-)

The code in the option frame's AfterUpdate event could then
be something like:

Dim ctl As Control
For Each ctl In Me.Controls
If Left(ctl.Tag, 4) = "Page" Then
ctl.Visible = (Mid(ctl.Tag, 5) = Me.frame)
End If
Next ctl

--
Marsh
MVP [MS Access]

==============================================================================
TOPIC: find files on hard drive from a list field
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0b07605a1f34bfb2?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 5:17 pm
From: Isaac

I figured it out. No need to respond to this post. Thanks

"Isaac" wrote:

> I already know the logic to use in the process. What I don't know is how to
> perform step 3, especially the part of moving the file programmatically. I
> know how to open a folder, but I don't want to be in the loop. I want the
> program to do it all until it reaches an EOF.
>
> "chun" wrote:
>
> > You may want to follow the following steps:
> > 1. put the list of files in a Access table.
> > 2. open the table as a recordset, loop through each record and retrive the
> > file name, including the path.
> > 3. open the folder where the files reside, loop through each file in the
> > folder, if the file name macth that from the recordset, move them, otherwise
> > ignore the file and move to the next file.
> >
> > Hope this helps.
> >
> > --
> > For the freedom in MS Office!
> >
> >
> > "Isaac" wrote:
> >
> > > I have a need to move (or copy) certain files on my hard drive in a
> > > particular folder into another folder. Not all files in that folder will be
> > > moved/copied. I want to have only those files whose name is listed in a
> > > field in a table moved. I know can do this one record at a time, but would
> > > like it to be automated so that all files listed in that field are moved with
> > > a single click.
> > > I have no idea how to do this. Can anyone help.

== 1 of 2 ==
Date: Tues, Nov 20 2007 5:39 pm
From: BWD

Hi Group,

In a bind here; I have a form with 4 multi select list boxes. What I
would like to do is create a query that utilizes the values selected
in the 4 list boxes. I have gotten as far as creating a text box on
my form that records the values selected in the multi list. This text
box value is in the format of "Value1" or "Value2" or "Value3" etc.

However, setting the query parameter to my text box value does not
yield the results I am looking for. Can anyone provide some
guidance??

Thanks much!

== 2 of 2 ==
Date: Tues, Nov 20 2007 9:03 pm
From: "Stuart McCall"

"BWD" <sales@businesswebdesigns.net> wrote in message
news:28795881-df3a-459a-aab8-8c15ecbd990b@d61g2000hsa.googlegroups.com...
> Hi Group,
>
> In a bind here; I have a form with 4 multi select list boxes. What I
> would like to do is create a query that utilizes the values selected
> in the 4 list boxes. I have gotten as far as creating a text box on
> my form that records the values selected in the multi list. This text
> box value is in the format of "Value1" or "Value2" or "Value3" etc.
>
> However, setting the query parameter to my text box value does not
> yield the results I am looking for. Can anyone provide some
> guidance??
>
> Thanks much!

First of all, change your code that fills your textboxes so that they
contain comma-separated lists of values, ie:

"Value1","Value2","Value3"

Then construct your query's SQL something like this:

SELECT <fieldlist> FROM <tablename> WHERE <fieldname> IN(" & <textbox1> & ")
AND <fieldname> IN(" & <textbox2> & ")"
etc.

Replace everything in angle brackets with your real names.

==============================================================================
TOPIC: DateDiff
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/747b4c1e77e6252d?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 5:40 pm
From: John W. Vinson

On Tue, 20 Nov 2007 12:49:03 -0800, Roger Converse
<RogerConverse@discussions.microsoft.com> wrote:

>I was hoping that Access had somethig for holidays.

Well... it can't.

My local Islamic Center observes Eid ul-Fitr and Eid ul-Adha as holidays
(among others); they don't observe Easter Monday or Christmas Day. Columbus
Day might be observed as a holiday in Massachusetts; it's not in Idaho. Each
company has its own definition of what's a "holiday" and it's frankly
unreasonable to expect Access to magically intuit your particular set of days!

John W. Vinson [MVP]

==============================================================================
TOPIC: Finding the forms name in visual basic
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/9be877c43a6cfde5?hl=en
==============================================================================

== 1 of 3 ==
Date: Tues, Nov 20 2007 5:47 pm
From: Mabeline

Hi,

Is there anyway of finding the forms name that is currently being used by
the user in visual basic on a real time basis. Maybe something like:

if formname = "name"
variable = name
elseif formname = "othername"
variable = othername.
end if
end if

I am using a subform in 2 different forms that have different names for
specific reasons and I need to know which form the user is working with so
that I can ensure the correct table is updated.

Can anyone help.

Thanks in advance

Mabeline.

== 2 of 3 ==
Date: Tues, Nov 20 2007 6:14 pm
From: Daniel Pineault

Me.Form.Name
--
Hope this helps,

Daniel Pineault

"Mabeline" wrote:

> Hi,
>
> Is there anyway of finding the forms name that is currently being used by
> the user in visual basic on a real time basis. Maybe something like:
>
> if formname = "name"
> variable = name
> elseif formname = "othername"
> variable = othername.
> end if
> end if
>
> I am using a subform in 2 different forms that have different names for
> specific reasons and I need to know which form the user is working with so
> that I can ensure the correct table is updated.
>
> Can anyone help.
>
> Thanks in advance
>
> Mabeline.

== 3 of 3 ==
Date: Tues, Nov 20 2007 6:20 pm
From: Daniel Pineault

If you need to determine the name of the parent form from the subform then
try something like

Me.Parent.Name
--
Hope this helps,

Daniel Pineault

"Mabeline" wrote:

==============================================================================
TOPIC: Autocomplete in Access
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/c52e7d26a67857c6?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 6:28 pm
From: david12

On Nov 19, 1:29 pm, "Albert D. Kallal" <PleaseNOOOsPAMmkal...@msn.com>
wrote:
> As mentioned, we really don't have all auto complete inMSaccess.
>
> However we certainly do have combo boxes, and you can set those up to behave
> in much the same way.
>
> if you start typing in a combo box, and your first few characters match,
> then the combo box will auto complete for you.
>
> It turns out that in most cases, if you have repeating data, or entries in a
> particular column that are being typed over and over, then a solution in a
> database is to normalize your data.
>
> For example in excel, you might be typing in the invoice number, and a
> payment to that invoice. In effect you wind up typing the invoiced number
> over and over during the data entry system (or using the auto complete
> feature of excel to do this for you). However this is actually considered a
> bad design as still are entering the data over and over. (the fact that the
> process is automated does mean your making a mess of your data).
>
> InMSaccesswhat we would do is normalize the data, annually type in the
> invoice ONE time. We then use a sub form in which you type in the amounts.
> At the end of day the result we will not be typing in the invoice number
> over and over.
>
> So while products like excel have auto complete, the reason why they need
> this is because are not database systems, and they're very poor at working
> with data.
>
> Good normalized database designs suggest that you move that out into a
> separate table and normalize your data. This also explains while the other
> poster is saying that he's never needed, or cared for, or never needed auto
> complete. if you understand database modeling, you'll see a huge reduction
> in this requirement to have autocomplete.
>
> This does not mean that your forms desing will not have pick lists, or have
> the ability for you to type the first few characters from a list and have
> that value selected. You can, and should use a combo box for that need.
>
> A properly normalizedaccessdatabase will run absolute circles around an
> excel sheet for data entry in terms of speed, and reducing what you actually
> have to type in.
>
> --
> Albert D. Kallal (AccessMVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKal...@msn.com

My clients have asked for this more than once.

Combobox with Auto Expand set to Yes is what I use with the following
SQL in the RowSource:
SELECT <fldname> FROM <tablename> GROUP BY <fldname> HAVING <fldname>
Is Not Null ORDER BY <fldname>;
where <fldname> is the field in the Control Source of the combobox.

Then in the Form_AfterUpdate event, requery the combobox because it
can only show values already saved.

You can even do this in the table instead of a separate lookup table
which is real nice for minimizing clutter in your tables page.

One thing it doesn't do is automatically drop-down the list when user
starts typing.
This is possible but requires a public variable and a function in the
OnChange event of the combobox.
Let me know if you need that solution.

Another thing it doesn't do is allow users to change one value across
all records at once like a lookup table would provide.
But that can be dealt with using an update-type query (or RunSQL with
SQL string built in code).

Dave Stelle, Access Application Developer

==============================================================================
TOPIC: Birthdays this month
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e9b69c449420462f?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 6:39 pm
From: Chuck

thanks that worked!

"Douglas J. Steele" wrote:

> Actually, all you need is Day([Client_DoB]) as the computed field, since
> you're already limiting to a single month.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> news:%23qkajp2KIHA.3516@TK2MSFTNGP02.phx.gbl...
> > Add a computed field to the underlying query Format([Client_DoB], "ddmm")
> > and sort on it.
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "Chuck" <Chuck@discussions.microsoft.com> wrote in message
> > news:C49D31C8-2BB8-4643-AC3C-6E349A495ACE@microsoft.com...
> >> Thanks Doug, i used all your suggestions except i created following code
> >> to
> >> ues the cboMonth
> >> Private Sub cboMonth_AfterUpdate()
> >> Set SF = Me.[subfrmBirthdays].Form ' Reference the Subform through
> >> it's
> >> *control*
> >>
> >> SF.Filter = "[Month] = """ & Me.cboMonth & """"
> >> SF.FilterOn = True
> >>
> >> 'Requery subform
> >> Me!subfrmBirthdays.Form.Requery
> >> End Sub
> >>
> >> Once I've filtered the month, now i want to sort the Client_DoB field by
> >> dd
> >> ascending so 01-Dec-1948 is first, 02-Dec-1980 is second etc. How do i do
> >> that as part Case1 below?
> >> Private Sub Filter_Click()
> >> Dim FrmS As Access.Form
> >>
> >> Set SF = Me.[subfrmBirthdays].Form ' Reference the Subform through
> >> it's
> >> *control*
> >>
> >> Select Case Me.[BirthdayFilterOpt].Value
> >> Case 1 ' Sort by Day
> >> SF.OrderBy = ??????
> >> SF.OrderByOn = True
> >> Case 2 ' Sort by Gender
> >> SF.OrderBy = "[Gender] ASC"
> >> SF.OrderByOn = True
> >> Case 3 'Sort by Last Name
> >> SF.OrderBy = "[Last_Name] ASC"
> >> SF.OrderByOn = True
> >>
> >> End Select
> >> End Sub
> >>
> >> "Douglas J. Steele" wrote:
> >>
> >>> Private Sub Filter_Click()
> >>>
> >>> Me!NameOfSubformControl.Form.Requery
> >>>
> >>> End Sub
> >>>
> >>> It may well be that the subform control on the main form is also named
> >>> subfrmBirthdays, in which case the line would be
> >>>
> >>> Me!subfrmBirthdays.Form.Requery
> >>>
> >>> However, only you can determine that.
> >>>
> >>> --
> >>> Doug Steele, Microsoft Access MVP
> >>> http://I.Am/DougSteele
> >>> (no e-mails, please!)
> >>>
> >>>
> >>> "Chuck" <Chuck@discussions.microsoft.com> wrote in message
> >>> news:4490DEFB-B053-4EB8-B8A2-F77C4BABAE59@microsoft.com...
> >>> > Hi Doug, can you help me with the actual VBA code statements - I have
> >>> > added
> >>> > a Filter button that should execute the vba code, ie the cboMonth will
> >>> > be
> >>> > just used to select the the appropriate month - then you press Filter
> >>> > button.
> >>> >
> >>> > "Douglas J. Steele" wrote:
> >>> >
> >>> >> Try using =DateDiff("yyyy",[Client_DoB],Now()) for the Happy Birthday
> >>> >> field.
> >>> >>
> >>> >> To retrieve only those records with birthdays in whatever month has
> >>> >> been
> >>> >> selected in cboMonth, add a computed field Format([Client_DoB],
> >>> >> "mmmm")
> >>> >> to
> >>> >> your query, and set Forms!frmBirthsdays!cboMonth as its criteria. In
> >>> >> the
> >>> >> AfterUpdate event of the combo box, put code to requery the subform:
> >>> >> Me.NameOfSubformControl.Form.Requery. Make sure that you use the name
> >>> >> of
> >>> >> the
> >>> >> subform control on the form: depending on how you added
> >>> >> subfrmBirthdays
> >>> >> as a
> >>> >> subform, the name of the control might not be subfrmBirthdays.
> >>> >>
> >>> >> --
> >>> >> Doug Steele, Microsoft Access MVP
> >>> >> http://I.Am/DougSteele
> >>> >> (no e-mails, please!)
> >>> >>
> >>> >>
> >>> >> "Chuck" <Chuck@discussions.microsoft.com> wrote in message
> >>> >> news:D63B18B1-E4DC-46E3-8F75-4DDC1090346F@microsoft.com...
> >>> >> > Hi, i have a form (frmbirthdays) with a combo box (cboMonth) with
> >>> >> > months
> >>> >> > of
> >>> >> > the year starting at January, February etc. I have a subform
> >>> >> > (subfrmBirthdays) with following fileds; client name, gender,
> >>> >> > Client_DoB,
> >>> >> > Current Age, Happy Birthday.
> >>> >> > Current Age is calculated with formula:
> >>> >> > =DateDiff("yyyy",[Client_DoB],Now())+Int(Format(Now(),"mmdd")<Format([Client_DoB],"mmdd"))
> >>> >> >
> >>> >> > Client_DoB is Date Data Type. dd/mm/yyyy
> >>> >> > I would like the subfrm filtered by the month in the combo box when
> >>> >> > selected
> >>> >> > to reflect birthdays in November for example. I would also like
> >>> >> > Happy
> >>> >> > Birthday to reflect Age + 1 but only if the curent date is < the
> >>> >> > DoB.
> >>> >> > Can
> >>> >> > you
> >>> >> > help with VBA please...thanks
> >>> >>
> >>> >>
> >>> >>
> >>>
> >>>
> >>>
> >
> >
>
>
>

== 1 of 2 ==
Date: Tues, Nov 20 2007 9:13 pm
From: RobGMiller

Yes, it did speed it up a bit but it still takes about 15 seconds before
either the autoexec runs or the assigned display form comes up.
--
RobGMiller

"ruralguy via AccessMonster.com" wrote:

> Did you try Tony's suggestion of copying importing everything into a new,
> fresh mdb?
>
> RobGMiller wrote:
> >I've tried to open an unbound splash form as soon as the Autoexec is run or
> >by making it the primary display form. Either methods takes about as
> >objectionably long to reach the code that opens the form.
> >
> >I think there is something wrong other than the size of it. Do you know of a
> >place where the startup process prior to the Autoexec or Opening the primary
> >display form might be found.
> >
> >I did get an error when trying to decompile. However, I don't know how to
> >get around it. Incidentally, the decompile, compact and recompile method used
> >to clean up the database and speed it up a bit did corrupt it to the point
> >where I had to go back to the backup.
> >
> >I might have to remove so code one bit at a time to see which code the
> >decompile process objects to.
> >
> >> You should be able to open a Splash form (that has no RecordSource) quickly
> >> and then open the other form invisible and when loaded, turn on the
> >[quoted text clipped - 11 lines]
> >> >> >> >
> >> >> >> >Thanks for your help.
>
> --
> RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
> Please post back to this forum so all may benefit.
>
> Message posted via http://www.accessmonster.com
>
>

== 2 of 2 ==
Date: Tues, Nov 20 2007 10:38 pm
From: "Tony Toews [MVP]"

RobGMiller <RobGMiller@discussions.microsoft.com> wrote:

>Yes, it did speed it up a bit but it still takes about 15 seconds before
>either the autoexec runs or the assigned display form comes up.

Ok, now try the decompile on the new MDB. After making a copy of it.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

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

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

25 new messages in 13 topics - digest

2007-11-20T16:23:00.000-08:00

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: Help- Calling an ILX Excel function in Access
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/a3e8e9b40a52b038?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 11:21 am
From: Nate

Does anyone have any thoughts or experience with this?

"Nate" wrote:

> I am trying to call an ILX function (i.e. =ILX|Q!'ONFD-GA,BID') in an Access
> Form. In Excel, this function pulls live data from a financial server
> (Thompson TradeWeb) and gives the current quote. I am attempting to move
> information to Access and was wondering if it is possible to pull live data
> in a database. If it is possible, will the link update as the data changes?
> From reading other questions in this forum, it sounds like the best way of
> doing this would be to call an Excel function. My VB skills are lacking, so
> any help would be greatly appreciated.
>
> Thanks!

==============================================================================
TOPIC: usps api's?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/994f314776a9c2b7?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 11:36 am
From: C Hayes

Does anyone know about using usps api's for scrubbing address data?

I've got 15,000 + records I'd like to cleanse about twice a year. I was
hoping to write some code against a usps api.

Is this possible in access?

==============================================================================
TOPIC: When all records in a subform combo box are a certain value, f
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e03e744c4d3df358?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 11:54 am
From: mattieflo

Great thanks Stuart.

"Stuart McCall" wrote:

> "mattieflo" <mattieflo@discussions.microsoft.com> wrote in message
> news:E3133C56-7802-4B05-AAFE-08CB3C2D9F88@microsoft.com...
> > Hi Stuart,
> >
> > I just realized that EMAIL is also a subform. They are linked together and
> > EMAIL is the parent form and PURCHASE ORDERS is the child form. I still
> > want
> > it to do the same thing, but I'm not writing the code right that you
> > provided. Could you help me with how to reference that subform? Thanks.
> >
> > "Stuart McCall" wrote:
> >
> >> "mattieflo" <mattieflo@discussions.microsoft.com> wrote in message
> >> news:9E27FF53-D475-4758-AD63-BCEEAA10B1D5@microsoft.com...
> >> > Hello,
> >> >
> >> > I have a form named EMAIL and on it is a subform named PURCHASE ORDERS.
> >> > Each
> >> > EMAIL has several PURCHASE ORDERS. On the PURCHASE ORDER subform, there
> >> > is
> >> > a
> >> > drop down box that indicates Pending or Completed. I want to program
> >> > this
> >> > so
> >> > that when all PURCHASE ORDERS are set to "Completed" that it will flag
> >> > a
> >> > checkbox on the EMAIL main form. Does anyone have any suggestions on
> >> > how
> >> > to
> >> > go about this? Thanks!
> >>
> >> Use code like this in your main form's Current event:
> >>
> >> Dim CompletedCount As Long
> >> Dim SubformCount As Long
> >>
> >> CompletedCount = Dcount("*","PURCHASE ORDERS","ComboField = 'Completed'")
> >> SubformCount = Me.SubformControlName.Form.Recordcount
> >> Me.CheckboxName.Value = (CompletedCount = SubformCount)
> >>
> >> (alter the control names accordingly, of course)
>
> Ok, if I understand you correctly, getting the SubformCount ought to go
> something like this:
>
> SubformCount =
> Me.EMAILSubformControlName.Form.PURCHASEORDERSSubformControlName.Form.Recordcount
>
> (that's one line of code)
>
>
>
>
>
>

== 1 of 3 ==
Date: Tues, Nov 20 2007 12:21 pm
From: "Tony Toews [MVP]"

RobGMiller <RobGMiller@discussions.microsoft.com> wrote:

>I tried to decompile but it tells me that the VBA project in the database is
>corrupt.

The decompile is what I would do first in your situation but you got a
corrupt VBA message. I'd suggest importing the database into a new
MDB and then trying the decompile.

Note that orphaned compiled code, which decompile will strip out, gets
copied across when you create an MDE.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

== 2 of 3 ==
Date: Tues, Nov 20 2007 2:00 pm
From: RobGMiller

I've tried to open an unbound splash form as soon as the Autoexec is run or
by making it the primary display form. Either methods takes about as
objectionably long to reach the code that opens the form.

I think there is something wrong other than the size of it. Do you know of a
place where the startup process prior to the Autoexec or Opening the primary
display form might be found.

I did get an error when trying to decompile. However, I don't know how to
get around it. Incidentally, the decompile, compact and recompile method used
to clean up the database and speed it up a bit did corrupt it to the point
where I had to go back to the backup.

I might have to remove so code one bit at a time to see which code the
decompile process objects to.

--
RobGMiller

"ruralguy via AccessMonster.com" wrote:

> You should be able to open a Splash form (that has no RecordSource) quickly
> and then open the other form invisible and when loaded, turn on the
> visibility and close the Splash form.
>
> RobGMiller wrote:
> >Yes it is.
> >
> >So is the Access data container for the purpose of this test. Putting it on
> >a mapped network drive does not affect the opening time and it has a minor
> >effect on data access generally.
> >
> >> Your MDE *is* on the local drive, right?
> >>
> >[quoted text clipped - 18 lines]
> >> >> >
> >> >> >Thanks for your help.
>
> --
> RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
> Please post back to this forum so all may benefit.
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200711/1
>
>

== 3 of 3 ==
Date: Tues, Nov 20 2007 4:19 pm
From: "ruralguy via AccessMonster.com"

Did you try Tony's suggestion of copying importing everything into a new,
fresh mdb?

RobGMiller wrote:
>I've tried to open an unbound splash form as soon as the Autoexec is run or
>by making it the primary display form. Either methods takes about as
>objectionably long to reach the code that opens the form.
>
>I think there is something wrong other than the size of it. Do you know of a
>place where the startup process prior to the Autoexec or Opening the primary
>display form might be found.
>
>I did get an error when trying to decompile. However, I don't know how to
>get around it. Incidentally, the decompile, compact and recompile method used
>to clean up the database and speed it up a bit did corrupt it to the point
>where I had to go back to the backup.
>
>I might have to remove so code one bit at a time to see which code the
>decompile process objects to.
>
>> You should be able to open a Splash form (that has no RecordSource) quickly
>> and then open the other form invisible and when loaded, turn on the
>[quoted text clipped - 11 lines]
>> >> >> >
>> >> >> >Thanks for your help.

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

==============================================================================
TOPIC: how to loop through all tables in an Access dataset
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/7c7c53d0de384411?hl=en
==============================================================================

== 1 of 7 ==
Date: Tues, Nov 20 2007 12:33 pm
From: chun

Hi,

Can anybody show me how to loop through all tables in a Access dataset? The
object model for Access seems to be harder to understand than that for Excel,
why is that?

Thanks!!!

--
For the freedom in MS Office!

== 2 of 7 ==
Date: Tues, Nov 20 2007 12:44 pm
From: SHIPP

The following loops through all tables to see if a table exists.

TO SEE IF A TABLE EXISTS

Function fExistTable(strTableName As String) As Integer
Dim db As Database
Dim i As Integer
Set db = DBEngine.Workspaces(0).Databases(0)
fExistTable = False
db.TableDefs.Refresh
For i = 0 To db.TableDefs.Count - 1
If strTableName = db.TableDefs(i).Name Then
'Table Exists
fExistTable = True
Exit For
End If
Next I
Set db = Nothin
End Function

--
M. Shipp

"chun" wrote:

> Hi,
>
> Can anybody show me how to loop through all tables in a Access dataset? The
> object model for Access seems to be harder to understand than that for Excel,
> why is that?
>
> Thanks!!!
>
> --
> For the freedom in MS Office!

== 3 of 7 ==
Date: Tues, Nov 20 2007 12:45 pm
From: Roger Converse

Something like this should work.

Sub TableisThere(booIsThere As Boolean, TableName As String)

Dim tbldef As TableDef, tbldefs As TableDefs

Set tbldefs = CurrentDb.TableDefs

booIsThere = False

For Each tbldef In tbldefs

If tbldef.Name = TableName Then
'We found the proper table,
booIsThere = True
Exit Sub
End If

Next tbldef

End Sub

"chun" wrote:

== 4 of 7 ==
Date: Tues, Nov 20 2007 12:47 pm
From: chun

I wonder if I can loop through all the tables just using the Access objects,
without involving DAO. Can you hlep?
--
For the freedom in MS Office!

"SHIPP" wrote:

> The following loops through all tables to see if a table exists.
>
> TO SEE IF A TABLE EXISTS
>
> Function fExistTable(strTableName As String) As Integer
> Dim db As Database
> Dim i As Integer
> Set db = DBEngine.Workspaces(0).Databases(0)
> fExistTable = False
> db.TableDefs.Refresh
> For i = 0 To db.TableDefs.Count - 1
> If strTableName = db.TableDefs(i).Name Then
> 'Table Exists
> fExistTable = True
> Exit For
> End If
> Next I
> Set db = Nothin
> End Function
>
> --
> M. Shipp
>
>
> "chun" wrote:
>
> > Hi,
> >
> > Can anybody show me how to loop through all tables in a Access dataset? The
> > object model for Access seems to be harder to understand than that for Excel,
> > why is that?
> >
> > Thanks!!!
> >
> > --
> > For the freedom in MS Office!

== 5 of 7 ==
Date: Tues, Nov 20 2007 1:00 pm
From: "Albert D. Kallal"

"chun" <chun@discussions.microsoft.com> wrote in message
news:7860FBD7-0B27-4337-8751-844FE0370BF9@microsoft.com...
> Hi,
>
> Can anybody show me how to loop through all tables in a Access dataset?

You actually don't necessarily have to use a dataset, there is a so called
collection of all tables that we normally use.

eg:

Dim T As TableDef

For Each T In CurrentDb.TableDefs
Debug.Print T.Name
Next T

> The
> object model for Access seems to be harder to understand than that for
> Excel,
> why is that?

if you can answer the following question, then you'll be able to understand
your answer. Why is it more difficult to build a garage that it is to build
a dog house? after all, in both cases are simply using hammers and nails.
can you answer this question for me? if you can solve an answer this
question, then you'll have exactly the same answer as to when accessed model
is considerably more difficult than that of Excel.

In a nutshell, it's simply that MS access is a considerably more complex
product. MS access is a database system, and database systems are what we
use to manage information. Your local bank certainly will not keep all their
accouting data in a spreadsheet. However, I can bet you a million dollars
that that bank keeps its business account information in a database.
Database systems by their nature are more complex products than are
spreadsheets.

Building a garage is a considerably more difficult task then it is to build
a dog house. It should not be one of life's great mystries to you that it's
more difficult to build a garage, then it is a dog house.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

== 6 of 7 ==
Date: Tues, Nov 20 2007 1:12 pm
From: chun

I think I understand it a little bit now. Thank you so much for taking the
time to respond my question!

--
For the freedom in MS Office!

"Albert D. Kallal" wrote:

> "chun" <chun@discussions.microsoft.com> wrote in message
> news:7860FBD7-0B27-4337-8751-844FE0370BF9@microsoft.com...
> > Hi,
> >
> > Can anybody show me how to loop through all tables in a Access dataset?
>
> You actually don't necessarily have to use a dataset, there is a so called
> collection of all tables that we normally use.
>
> eg:
>
> Dim T As TableDef
>
> For Each T In CurrentDb.TableDefs
> Debug.Print T.Name
> Next T
>
>
>
> > The
> > object model for Access seems to be harder to understand than that for
> > Excel,
> > why is that?
>
> if you can answer the following question, then you'll be able to understand
> your answer. Why is it more difficult to build a garage that it is to build
> a dog house? after all, in both cases are simply using hammers and nails.
> can you answer this question for me? if you can solve an answer this
> question, then you'll have exactly the same answer as to when accessed model
> is considerably more difficult than that of Excel.
>
> In a nutshell, it's simply that MS access is a considerably more complex
> product. MS access is a database system, and database systems are what we
> use to manage information. Your local bank certainly will not keep all their
> accouting data in a spreadsheet. However, I can bet you a million dollars
> that that bank keeps its business account information in a database.
> Database systems by their nature are more complex products than are
> spreadsheets.
>
> Building a garage is a considerably more difficult task then it is to build
> a dog house. It should not be one of life's great mystries to you that it's
> more difficult to build a garage, then it is a dog house.
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com
>
>
>
>

== 7 of 7 ==
Date: Tues, Nov 20 2007 1:27 pm
From: "Dirk Goldgar"

"chun" <chun@discussions.microsoft.com> wrote in message
news:91A46BD4-4BB4-4AB5-8A1D-E173143DFB1A@microsoft.com...
>I wonder if I can loop through all the tables just using the Access
>objects,
> without involving DAO. Can you hlep?

Yes, in all versions Access 2000 forward, there's a built-in collection you
can use:

Dim ao As AccessObject

For Each ao In CurrentData.AllTables
Debug.Print ao.Name
Next ao

However, there's not a whole lot you can do with an AccessObject, so most
often you'll end up using DAO anyway.

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

(please reply to the newsgroup)

==============================================================================
TOPIC: MS-Access - File Import Specifications...
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/c73e3d40cce6e9cc?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 12:38 pm
From: chun

Try Schema, which is some kind of file defining the layout of the files you
intend to import. Then you need some vba coding to do the import.
--
For the freedom in MS Office!

"JEB" wrote:

> I have a rather large (2300 byte) file layout of Billing Data that I need to
> import into Access, field by field. The definition of this file exists in a
> Cobol Copybook. Assuming that the Copybook can be translated correctly into
> a form that represents the import wizard, can you directly import the file
> definition into Access? If so, how?
> Thank you.
>
> JEB

==============================================================================
TOPIC: Visible = True Vs Visble = False
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/ab35e58d40612388?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 12:42 pm
From: dsc2bjn

I will try it and see how it works, but I have created a pseudo-workaround.

I increased the height of all the headers to allow for the height of the
first header group print completely (granted if someone enters a longer
values it would still run over).

This wil buy me some time to attempt your solution.

"Marshall Barton" wrote:

> dsc2bjn wrote:
> >I think I did what you suggested.
>
>
> Forget that earlier partial workarounf, I wasn't done with
> it and did not mean to send it. The problem is even messier
> than I thought it would be.
>
> It turns out that the best I could do ran into another issue
> when one of the headers grow and the detail grows, then
> Access will grow the detail too much. It works well if the
> detail grows but none of the headers grow. It also works if
> any header grows but the detail does not grow.
>
> The messiness starts by adding an unbound hidden text box
> named txtMaxHdr to the top level group header. This will be
> set to the height of the tallest header.
>
> The next step is to create a group header section named
> DummyHdr for the detail sorting level. (If you are not
> sorting the details, create a group level on the PK field.)
> Add a text box named txtLine to this section and set its
> control source to =1 and RunningSum property to Over Group.
> This will be used to determine the first detail after one of
> the other headers has a new value. Make this header section
> invisible because it is only used to help the code.
>
> Hear's a copy of my report module. I hope you can unravel
> the line wrapping.
> -----------------------------------------------------------
> Private Sub Detail_Format(Cancel As Integer, FormatCount As
> Integer)
> If Me.txtLine = 1 And Me.Section(0).Height < txtMaxHdr
> Then
> Me.Section(0).Height = txtMaxHdr
> End If
> txtMaxHdr = 0
> End Sub
>
> Private Sub Detail_Print(Cancel As Integer, PrintCount As
> Integer)
> Me.Line (Me.EmployeeID.Left, 0)
> -Step(Me.EmployeeID.Width, Me.Height), , B
> Me.Line (Me.AbsenceDate.Left, 0)
> -Step(Me.AbsenceDate.Width, Me.Height), , B
> Me.Line (Me.Reason.Left, 0)-Step(Me.Reason.Width,
> Me.Height), , B
> End Sub
>
> Private Sub DummyHdr_Format(Cancel As Integer, FormatCount
> As Integer)
> Me.Section(0).Height = 0 'reduce detail size in case
> it was changed
> End Sub
>
> Private Sub EmployeeHdr_Print(Cancel As Integer, PrintCount
> As Integer)
> txtMaxHdr = Me.Height 'top group
> Me.MoveLayout = False
> End Sub
>
> Private Sub AbsenceHdr_Print(Cancel As Integer, PrintCount
> As Integer)
> If Me.Height > txtMaxHdr Then txtMaxHdr = Me.Height
> Me.MoveLayout = False 'intermediate groups
> End Sub
> -----------------------------------------------------------
> I tested that code in A2003. I think it should work in
> A2002, but don't know what will happen in A2007. It can not
> work in A2k or earlier versions. If you are using A97 or
> A2K then I might or might not be able to think up another
> way, but would rather not try unless it's necessary.
>
> --
> Marsh
> MVP [MS Access]
>

== 1 of 2 ==
Date: Tues, Nov 20 2007 12:45 pm
From: chun

You may want to follow the following steps:
1. put the list of files in a Access table.
2. open the table as a recordset, loop through each record and retrive the
file name, including the path.
3. open the folder where the files reside, loop through each file in the
folder, if the file name macth that from the recordset, move them, otherwise
ignore the file and move to the next file.

Hope this helps.

--
For the freedom in MS Office!

"Isaac" wrote:

> I have a need to move (or copy) certain files on my hard drive in a
> particular folder into another folder. Not all files in that folder will be
> moved/copied. I want to have only those files whose name is listed in a
> field in a table moved. I know can do this one record at a time, but would
> like it to be automated so that all files listed in that field are moved with
> a single click.
> I have no idea how to do this. Can anyone help.

== 2 of 2 ==
Date: Tues, Nov 20 2007 2:50 pm
From: Isaac

I already know the logic to use in the process. What I don't know is how to
perform step 3, especially the part of moving the file programmatically. I
know how to open a folder, but I don't want to be in the loop. I want the
program to do it all until it reaches an EOF.

"chun" wrote:

> You may want to follow the following steps:
> 1. put the list of files in a Access table.
> 2. open the table as a recordset, loop through each record and retrive the
> file name, including the path.
> 3. open the folder where the files reside, loop through each file in the
> folder, if the file name macth that from the recordset, move them, otherwise
> ignore the file and move to the next file.
>
> Hope this helps.
>
> --
> For the freedom in MS Office!
>
>
> "Isaac" wrote:
>
> > I have a need to move (or copy) certain files on my hard drive in a
> > particular folder into another folder. Not all files in that folder will be
> > moved/copied. I want to have only those files whose name is listed in a
> > field in a table moved. I know can do this one record at a time, but would
> > like it to be automated so that all files listed in that field are moved with
> > a single click.
> > I have no idea how to do this. Can anyone help.

== 1 of 2 ==
Date: Tues, Nov 20 2007 12:49 pm
From: Roger Converse

Thanks!

John, I was hoping that Access had somethig for holidays. With the weekends
taken care of, updating a table once a year is no big deal.

Thanks again.

Roger

"John W. Vinson" wrote:

> On Mon, 19 Nov 2007 09:34:01 -0800, Roger Converse
> <RogerConverse@discussions.microsoft.com> wrote:
>
> > Ir eally don't want to have to update a table every
> >year if possible.
>
> Then you will need to create a table for the movable holiday dates in every
> year that you expect the application to be in operation. You can certainly
> program to have December 25th as a holiday (if your company observes
> Christmas); but other variable holidays - Mondays after a Sunday holiday, say
> - may be harder to calculate.
>
> IOW there is nothing built in to Access which identifies a date as "a date
> that your company observes as a holiday" - you have to provide that
> information!
>
> John W. Vinson [MVP]
>

== 2 of 2 ==
Date: Tues, Nov 20 2007 2:24 pm
From: "Douglas J. Steele"

See whether my June, 2005 "Access Answers" column in Pinnacle Publication's
"Smart Access" (a followup to the September, 2004 column) helps. You can
download the columns (and sample databases) for free from

http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"Roger Converse" <RogerConverse@discussions.microsoft.com> wrote in message
news:87E03353-7019-4355-B94C-3174356ADA58@microsoft.com...
> Thanks!
>
> John, I was hoping that Access had somethig for holidays. With the
> weekends
> taken care of, updating a table once a year is no big deal.
>
> Thanks again.
>
> Roger
>
>
>
> "John W. Vinson" wrote:
>
>> On Mon, 19 Nov 2007 09:34:01 -0800, Roger Converse
>> <RogerConverse@discussions.microsoft.com> wrote:
>>
>> > Ir eally don't want to have to update a table every
>> >year if possible.
>>
>> Then you will need to create a table for the movable holiday dates in
>> every
>> year that you expect the application to be in operation. You can
>> certainly
>> program to have December 25th as a holiday (if your company observes
>> Christmas); but other variable holidays - Mondays after a Sunday holiday,
>> say
>> - may be harder to calculate.
>>
>> IOW there is nothing built in to Access which identifies a date as "a
>> date
>> that your company observes as a holiday" - you have to provide that
>> information!
>>
>> John W. Vinson [MVP]
>>

== 1 of 1 ==
Date: Tues, Nov 20 2007 12:57 pm
From: bobmount

Thanks, Gary....It seems like your query WOULD achieve what I'm after, but I
can't seem to get the syntax right for use within an existing "Make Table"
query in Access.

Basically, I'd like to simply add the percentile_rank fields (as, I think,
you've defined it) to the Access Query that's currently defined by the
following SQL:

SELECT Category, Name, Value INTO Product_1
FROM DataSet
WHERE (((Category)="Product_1"));

Would you mind integrating your SQL into this structure? I've tried
fiddling with it for hours, and can't seem to get it right....

Thank you sooooo much (in advance!)....

"Gary Walter" wrote:

>
> "bobmount" wrote:
> > I'm trying to add a field to a "Make Table" query in Access which will add
> > a
> > column representing the percentile rank of the [Value] field, based on the
> > other values in that column (i.e., 1%-100%)....similar to the PERCENTILE
> > RANK
> > feature in Excel. Does anyone know how to do this? (Assume my table
> > name
> > is 'DataSet', and the field with the values to rank is [Value])....
> >
> Hi Bob,
>
> Are we talking about:
>
> Percentile = (Rank/TotalCount) * 100
>
> The following solution requires that you
> be able to use DCount to produce a rank
> and a total count over the group.
>
>
> SELECT
> D.[Value],
> DCount("*","DataSet", "[Value]<" & D.[Value] ) AS ValRank,
> DCount("*","DataSet") AS TotCnt,
> ([ValRank]/[TotCnt])*100 AS Percentile
> FROM DataSet AS D;
>
> the above won't resolve ties though (but can with
> some other field added to DCount for ValRank...
>
> good luck,
>
> gary
>
>
>

== 1 of 3 ==
Date: Tues, Nov 20 2007 2:09 pm
From: KJ-clueless

Is there any way to rotate or otherwise change orientation of a tab control
so that it's tabs appear on the left side rather than across the top? I've
looked in several forumns with no luck.

Thanks in advance

== 2 of 3 ==
Date: Tues, Nov 20 2007 2:22 pm
From: "Douglas J. Steele"

I don't believe it is possible.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"KJ-clueless" <KJclueless@discussions.microsoft.com> wrote in message
news:25A672D0-459D-4875-A9C9-6B4DD3FBF357@microsoft.com...
> Is there any way to rotate or otherwise change orientation of a tab
> control
> so that it's tabs appear on the left side rather than across the top?
> I've
> looked in several forumns with no luck.
>
> Thanks in advance

== 3 of 3 ==
Date: Tues, Nov 20 2007 2:44 pm
From: KJ-clueless

Thank you... unfortunately this is what I thought as well.

"Douglas J. Steele" wrote:

> I don't believe it is possible.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "KJ-clueless" <KJclueless@discussions.microsoft.com> wrote in message
> news:25A672D0-459D-4875-A9C9-6B4DD3FBF357@microsoft.com...
> > Is there any way to rotate or otherwise change orientation of a tab
> > control
> > so that it's tabs appear on the left side rather than across the top?
> > I've
> > looked in several forumns with no luck.
> >
> > Thanks in advance
>
>
>

== 1 of 1 ==
Date: Tues, Nov 20 2007 3:18 pm
From: mattieflo

Hello, I'm trying to write a search form that will search a main form for
fields. The main form consists of several of several subforms. The following
piece of code successfully searches for the Buyer Field on the main field.

Private Sub cmdSearchBuyer_Click()

Dim stbuyernumber As String
Dim stDocName As String
Dim stLinkCriteria As String

stbuyernumber = InputBox("Enter Buyer Number", "Search Buyer Number")
If stbuyernumber <> "" Then
stDocName = "frmMain2"
stLinkCriteria = "[tblBuyer].[BuyerNum] = " & stbuyernumber
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frmSearch"
End If

A buyer can have many emails. I have an EMAIL subform that is linked to the
BUYER mainform. I want to search for the primary key of the email subform
(EmailID) so that it brings up the appropriate parent buyer number. Is there
a way to adjust this line in the code so it does that?

"stLinkCriteria = "[tblBuyer].[BuyerNum] = " & stbuyernumber"

Thanks!

End Sub

==============================================================================
TOPIC: Show me function
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/8c24e13f4016cce5?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 4:04 pm
From: pdouglas4294

Well, the vendor is Microsoft. I'm encountering it on SOME of the links
within Office 2K Help files. The links which will take you to another page
work. The links which expand/define a word do not. This problem is on two
seperate computers: An XPPRO Desktop and and XPPRO Laptop.
The Desktop, it had all of the Office 2K updates (including SP3). I
followed MS instructions and "Repaired" the installation. Problem persisted.
On the Laptop, again, it had all of the O2K Updates. This time, I
uninstalled Office, re-booted, re-installed, re-booted, applied the Office
2000 UA Control update (Refer article 262767), rebooted, and the problem
persisted.
This was before applying SR1 or SP3.

Internet Security Settings
Internet - Medium
Local Internet - Medium Low
Trusted Sites - Medium
Restricted Sites - High

Also running Symantec AV / Client Security 9.03 in a managed environment.
Have also tried it with the Fire wall dropped.

-Paul

"Tom van Stiphout" wrote:

> On Fri, 16 Nov 2007 11:29:01 -0800, Del
> <Del@discussions.microsoft.com> wrote:
>
> What did the help file or the vendor of this ocx say when you asked
> them?
>
> -Tom.
>
>
> >How do I turn on the "Show me" function that Ouactrl.ocx update disabled?
>

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

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

25 new messages in 13 topics - digest

2007-11-20T10:23:00.000-08:00

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: Automating Outlook from inside Access
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/ecb06f1dc0c3ed33?hl=en
==============================================================================

== 1 of 2 ==
Date: Tues, Nov 20 2007 12:50 am
From: "GeoffG"

On re-reading, I'm not sure I read your original post correctly. My fault.

Interesting solution - thanks for posting back.

Geoff

"Tylendal" <Tylendal@discussions.microsoft.com> wrote in message
news:0814C6B9-C094-4B53-8404-967DF4A96EC6@microsoft.com...
> Thanks...
>
> I posted there also. Discovered the MailItem .sentOnBehalfOfName which
> works perfect for what I needed.
>
>
> Fred Alyea
>
> "GeoffG" wrote:
>
>> The "olBCC" constant will designate recipients who are to receive blind
>> carbon copies. Recipients in the "To" field won't know that the BCC
>> recipients are getting copies.
>>
>> You've used the olBCC constant to designate **From recipients**, which
>> isn't
>> right. A recipient isn't a sender. I think you can only change the
>> sender
>> in Outlook 2007, not earlier versions. This is because the object model
>> for
>> earlier versions of Outlook don't have the necessary Sender property of
>> the
>> MailItem object.
>>
>> Suggest you repost to an Outlook newsgroup or search the Microsoft
>> website.
>> I know I've seen something recently on the MS site.
>>
>> Geoff
>>
>>
>>
>> "Tylendal" <Tylendal@discussions.microsoft.com> wrote in message
>> news:DEF7350B-EC6E-4153-A696-8FA5823F0B38@microsoft.com...
>> > Im attempting to automate and Outlook Email send from within an Access
>> > application. This is the code I'm using.
>> >
>> > Dim objOutlook As Outlook.Application
>> > Dim objOutlookMsg As Outlook.MailItem
>> > Dim objOutlookRecip As Outlook.Recipient
>> > Dim objOutlookAttach As Outlook.Attachment
>> >
>> > Dim mMgrAddy As String
>> > Dim mEmpAddy As String
>> >
>> > mBody = "This is test of the olOriginator MailItem"
>> >
>> > ' Create the Outlook session.
>> > Set objOutlook = CreateObject("Outlook.Application")
>> >
>> > ' Create the message.
>> > Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
>> >
>> > With objOutlookMsg
>> > ' Add the To recipient(s) to the message.
>> > Set objOutlookRecip = .Recipients.Add(mEmpAddy)
>> > objOutlookRecip.Type = olTo
>> >
>> > ' Add the From recipient(s) to the message.
>> > Set objOutlookRecip = .Recipients.Add(mMgrAddy)
>> > objOutlookRecip.Type = olBCC
>> >
>> > ' Set the Subject, Body, and Importance of the message.
>> > .Subject = "This is a TEST Email"
>> > .Body = mBody & vbCrLf & vbCrLf
>> > .Importance = olImportanceHigh 'High importance
>> >
>> > ' Resolve each Recipient's name.
>> > For Each objOutlookRecip In .Recipients
>> > objOutlookRecip.Resolve
>> > Next
>> >
>> > ' Should we display the message before sending?
>> > If DisplayMsg Then
>> > .Display
>> > Else
>> > .Save
>> > .Send
>> > End If
>> > End With
>> >
>> > My question has to do with the "From" button which opens Choose Sender
>> > when
>> > working directly in Outlook. The person running the Access application
>> > has
>> > rights to a specific email addy they can use but I cant seem to figure
>> > out
>> > how to make the application use the "From" or Choose Sender Outlook
>> > feature.
>> >
>> > Appreciate any assistance on this.
>> >
>> > Thanks
>> > Fred Alyea
>> >
>>
>>
>>

== 2 of 2 ==
Date: Tues, Nov 20 2007 6:08 am
From: Alt255

On Nov 19, 10:28 am, Tylendal <Tylen...@discussions.microsoft.com>
wrote:
> Im attempting to automate and Outlook Email send from within an Access
> application. This is the code I'm using.
>
> Dim objOutlook As Outlook.Application
> Dim objOutlookMsg As Outlook.MailItem
> Dim objOutlookRecip As Outlook.Recipient
> Dim objOutlookAttach As Outlook.Attachment
>
> Dim mMgrAddy As String
> Dim mEmpAddy As String
>
> mBody = "This is test of the olOriginator MailItem"
>
> ' Create the Outlook session.
> Set objOutlook = CreateObject("Outlook.Application")
>
> ' Create the message.
> Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
>
> With objOutlookMsg
> ' Add the To recipient(s) to the message.
> Set objOutlookRecip = .Recipients.Add(mEmpAddy)
> objOutlookRecip.Type = olTo
>
> ' Add the From recipient(s) to the message.
> Set objOutlookRecip = .Recipients.Add(mMgrAddy)
> objOutlookRecip.Type = olBCC
>
> ' Set the Subject, Body, and Importance of the message.
> .Subject = "This is a TEST Email"
> .Body = mBody & vbCrLf & vbCrLf
> .Importance = olImportanceHigh 'High importance
>
> ' Resolve each Recipient's name.
> For Each objOutlookRecip In .Recipients
> objOutlookRecip.Resolve
> Next
>
> ' Should we display the message before sending?
> If DisplayMsg Then
> .Display
> Else
> .Save
> .Send
> End If
> End With
>
> My question has to do with the "From" button which opens Choose Sender when
> working directly in Outlook. The person running the Access application has
> rights to a specific email addy they can use but I cant seem to figure out
> how to make the application use the "From" or Choose Sender Outlook feature.
>
> Appreciate any assistance on this.
>
> Thanks
> Fred Alyea

I use this

mitem.SentOnBehalfOfName = rst1.Fields("Mailbox")

I have a query that get the name of the mailbox and my Outlook account
has the ability to send emails on their behalf.

==============================================================================
TOPIC: Removing active users
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/dbd291b879c18b04?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 12:59 am
From: Chris

Thanks Marshall. I'll give it a go

"Marshall Barton" wrote:

> Chris wrote:
>
> >I can get a list of those currently in the database, but is there a way of
> >then kicking them out of it so I can work on it. I can only find references
> >to user groups etc.
>
>
> Here's some references:
>
> http://www.datastrat.com/Download2.html
> http://www.rogersaccesslibrary.com/download3.asp?SampleName=LogUsersOff.mdb
>
> --
> Marsh
> MVP [MS Access]
>

==============================================================================
TOPIC: Suppress MS-Access Window?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/bd783b6c207b1594?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 1:53 am
From: "Pieter Wijnen"

I won't reccomend it but you can try with

Public Declare Function SetParent Lib "User32" (ByVal hWndChild As Long,
ByVal hWndNewParent As Long) As Long

Usage (in report open):

SetParent Me.hWnd,0&

Pieter

"JimP" <jpockmire@comcast.net> wrote in message
news:qZednQTLD8aprd_anZ2dnUVZ_rKtnZ2d@comcast.com...
> Tnx, but not the database window - the entire MS-Access window.
>
> "PJFry" <PJFry@discussions.microsoft.com> wrote in message
> news:AE61B6E2-982D-45DC-97F1-726214B3BDEB@microsoft.com...
>> The simplest way is Tools, Startup.
>>
>> Under Display Form/Page choose the report. Then uncheck Display Database
>> Window.
>>
>> "JimP" wrote:
>>
>>> Is it possible to suppress the MS-Access window?
>>>
>>> At startup I would like to display a report in preview mode, without
>>> showing
>>> the Access window.
>>>
>>>
>>>
>
>

==============================================================================
TOPIC: create folder with package wizard
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/bf45931cb5c37053?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 1:59 am
From: petery@online.microsoft.com ("Peter Yang[MSFT]")

Hello,

I think there is no direct method in package wizard. However, you could run
a batch file to create the folder for you by using "homepath" variable. I
recommended to modify the Setup.ini file to chain the install. This chained
install allows the users to run the install from the Setup.exe that the
Packaging Wizard creates and then it kicks off the batch file which will
create the folder for you.

You may want to refer to the following article for more detailed
information:

KB 828947 "How to deploy multiple Office products in a single installation
in
Office 2003" http://support.microsoft.com/default.aspx?scid=kb;EN-US;828947

If you have further questions or comments, please feel free to let's know.
Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to

http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

== 1 of 2 ==
Date: Tues, Nov 20 2007 2:14 am
From: "Allen Browne"

The answer to your question will depend on how you have set your tables up.

It sounds like you need to teach Access the steps required to complete a
project. You could define that with tables like this:

Tool table (one record for each type of tool), with fields:
- ToolID (primary key)

ProjectType (one record for each type of thing you build.) Fields:
- ProjectTypeID (e.g. chair)

ProjectTypeStep table
- ProjectTypeID
- StepNum
- ToolID
(Primary key is ProjectTypeID + StepNum.)

Now that the database knows the steps for each type of project, you can
create the tables for the actual projects you do, like this:

Project table (one record for each project you take on):
- ProjectID
- ProjectType
- ClientID (who this project is for.)
- DueDate (when you're supposed to finish this.)

ProjectStep table (one record for each step of a project):
- ProjectID Which project
- StepNum Which Step of the project.
- ToolID: Tool for this step
- StepDate: Date/Time (when you started this step.)

I suspect you already have something like these 2 tables, with the form and
subform. If so, you can use the BeforeInsert event procedure of the subform
to look up the last step, figure out the next step and tool number, and
assign them to this new record.

This is untested aircode only, and needs debugging, but should give you the
idea:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rs As DAO.Recordset
Dim strWhere As String
Dim strSql As String
Dim varResult As Variant
Const strcStub = "SELECT StepNum, ToolID " & vbCrLf & _
"FROM ProjectTypeStep INNER JOIN Project " & _
"ON ProjectTypeStepID = Project.ProjectTypeID " & vbCrLf & _
"WHERE ("
Const strcTail = ") " & vbCrLf & "ORDER BY StepNum;"

With Me.Parent
If IsNull(!ProjectID) Then
Cancel = True
MsgBox "Enter the project in the main form first."
Else
strWhere = "ProjectID = " & !ProjectID
varResult = DMax("StepNum", "ProjectStep", strWhere)
If Not IsNull(varResult) Then
strWhere = strWhere & ") AND (StepNum > " & varResult
End If
strSql = strcStub & strWhere & strcTail
'Debug.Print strSql
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
If Not rs.RecordCount = 0 Then
Me.StepNum = rs!StepNum
Me.ToolID = rs!ToolID
End If
rs.Close
Set rs = Nothing
End If
End With
End Sub

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

"ArielZusya" <ArielZusya@discussions.microsoft.com> wrote in message
news:0DBEF3D6-4F75-4FA0-8107-AFDBCDA505EB@microsoft.com...
> I've got a form that uses subforms to display/enter data associated with a
> parent record. The "parent" is each day on a project and the "child" is
> each
> tool used for the project on that day. So... if project number 37,
> building
> a chair, requires green hammer and a blue saw on 11/27/07 the form would
> tell
> me that "chair" with ID 37 is associated with green hammer (tool1) and
> blue
> saw (tool2). And then if project number 37, building a char, requires a
> yellow level (tool3) and a purple tape measure (tool4) on 11/28/07, there
> would be a new record for 11/28 with that information in the submenu. I
> want
> to write code that will determine if this is the first new child record
> associated with the parent project or if not what the last child record
> entered associated with this project is and then populate the child's
> tool#
> with the next sequential tool# but I'm not sure where to even begin (I
> probably don't even have the lingo right). In other words, if on 11/29/07
> I
> go to enter the tools needed that day for the chair project (#37) I'd like
> it
> to know that the next sequential tool # is tool5 even though the current
> record is only linked to the 11/28 record by that 37. This sounds so much
> more complicated than it actually is... hopefully someone will be able to
> understand what I'm trying to do. Any suggestions? Thanks!

== 2 of 2 ==
Date: Tues, Nov 20 2007 7:53 am
From: ArielZusya

Hmm... well... before I get too deep in here, I want to make sure I follow.
Also, so we're speaking the same language, instead of having you guess at the
structure of my tables I'll just use your example tables to learn how to do
this and then I'll adapt it later to fit my db (which may be the wrong
structure and needs revamping anyhow *GRIN*). So... I think I followed most
of the table structure you wrote about but I want to be sure... so... here's
what I understood your post to need:

Tables:

tblProject.ProjectID (Primary Key - Text - holds the description of the
project)
tblProject.ProjectType (Text - project name from tblProjectType)
tblProject.ClientID (Text - holds the name of the client)
tblProject.DueDate (Date/Time - holds the date the project is due)

tblProjectStep.ProjectID (Primary Key - Text - description from tblProject)
tblProjectStep.StepNum (Primary Key - Text - StepNum from tblProjectStep)
tblProjectStep.ToolID (Primary Key - Text - ToolID from tblTool)
tblProjectStep.StepDate (Date/Time - holds the date that the step happened)

tblProjectType.ProjectTypeID (Primary Key - Text - ex: "chair")

tblProjectTypeStep.ProjectTypeID (Primary Key - Text)
tblProjectTypeStep.StepNum (Primary Key - Text)
tblProjectTypeStep.ToolID (Text - ToolID from tblTool)

tblTool.ToolID (Primary Key - Text - holds the name of the tool)

Relationships:

One : Many

tblProjectType.ProjectTypeID : tblProjectTypeStep.ProjectTypeID
tblTool.ToolID : tblProjectTypeStep.ToolID
tblProjectTypeStep.StepNum : tblProjectStep.StepNum
tblTool.ToolID : tblProjectStep.ToolID
tblProject.ProjectID : tblProjectStep.ProjectID

Is that right so far? I didn't want to start teaching myself what the code
means before getting the tables right. Incidentally, this is also my first
experience with using two fields as primary keys (I feel like such a n00b).
Anyhow... let me know. Thanks for your help (and patience *GRIN*)!

--Ariel

"Allen Browne" wrote:

> The answer to your question will depend on how you have set your tables up.
>
> It sounds like you need to teach Access the steps required to complete a
> project. You could define that with tables like this:
>
> Tool table (one record for each type of tool), with fields:
> - ToolID (primary key)
>
> ProjectType (one record for each type of thing you build.) Fields:
> - ProjectTypeID (e.g. chair)
>
> ProjectTypeStep table
> - ProjectTypeID
> - StepNum
> - ToolID
> (Primary key is ProjectTypeID + StepNum.)
>
> Now that the database knows the steps for each type of project, you can
> create the tables for the actual projects you do, like this:
>
> Project table (one record for each project you take on):
> - ProjectID
> - ProjectType
> - ClientID (who this project is for.)
> - DueDate (when you're supposed to finish this.)
>
> ProjectStep table (one record for each step of a project):
> - ProjectID Which project
> - StepNum Which Step of the project.
> - ToolID: Tool for this step
> - StepDate: Date/Time (when you started this step.)
>
> I suspect you already have something like these 2 tables, with the form and
> subform. If so, you can use the BeforeInsert event procedure of the subform
> to look up the last step, figure out the next step and tool number, and
> assign them to this new record.
>
> This is untested aircode only, and needs debugging, but should give you the
> idea:
>
> Private Sub Form_BeforeInsert(Cancel As Integer)
> Dim rs As DAO.Recordset
> Dim strWhere As String
> Dim strSql As String
> Dim varResult As Variant
> Const strcStub = "SELECT StepNum, ToolID " & vbCrLf & _
> "FROM ProjectTypeStep INNER JOIN Project " & _
> "ON ProjectTypeStepID = Project.ProjectTypeID " & vbCrLf & _
> "WHERE ("
> Const strcTail = ") " & vbCrLf & "ORDER BY StepNum;"
>
> With Me.Parent
> If IsNull(!ProjectID) Then
> Cancel = True
> MsgBox "Enter the project in the main form first."
> Else
> strWhere = "ProjectID = " & !ProjectID
> varResult = DMax("StepNum", "ProjectStep", strWhere)
> If Not IsNull(varResult) Then
> strWhere = strWhere & ") AND (StepNum > " & varResult
> End If
> strSql = strcStub & strWhere & strcTail
> 'Debug.Print strSql
> Set rs = dbEngine(0)(0).OpenRecordset(strSql)
> If Not rs.RecordCount = 0 Then
> Me.StepNum = rs!StepNum
> Me.ToolID = rs!ToolID
> End If
> rs.Close
> Set rs = Nothing
> End If
> End With
> End Sub
>
> --
> 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.
>
> "ArielZusya" <ArielZusya@discussions.microsoft.com> wrote in message
> news:0DBEF3D6-4F75-4FA0-8107-AFDBCDA505EB@microsoft.com...
> > I've got a form that uses subforms to display/enter data associated with a
> > parent record. The "parent" is each day on a project and the "child" is
> > each
> > tool used for the project on that day. So... if project number 37,
> > building
> > a chair, requires green hammer and a blue saw on 11/27/07 the form would
> > tell
> > me that "chair" with ID 37 is associated with green hammer (tool1) and
> > blue
> > saw (tool2). And then if project number 37, building a char, requires a
> > yellow level (tool3) and a purple tape measure (tool4) on 11/28/07, there
> > would be a new record for 11/28 with that information in the submenu. I
> > want
> > to write code that will determine if this is the first new child record
> > associated with the parent project or if not what the last child record
> > entered associated with this project is and then populate the child's
> > tool#
> > with the next sequential tool# but I'm not sure where to even begin (I
> > probably don't even have the lingo right). In other words, if on 11/29/07
> > I
> > go to enter the tools needed that day for the chair project (#37) I'd like
> > it
> > to know that the next sequential tool # is tool5 even though the current
> > record is only linked to the 11/28 record by that 37. This sounds so much
> > more complicated than it actually is... hopefully someone will be able to
> > understand what I'm trying to do. Any suggestions? Thanks!
>
>

== 1 of 2 ==
Date: Tues, Nov 20 2007 3:42 am
From: "Douglas J. Steele"

Add a computed field to the underlying query Format([Client_DoB], "ddmm")
and sort on it.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Chuck" <Chuck@discussions.microsoft.com> wrote in message
news:C49D31C8-2BB8-4643-AC3C-6E349A495ACE@microsoft.com...
> Thanks Doug, i used all your suggestions except i created following code
> to
> ues the cboMonth
> Private Sub cboMonth_AfterUpdate()
> Set SF = Me.[subfrmBirthdays].Form ' Reference the Subform through
> it's
> *control*
>
> SF.Filter = "[Month] = """ & Me.cboMonth & """"
> SF.FilterOn = True
>
> 'Requery subform
> Me!subfrmBirthdays.Form.Requery
> End Sub
>
> Once I've filtered the month, now i want to sort the Client_DoB field by
> dd
> ascending so 01-Dec-1948 is first, 02-Dec-1980 is second etc. How do i do
> that as part Case1 below?
> Private Sub Filter_Click()
> Dim FrmS As Access.Form
>
> Set SF = Me.[subfrmBirthdays].Form ' Reference the Subform through
> it's
> *control*
>
> Select Case Me.[BirthdayFilterOpt].Value
> Case 1 ' Sort by Day
> SF.OrderBy = ??????
> SF.OrderByOn = True
> Case 2 ' Sort by Gender
> SF.OrderBy = "[Gender] ASC"
> SF.OrderByOn = True
> Case 3 'Sort by Last Name
> SF.OrderBy = "[Last_Name] ASC"
> SF.OrderByOn = True
>
> End Select
> End Sub
>
> "Douglas J. Steele" wrote:
>
>> Private Sub Filter_Click()
>>
>> Me!NameOfSubformControl.Form.Requery
>>
>> End Sub
>>
>> It may well be that the subform control on the main form is also named
>> subfrmBirthdays, in which case the line would be
>>
>> Me!subfrmBirthdays.Form.Requery
>>
>> However, only you can determine that.
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Chuck" <Chuck@discussions.microsoft.com> wrote in message
>> news:4490DEFB-B053-4EB8-B8A2-F77C4BABAE59@microsoft.com...
>> > Hi Doug, can you help me with the actual VBA code statements - I have
>> > added
>> > a Filter button that should execute the vba code, ie the cboMonth will
>> > be
>> > just used to select the the appropriate month - then you press Filter
>> > button.
>> >
>> > "Douglas J. Steele" wrote:
>> >
>> >> Try using =DateDiff("yyyy",[Client_DoB],Now()) for the Happy Birthday
>> >> field.
>> >>
>> >> To retrieve only those records with birthdays in whatever month has
>> >> been
>> >> selected in cboMonth, add a computed field Format([Client_DoB],
>> >> "mmmm")
>> >> to
>> >> your query, and set Forms!frmBirthsdays!cboMonth as its criteria. In
>> >> the
>> >> AfterUpdate event of the combo box, put code to requery the subform:
>> >> Me.NameOfSubformControl.Form.Requery. Make sure that you use the name
>> >> of
>> >> the
>> >> subform control on the form: depending on how you added
>> >> subfrmBirthdays
>> >> as a
>> >> subform, the name of the control might not be subfrmBirthdays.
>> >>
>> >> --
>> >> Doug Steele, Microsoft Access MVP
>> >> http://I.Am/DougSteele
>> >> (no e-mails, please!)
>> >>
>> >>
>> >> "Chuck" <Chuck@discussions.microsoft.com> wrote in message
>> >> news:D63B18B1-E4DC-46E3-8F75-4DDC1090346F@microsoft.com...
>> >> > Hi, i have a form (frmbirthdays) with a combo box (cboMonth) with
>> >> > months
>> >> > of
>> >> > the year starting at January, February etc. I have a subform
>> >> > (subfrmBirthdays) with following fileds; client name, gender,
>> >> > Client_DoB,
>> >> > Current Age, Happy Birthday.
>> >> > Current Age is calculated with formula:
>> >> > =DateDiff("yyyy",[Client_DoB],Now())+Int(Format(Now(),"mmdd")<Format([Client_DoB],"mmdd"))
>> >> >
>> >> > Client_DoB is Date Data Type. dd/mm/yyyy
>> >> > I would like the subfrm filtered by the month in the combo box when
>> >> > selected
>> >> > to reflect birthdays in November for example. I would also like
>> >> > Happy
>> >> > Birthday to reflect Age + 1 but only if the curent date is < the
>> >> > DoB.
>> >> > Can
>> >> > you
>> >> > help with VBA please...thanks
>> >>
>> >>
>> >>
>>
>>
>>

== 2 of 2 ==
Date: Tues, Nov 20 2007 4:58 am
From: "Douglas J. Steele"

Actually, all you need is Day([Client_DoB]) as the computed field, since
you're already limiting to a single month.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:%23qkajp2KIHA.3516@TK2MSFTNGP02.phx.gbl...
> Add a computed field to the underlying query Format([Client_DoB], "ddmm")
> and sort on it.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Chuck" <Chuck@discussions.microsoft.com> wrote in message
> news:C49D31C8-2BB8-4643-AC3C-6E349A495ACE@microsoft.com...
>> Thanks Doug, i used all your suggestions except i created following code
>> to
>> ues the cboMonth
>> Private Sub cboMonth_AfterUpdate()
>> Set SF = Me.[subfrmBirthdays].Form ' Reference the Subform through
>> it's
>> *control*
>>
>> SF.Filter = "[Month] = """ & Me.cboMonth & """"
>> SF.FilterOn = True
>>
>> 'Requery subform
>> Me!subfrmBirthdays.Form.Requery
>> End Sub
>>
>> Once I've filtered the month, now i want to sort the Client_DoB field by
>> dd
>> ascending so 01-Dec-1948 is first, 02-Dec-1980 is second etc. How do i do
>> that as part Case1 below?
>> Private Sub Filter_Click()
>> Dim FrmS As Access.Form
>>
>> Set SF = Me.[subfrmBirthdays].Form ' Reference the Subform through
>> it's
>> *control*
>>
>> Select Case Me.[BirthdayFilterOpt].Value
>> Case 1 ' Sort by Day
>> SF.OrderBy = ??????
>> SF.OrderByOn = True
>> Case 2 ' Sort by Gender
>> SF.OrderBy = "[Gender] ASC"
>> SF.OrderByOn = True
>> Case 3 'Sort by Last Name
>> SF.OrderBy = "[Last_Name] ASC"
>> SF.OrderByOn = True
>>
>> End Select
>> End Sub
>>
>> "Douglas J. Steele" wrote:
>>
>>> Private Sub Filter_Click()
>>>
>>> Me!NameOfSubformControl.Form.Requery
>>>
>>> End Sub
>>>
>>> It may well be that the subform control on the main form is also named
>>> subfrmBirthdays, in which case the line would be
>>>
>>> Me!subfrmBirthdays.Form.Requery
>>>
>>> However, only you can determine that.
>>>
>>> --
>>> Doug Steele, Microsoft Access MVP
>>> http://I.Am/DougSteele
>>> (no e-mails, please!)
>>>
>>>
>>> "Chuck" <Chuck@discussions.microsoft.com> wrote in message
>>> news:4490DEFB-B053-4EB8-B8A2-F77C4BABAE59@microsoft.com...
>>> > Hi Doug, can you help me with the actual VBA code statements - I have
>>> > added
>>> > a Filter button that should execute the vba code, ie the cboMonth will
>>> > be
>>> > just used to select the the appropriate month - then you press Filter
>>> > button.
>>> >
>>> > "Douglas J. Steele" wrote:
>>> >
>>> >> Try using =DateDiff("yyyy",[Client_DoB],Now()) for the Happy Birthday
>>> >> field.
>>> >>
>>> >> To retrieve only those records with birthdays in whatever month has
>>> >> been
>>> >> selected in cboMonth, add a computed field Format([Client_DoB],
>>> >> "mmmm")
>>> >> to
>>> >> your query, and set Forms!frmBirthsdays!cboMonth as its criteria. In
>>> >> the
>>> >> AfterUpdate event of the combo box, put code to requery the subform:
>>> >> Me.NameOfSubformControl.Form.Requery. Make sure that you use the name
>>> >> of
>>> >> the
>>> >> subform control on the form: depending on how you added
>>> >> subfrmBirthdays
>>> >> as a
>>> >> subform, the name of the control might not be subfrmBirthdays.
>>> >>
>>> >> --
>>> >> Doug Steele, Microsoft Access MVP
>>> >> http://I.Am/DougSteele
>>> >> (no e-mails, please!)
>>> >>
>>> >>
>>> >> "Chuck" <Chuck@discussions.microsoft.com> wrote in message
>>> >> news:D63B18B1-E4DC-46E3-8F75-4DDC1090346F@microsoft.com...
>>> >> > Hi, i have a form (frmbirthdays) with a combo box (cboMonth) with
>>> >> > months
>>> >> > of
>>> >> > the year starting at January, February etc. I have a subform
>>> >> > (subfrmBirthdays) with following fileds; client name, gender,
>>> >> > Client_DoB,
>>> >> > Current Age, Happy Birthday.
>>> >> > Current Age is calculated with formula:
>>> >> > =DateDiff("yyyy",[Client_DoB],Now())+Int(Format(Now(),"mmdd")<Format([Client_DoB],"mmdd"))
>>> >> >
>>> >> > Client_DoB is Date Data Type. dd/mm/yyyy
>>> >> > I would like the subfrm filtered by the month in the combo box when
>>> >> > selected
>>> >> > to reflect birthdays in November for example. I would also like
>>> >> > Happy
>>> >> > Birthday to reflect Age + 1 but only if the curent date is < the
>>> >> > DoB.
>>> >> > Can
>>> >> > you
>>> >> > help with VBA please...thanks
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>
>

== 1 of 1 ==
Date: Tues, Nov 20 2007 6:31 am
From: JEB

I have a rather large (2300 byte) file layout of Billing Data that I need to
import into Access, field by field. The definition of this file exists in a
Cobol Copybook. Assuming that the Copybook can be translated correctly into
a form that represents the import wizard, can you directly import the file
definition into Access? If so, how?
Thank you.

JEB

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

== 1 of 2 ==
Date: Tues, Nov 20 2007 7:02 am
From: MKM

okay...this is what I figuerd out. If you have any option button with a
value of 1, Access always opens the form with that option selected,
regardless of the value you enter in the Default field of the frame. So, I
changed the value of my number 1 option to 9 and voila.

MKM

"tina" wrote:

> since you're using A2007, there may be something going on that i'm unaware
> of - i know nothing about A2007. sorry i wasn't able to help; good luck with
> it. tina
>
>
> "MKM" <MKM@discussions.microsoft.com> wrote in message
> news:57D86FEB-ED4D-45F6-8EA9-ECA0F43DC14E@microsoft.com...
> > I even created a new one (via Wizard) and choose "No default" I got a
> default!
> >
> > "tina" wrote:
> >
> > > open the form in Design view, click on the option group "frame" to
> select
> > > it. in the Properties box, go to the Default line. type a number 1 in
> the
> > > line, and press the Down arrow on the keyboard once. press the Up arrow
> > > once; the number 1 should be highlighted - DON'T touch it with the
> mouse.
> > > press the Delete key on the keyboard to delete the 1 value on the
> Default
> > > property line. press the Down arrow on the keyboard once. save the form
> and
> > > close it.
> > >
> > > hth
> > >
> > >
> > > "MKM" <MKM@discussions.microsoft.com> wrote in message
> > > news:ED263981-B6BD-47F9-AF89-83B9A6FE7682@microsoft.com...
> > > > I should have mentioned...I already tried setting the Default Value of
> the
> > > > frame to Null (literally typed in) and I also tried deleting the
> contents
> > > of
> > > > the Default property field in the property box. I also tried setting
> the
> > > > default value to "". When the form opens it always has option 1
> selected.
> > > > Maybe setting it in the OnOpen event?
> > > >
> > > > "tina" wrote:
> > > >
> > > > > 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!
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

== 2 of 2 ==
Date: Tues, Nov 20 2007 8:16 am
From: MKM

and one more crazy thing. I created a form in Access 2003 with the frame and
options as I wanted. I imported the form into my 07 database, copied the
option group onto my 07 form and it works...huh?

"tina" wrote:

== 1 of 8 ==
Date: Tues, Nov 20 2007 7:22 am
From: RobGMiller

Access 2003.

This is a front end whose tables are attached to an access or SQL server
back end data container. It is 3 megs large as an MDE. It's autoexec runs a
Main function. The time it takes to get to the main function is about 20
Seconds.

I'm assuming that if it is an MDE it's vba does not need to be compiled. I
have to conclude that the time it takes is simply loading it from the hard
drive.
I tried importing the tables to time the difference. The result is a bit
better by about 10%.

If I could start an unbound form while it reaches the Main function that
would be acceptable but nothing happens until it is done whatever it does to
reach the autoexec macro.

I've tried listing the splash screen form in the display form/page but it
comes up in about the same time as it takes to reach the AutoExec.

Is there a way to bring a form up while it it loading?

Thanks for your help.

--
RobGMiller

== 2 of 8 ==
Date: Tues, Nov 20 2007 7:53 am
From: "ruralguy via AccessMonster.com"

See if any of the suggestions in this link help:
http://www.granite.ab.ca/access/performancefaq.htm

RobGMiller wrote:
>Access 2003.
>
>This is a front end whose tables are attached to an access or SQL server
>back end data container. It is 3 megs large as an MDE. It's autoexec runs a
>Main function. The time it takes to get to the main function is about 20
>Seconds.
>
>I'm assuming that if it is an MDE it's vba does not need to be compiled. I
>have to conclude that the time it takes is simply loading it from the hard
>drive.
>I tried importing the tables to time the difference. The result is a bit
>better by about 10%.
>
>If I could start an unbound form while it reaches the Main function that
>would be acceptable but nothing happens until it is done whatever it does to
>reach the autoexec macro.
>
>I've tried listing the splash screen form in the display form/page but it
>comes up in about the same time as it takes to reach the AutoExec.
>
>Is there a way to bring a form up while it it loading?
>
>Thanks for your help.
>

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200711/1

== 3 of 8 ==
Date: Tues, Nov 20 2007 7:56 am
From: "Pieter Wijnen"

Can be related to your anti-virus program /settings

Pieter

"RobGMiller" <RobGMiller@discussions.microsoft.com> wrote in message
news:4F5A0ABF-BED0-4503-9B81-2BE2D86766C4@microsoft.com...
> Access 2003.
>
> This is a front end whose tables are attached to an access or SQL server
> back end data container. It is 3 megs large as an MDE. It's autoexec runs
> a
> Main function. The time it takes to get to the main function is about 20
> Seconds.
>
> I'm assuming that if it is an MDE it's vba does not need to be compiled. I
> have to conclude that the time it takes is simply loading it from the hard
> drive.
> I tried importing the tables to time the difference. The result is a bit
> better by about 10%.
>
> If I could start an unbound form while it reaches the Main function that
> would be acceptable but nothing happens until it is done whatever it does
> to
> reach the autoexec macro.
>
> I've tried listing the splash screen form in the display form/page but it
> comes up in about the same time as it takes to reach the AutoExec.
>
> Is there a way to bring a form up while it it loading?
>
> Thanks for your help.
>
> --
> RobGMiller

== 4 of 8 ==
Date: Tues, Nov 20 2007 9:10 am
From: RobGMiller

Thanks for the quick response.

I tried to decompile but it tells me that the VBA project in the database is
corrupt.
I was able to run it again after that. I did a few more things on the list
of recommendation and now it open in half the time.

Still, facing a grey sceen for 10 to 15 seconds depending on the speed of
the computer is not ideal if I can help it.

Any thoughts on showing something other than the blank background of the
empty access database window while it opens.

I dont suppose its possible to change the background without opening a form?

--
RobGMiller

"ruralguy via AccessMonster.com" wrote:

> See if any of the suggestions in this link help:
> http://www.granite.ab.ca/access/performancefaq.htm
>
> RobGMiller wrote:
> >Access 2003.
> >
> >This is a front end whose tables are attached to an access or SQL server
> >back end data container. It is 3 megs large as an MDE. It's autoexec runs a
> >Main function. The time it takes to get to the main function is about 20
> >Seconds.
> >
> >I'm assuming that if it is an MDE it's vba does not need to be compiled. I
> >have to conclude that the time it takes is simply loading it from the hard
> >drive.
> >I tried importing the tables to time the difference. The result is a bit
> >better by about 10%.
> >
> >If I could start an unbound form while it reaches the Main function that
> >would be acceptable but nothing happens until it is done whatever it does to
> >reach the autoexec macro.
> >
> >I've tried listing the splash screen form in the display form/page but it
> >comes up in about the same time as it takes to reach the AutoExec.
> >
> >Is there a way to bring a form up while it it loading?
> >
> >Thanks for your help.
> >
>
> --
> RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
> Please post back to this forum so all may benefit.
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200711/1
>
>

== 5 of 8 ==
Date: Tues, Nov 20 2007 9:40 am
From: "ruralguy via AccessMonster.com"

Your MDE *is* on the local drive, right?

RobGMiller wrote:
>Thanks for the quick response.
>
>I tried to decompile but it tells me that the VBA project in the database is
>corrupt.
>I was able to run it again after that. I did a few more things on the list
>of recommendation and now it open in half the time.
>
>Still, facing a grey sceen for 10 to 15 seconds depending on the speed of
>the computer is not ideal if I can help it.
>
>Any thoughts on showing something other than the blank background of the
>empty access database window while it opens.
>
>I dont suppose its possible to change the background without opening a form?
>
>> See if any of the suggestions in this link help:
>> http://www.granite.ab.ca/access/performancefaq.htm
>[quoted text clipped - 22 lines]
>> >
>> >Thanks for your help.

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

== 6 of 8 ==
Date: Tues, Nov 20 2007 9:58 am
From: RobGMiller

Yes it is.

So is the Access data container for the purpose of this test. Putting it on
a mapped network drive does not affect the opening time and it has a minor
effect on data access generally.

--
RobGMiller

"ruralguy via AccessMonster.com" wrote:

> Your MDE *is* on the local drive, right?
>
> RobGMiller wrote:
> >Thanks for the quick response.
> >
> >I tried to decompile but it tells me that the VBA project in the database is
> >corrupt.
> >I was able to run it again after that. I did a few more things on the list
> >of recommendation and now it open in half the time.
> >
> >Still, facing a grey sceen for 10 to 15 seconds depending on the speed of
> >the computer is not ideal if I can help it.
> >
> >Any thoughts on showing something other than the blank background of the
> >empty access database window while it opens.
> >
> >I dont suppose its possible to change the background without opening a form?
> >
> >> See if any of the suggestions in this link help:
> >> http://www.granite.ab.ca/access/performancefaq.htm
> >[quoted text clipped - 22 lines]
> >> >
> >> >Thanks for your help.
>
> --
> RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
> Please post back to this forum so all may benefit.
>
> Message posted via http://www.accessmonster.com
>
>

== 7 of 8 ==
Date: Tues, Nov 20 2007 9:59 am
From: RobGMiller

I shut off the anti virus and it had no noticible effect.

--
RobGMiller

"Pieter Wijnen" wrote:

> Can be related to your anti-virus program /settings
>
> Pieter
>
> "RobGMiller" <RobGMiller@discussions.microsoft.com> wrote in message
> news:4F5A0ABF-BED0-4503-9B81-2BE2D86766C4@microsoft.com...
> > Access 2003.
> >
> > This is a front end whose tables are attached to an access or SQL server
> > back end data container. It is 3 megs large as an MDE. It's autoexec runs
> > a
> > Main function. The time it takes to get to the main function is about 20
> > Seconds.
> >
> > I'm assuming that if it is an MDE it's vba does not need to be compiled. I
> > have to conclude that the time it takes is simply loading it from the hard
> > drive.
> > I tried importing the tables to time the difference. The result is a bit
> > better by about 10%.
> >
> > If I could start an unbound form while it reaches the Main function that
> > would be acceptable but nothing happens until it is done whatever it does
> > to
> > reach the autoexec macro.
> >
> > I've tried listing the splash screen form in the display form/page but it
> > comes up in about the same time as it takes to reach the AutoExec.
> >
> > Is there a way to bring a form up while it it loading?
> >
> > Thanks for your help.
> >
> > --
> > RobGMiller
>
>
>

== 8 of 8 ==
Date: Tues, Nov 20 2007 10:20 am
From: "ruralguy via AccessMonster.com"

You should be able to open a Splash form (that has no RecordSource) quickly
and then open the other form invisible and when loaded, turn on the
visibility and close the Splash form.

RobGMiller wrote:
>Yes it is.
>
>So is the Access data container for the purpose of this test. Putting it on
>a mapped network drive does not affect the opening time and it has a minor
>effect on data access generally.
>
>> Your MDE *is* on the local drive, right?
>>
>[quoted text clipped - 18 lines]
>> >> >
>> >> >Thanks for your help.

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200711/1

== 1 of 1 ==
Date: Tues, Nov 20 2007 8:09 am
From: "Gary Walter"

"bobmount" wrote:
> I'm trying to add a field to a "Make Table" query in Access which will add
> a
> column representing the percentile rank of the [Value] field, based on the
> other values in that column (i.e., 1%-100%)....similar to the PERCENTILE
> RANK
> feature in Excel. Does anyone know how to do this? (Assume my table
> name
> is 'DataSet', and the field with the values to rank is [Value])....
>
Hi Bob,

Are we talking about:

Percentile = (Rank/TotalCount) * 100

The following solution requires that you
be able to use DCount to produce a rank
and a total count over the group.

SELECT
D.[Value],
DCount("*","DataSet", "[Value]<" & D.[Value] ) AS ValRank,
DCount("*","DataSet") AS TotCnt,
([ValRank]/[TotCnt])*100 AS Percentile
FROM DataSet AS D;

the above won't resolve ties though (but can with
some other field added to DCount for ValRank...

good luck,

gary

== 1 of 2 ==
Date: Tues, Nov 20 2007 8:25 am
From: mattieflo

Hi Stuart,

I just realized that EMAIL is also a subform. They are linked together and
EMAIL is the parent form and PURCHASE ORDERS is the child form. I still want
it to do the same thing, but I'm not writing the code right that you
provided. Could you help me with how to reference that subform? Thanks.

"Stuart McCall" wrote:

> "mattieflo" <mattieflo@discussions.microsoft.com> wrote in message
> news:9E27FF53-D475-4758-AD63-BCEEAA10B1D5@microsoft.com...
> > Hello,
> >
> > I have a form named EMAIL and on it is a subform named PURCHASE ORDERS.
> > Each
> > EMAIL has several PURCHASE ORDERS. On the PURCHASE ORDER subform, there is
> > a
> > drop down box that indicates Pending or Completed. I want to program this
> > so
> > that when all PURCHASE ORDERS are set to "Completed" that it will flag a
> > checkbox on the EMAIL main form. Does anyone have any suggestions on how
> > to
> > go about this? Thanks!
>
> Use code like this in your main form's Current event:
>
> Dim CompletedCount As Long
> Dim SubformCount As Long
>
> CompletedCount = Dcount("*","PURCHASE ORDERS","ComboField = 'Completed'")
> SubformCount = Me.SubformControlName.Form.Recordcount
> Me.CheckboxName.Value = (CompletedCount = SubformCount)
>
> (alter the control names accordingly, of course)
>
>
>

== 2 of 2 ==
Date: Tues, Nov 20 2007 10:04 am
From: "Stuart McCall"

"mattieflo" <mattieflo@discussions.microsoft.com> wrote in message
news:E3133C56-7802-4B05-AAFE-08CB3C2D9F88@microsoft.com...
> Hi Stuart,
>
> I just realized that EMAIL is also a subform. They are linked together and
> EMAIL is the parent form and PURCHASE ORDERS is the child form. I still
> want
> it to do the same thing, but I'm not writing the code right that you
> provided. Could you help me with how to reference that subform? Thanks.
>
> "Stuart McCall" wrote:
>
>> "mattieflo" <mattieflo@discussions.microsoft.com> wrote in message
>> news:9E27FF53-D475-4758-AD63-BCEEAA10B1D5@microsoft.com...
>> > Hello,
>> >
>> > I have a form named EMAIL and on it is a subform named PURCHASE ORDERS.
>> > Each
>> > EMAIL has several PURCHASE ORDERS. On the PURCHASE ORDER subform, there
>> > is
>> > a
>> > drop down box that indicates Pending or Completed. I want to program
>> > this
>> > so
>> > that when all PURCHASE ORDERS are set to "Completed" that it will flag
>> > a
>> > checkbox on the EMAIL main form. Does anyone have any suggestions on
>> > how
>> > to
>> > go about this? Thanks!
>>
>> Use code like this in your main form's Current event:
>>
>> Dim CompletedCount As Long
>> Dim SubformCount As Long
>>
>> CompletedCount = Dcount("*","PURCHASE ORDERS","ComboField = 'Completed'")
>> SubformCount = Me.SubformControlName.Form.Recordcount
>> Me.CheckboxName.Value = (CompletedCount = SubformCount)
>>
>> (alter the control names accordingly, of course)

Ok, if I understand you correctly, getting the SubformCount ought to go
something like this:

SubformCount =
Me.EMAILSubformControlName.Form.PURCHASEORDERSSubformControlName.Form.Recordcount

(that's one line of code)

==============================================================================
TOPIC: every other row + datasheet + Cond Format
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/ad8fed09922c96ce?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 9:09 am
From: JK

Any idea why this won't work in AC2003 - SP3?

Option Compare Database

' Declare an instance of our class
Private CF As clsConditionalFormattingDataSheetView

Private Sub Form_Current()
' Call our redraw function.
' We have to do this here because of a bug using
' Withevents to sink a Form's events from a Class module.
CF.Redraw
End Sub

Private Sub Form_Load()
' startup our class
Set CF = New clsConditionalFormattingDataSheetView
' You must set a reference to a TextBox control
' that you have placed anywhere in the Detail section.
' Don't worry about the control's size or placement.
' The class will position, size and set it's properties as required.
CF.KeyFieldControl = Me.txtcustomerID

' Set the desired Highlight Color
CF.HighlightColor = CLng(vbRed)
End Sub

Private Sub Form_Unload(Cancel As Integer)
' Release the reference to our class.
Set CF = Nothing
End Sub

==============================================================================
TOPIC: Combo box based on anothr combo box
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/4e61d0b78a3f129d?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 20 2007 9:08 am
From: Tknows

the real issue I guess is that I expect the second combo box to generate a
set of values based on the selection of the first combo box. Instead each
item I select generates a parameter box. how do I elimnate this. My snytax
is in the second combo box is:
SELECT Picture.PictureId, Picture.PictureDescription, Picture.Location,
Picture.members, Picture.PositionDescription FROM Picture WHERE
(((Picture.PicYear)=[forms]![Select_Picture]![txtYear])) ORDER BY
Picture.PictureDescription;:

A requery in the after update of the first combo box does not seem to work

"Ofer Cohen" wrote:

> Look at this link on "Limit content of combo/list boxes" for example
>
> http://www.mvps.org/access/forms/frm0028.htm
>
> Also, this line
> form![SelectPicture]![cboPicYear]
>
> Should be with Forms and not Form:
> forms![SelectPicture]![cboPicYear]
>
> If you want to Access the second column in the Combo, create a text box in
> the form (not visible) and set it to the second column.
> In the ControlSource of the text box write
> =[ComboName].Column(1)
>
> Then, filter the query by this text box
> Forms![SelectPicture]![TextBoxName]
>
> --
> Good Luck
> BS"D
>
>
> "Tknows" wrote:
>
> > i'm trying to generate a combo box list based on the selection of another
> > combo box. I aslo need to requery the second combo box once a selection is
> > made in the first combo box.
> > the select query in the row source of the second combo box is the following:
> >
> > [Form]![SelectPicture]![cboPicYear (1)]
> >
> > This appears as a parameter in form view.
> > The SQL seems correct:
> > SELECT Picture.PictureId, Picture.PictureDescription, Picture.Location,
> > Picture.members, Picture.PositionDescription
> > FROM Picture
> > WHERE (((Picture.PicYear)=form![SelectPicture]![cboPicYear]))
> > ORDER BY Picture.PictureDescription;
> >
> > Please note teh cboPicYear has two columns one for the ID and the other for
> > the picture year. The picture year is the value that is displayed in the
> > combo box.
> >

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

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

8 new messages in 7 topics - digest

2007-11-20T00:24:00.001-08:00

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

microsoft.public.access.modulesdaovba@googlegroups.com

Today's topics:

* Populating Hyperlink from vba - 1 messages, 1 author
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/92eb08f1cad9e59b?hl=en
* How do I return a Percentile Rank in Microsoft Access? - 1 messages, 1
author
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/ae9f54d2818421f3?hl=en
* Update SQL problem - 1 messages, 1 author
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/cfdf53ee466a81de?hl=en
* create folder with package wizard - 1 messages, 1 author
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/bf45931cb5c37053?hl=en
* Visible = True Vs Visble = False - 1 messages, 1 author
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/ab35e58d40612388?hl=en
* Office 2003 SP3 broke my vba code "user-defined type not defined" - 2
messages, 2 authors
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e77fbf8c840cf62d?hl=en
* Combo box based on anothr combo box - 1 messages, 1 author
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/4e61d0b78a3f129d?hl=en

==============================================================================
TOPIC: Populating Hyperlink from vba
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/92eb08f1cad9e59b?hl=en
==============================================================================

== 1 of 1 ==
Date: Mon, Nov 19 2007 6:52 pm
From: "Dale"

Gary you are the best!!!!!!! You got it right away!!
This has eluded me and others in this newsgroup for a month now... Yes the
strfpath is the full path to the word doc file i.e.
strfpath="C:\somefolder\" And [Fname] as you guessed is a field in the
Purchase table (guess I need to be more explicit).
The code that worked if others find themselves needing this type of code:
strSql= "UPDATE tblpurchase_orders " _
& "SET [HLink] = [Fname] & '#" & strfpath _
& "' & [FName] " _
& "WHERE ([HLink] Is Null) AND ([PO] Is Null);"

Don't think I'd have ever come up with the single quotes..but obviously they
are needed

"Gary Walter" <gary@wrotein.msg> wrote in message
news:eoTnHxtKIHA.5580@TK2MSFTNGP02.phx.gbl...
>
> "Dale" <cyber@nowhere.com> wrote in message
> news:%23Gd8ZFrKIHA.3516@TK2MSFTNGP02.phx.gbl...
>>I am trying to run this code from a subroutine in vba... The filepath is a
>>constant string "strfpath". Currently I am getting a "missing operator"
>>error (where is it!!! the missing operator!!). Much appreciated if
>>someone could point me in the right direction. Thanks
>>
>> strSql = "UPDATE tblpurchase_orders SET tblpurchase_orders.HLink
>> =[Fname]#" & strfpath & "[fname]#" & _
>> " WHERE (((tblpurchase_orders.HLink) Is Null) AND
>> ((tblpurchase_orders.PO) Is Null))"
>>
> Hi Dale,
>
> On further thought...
>
> If [Fname] is field in tblpurchase_orders,
> then I think you want your SQL string to
> end up as (if you did Debug.Print strSQL)...
>
> if strfpath = "C:\" (does strfpath end with "\" ?)
>
> UPDATE tblpurchase_orders
> SET [HLink] = [Fname] & "#C:\" & [FName]
> WHERE
> ([HLink] Is Null)
> AND
> ([PO] Is Null);
>
> does that look right?
>
> if so...possibly try...
>
> strSql= "UPDATE tblpurchase_orders " _
> & "SET [HLink] = [Fname] & '#" & strfpath _
> & "' & [FName] " _
> & "WHERE ([HLink] Is Null) AND ([PO] Is Null);"
>
> testing in Immediate Window:
>
> strfpath="C:\"
>
> strSql= "UPDATE tblpurchase_orders " _
> & "SET [HLink] = [Fname] & '#" & strfpath _
> & "' & [FName] " _
> & "WHERE ([HLink] Is Null) AND ([PO] Is Null);"
>
> ?strSql
> UPDATE tblpurchase_orders SET [HLink] = [Fname] & '#C:\' & [FName] WHERE
> ([HLink] Is Null) AND ([PO] Is Null);
>

== 1 of 1 ==
Date: Mon, Nov 19 2007 8:07 pm
From: bobmount

I'm trying to add a field to a "Make Table" query in Access which will add a
column representing the percentile rank of the [Value] field, based on the
other values in that column (i.e., 1%-100%)....similar to the PERCENTILE RANK
feature in Excel. Does anyone know how to do this? (Assume my table name
is 'DataSet', and the field with the values to rank is [Value])....

Thanks, in advance!

==============================================================================
TOPIC: Update SQL problem
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/cfdf53ee466a81de?hl=en
==============================================================================

== 1 of 1 ==
Date: Mon, Nov 19 2007 8:10 pm
From: "Tony Toews [MVP]"

Billy B <BillyB@discussions.microsoft.com> wrote:

> DoCmd.RunSQL strSQLUpdateIt

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

== 1 of 1 ==
Date: Mon, Nov 19 2007 8:20 pm
From: HollyylloH

I have a database that I am packaging using the developer extensions package
wizard. I am wondering if there is a way to create a folder such as
"MyFolder" in the user's My Documents folder regardless of which Windows OS
they have installed.

== 1 of 1 ==
Date: Mon, Nov 19 2007 9:38 pm
From: Marshall Barton

dsc2bjn wrote:
>I think I did what you suggested.

Forget that earlier partial workarounf, I wasn't done with
it and did not mean to send it. The problem is even messier
than I thought it would be.

It turns out that the best I could do ran into another issue
when one of the headers grow and the detail grows, then
Access will grow the detail too much. It works well if the
detail grows but none of the headers grow. It also works if
any header grows but the detail does not grow.

The messiness starts by adding an unbound hidden text box
named txtMaxHdr to the top level group header. This will be
set to the height of the tallest header.

The next step is to create a group header section named
DummyHdr for the detail sorting level. (If you are not
sorting the details, create a group level on the PK field.)
Add a text box named txtLine to this section and set its
control source to =1 and RunningSum property to Over Group.
This will be used to determine the first detail after one of
the other headers has a new value. Make this header section
invisible because it is only used to help the code.

Hear's a copy of my report module. I hope you can unravel
the line wrapping.
-----------------------------------------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As
Integer)
If Me.txtLine = 1 And Me.Section(0).Height < txtMaxHdr
Then
Me.Section(0).Height = txtMaxHdr
End If
txtMaxHdr = 0
End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As
Integer)
Me.Line (Me.EmployeeID.Left, 0)
-Step(Me.EmployeeID.Width, Me.Height), , B
Me.Line (Me.AbsenceDate.Left, 0)
-Step(Me.AbsenceDate.Width, Me.Height), , B
Me.Line (Me.Reason.Left, 0)-Step(Me.Reason.Width,
Me.Height), , B
End Sub

Private Sub DummyHdr_Format(Cancel As Integer, FormatCount
As Integer)
Me.Section(0).Height = 0 'reduce detail size in case
it was changed
End Sub

Private Sub EmployeeHdr_Print(Cancel As Integer, PrintCount
As Integer)
txtMaxHdr = Me.Height 'top group
Me.MoveLayout = False
End Sub

Private Sub AbsenceHdr_Print(Cancel As Integer, PrintCount
As Integer)
If Me.Height > txtMaxHdr Then txtMaxHdr = Me.Height
Me.MoveLayout = False 'intermediate groups
End Sub
-----------------------------------------------------------
I tested that code in A2003. I think it should work in
A2002, but don't know what will happen in A2007. It can not
work in A2k or earlier versions. If you are using A97 or
A2K then I might or might not be able to think up another
way, but would rather not try unless it's necessary.

--
Marsh
MVP [MS Access]

==============================================================================
TOPIC: Office 2003 SP3 broke my vba code "user-defined type not defined"
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e77fbf8c840cf62d?hl=en
==============================================================================

== 1 of 2 ==
Date: Mon, Nov 19 2007 9:54 pm
From: DAB

After installing Office2003 SP3 I get the following error...
[Microsoft Visual Basic]
Compile error:
User-defined type not defined
The same database run on an Office2003 SP2 machine works fine.

Also, as I am creating a new database, just after saving the new name
db1.mdb, I get another error...
[Microsoft Visual Basic]
Object Library not registered

Any assistance would be GREATLY appreciated

== 2 of 2 ==
Date: Mon, Nov 19 2007 11:05 pm
From: "Tony Toews [MVP]"

DAB <DAB@discussions.microsoft.com> wrote:

>After installing Office2003 SP3 I get the following error...
> [Microsoft Visual Basic]
> Compile error:
> User-defined type not defined
>The same database run on an Office2003 SP2 machine works fine.
>
>Also, as I am creating a new database, just after saving the new name
>db1.mdb, I get another error...
> [Microsoft Visual Basic]
> Object Library not registered

Interesting. What are your references? The following is some VBA code
to put them in the debug window.

Dim ref As Reference

For Each ref In Access.References
Debug.Print ref.Name & " " & ref.FullPath & " " & ref.Major &
"." & ref.Minor
Next ref

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

== 1 of 1 ==
Date: Tues, Nov 20 2007 12:06 am
From: Ofer Cohen

Look at this link on "Limit content of combo/list boxes" for example

http://www.mvps.org/access/forms/frm0028.htm

Also, this line
form![SelectPicture]![cboPicYear]

Should be with Forms and not Form:
forms![SelectPicture]![cboPicYear]

If you want to Access the second column in the Combo, create a text box in
the form (not visible) and set it to the second column.
In the ControlSource of the text box write
=[ComboName].Column(1)

Then, filter the query by this text box
Forms![SelectPicture]![TextBoxName]

--
Good Luck
BS"D

"Tknows" wrote:

> i'm trying to generate a combo box list based on the selection of another
> combo box. I aslo need to requery the second combo box once a selection is
> made in the first combo box.
> the select query in the row source of the second combo box is the following:
>
> [Form]![SelectPicture]![cboPicYear (1)]
>
> This appears as a parameter in form view.
> The SQL seems correct:
> SELECT Picture.PictureId, Picture.PictureDescription, Picture.Location,
> Picture.members, Picture.PositionDescription
> FROM Picture
> WHERE (((Picture.PicYear)=form![SelectPicture]![cboPicYear]))
> ORDER BY Picture.PictureDescription;
>
> Please note teh cboPicYear has two columns one for the ID and the other for
> the picture year. The picture year is the value that is displayed in the
> combo box.
>

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

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