Excel for Life: 25 new messages in 12 topics

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

microsoft.public.access.modulesdaovba@googlegroups.com

Today's topics:

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

== 1 of 2 ==
Date: Tues, Nov 13 2007 8:22 am
From: morgan

Hello everybody, I'm making my MP3 collection db importing xls files created
by an MP3 tag editor. I have the following code to append the xls file to my
access table and everything work fine. Since the last row of the xls file is
always "this list has been created with..." I'd like to modify the code to
delete the last record appended on my access table and then refresh the form,
in order to get immeditely the new records. I'm using access 2003.
Tks in advance

Private Sub Comando10_Click()

Dim dlg As FileDialog
Dim Importfile As String

Set dlg = Application.FileDialog(msoFileDialogFilePicker)
With dlg
.Title = "Select the Excelfile to import"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Files", "*.xls", 1
.Filters.Add "All Files", "*.*", 2
If .Show = -1 Then
Importfile = .SelectedItems(1)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"Db_MP3", Importfile, True, "A1:E1000"
Else
Exit Sub
End If
End With

End Sub

== 2 of 2 ==
Date: Tues, Nov 13 2007 9:26 am
From: "John Spencer"

Why not run a delete query that deletes any record that has the Phrase "This
List has been created "

You can probably just add the following line (all one line) of code to your
current code.

CurrentDb().Execute "DELETE FROM db_MP3 WHERE [Somefield] Like 'This List
has been created*'"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

"morgan" <morgan@discussions.microsoft.com> wrote in message
news:034FFDE9-3AF9-4948-981E-EC2A498ABD3A@microsoft.com...
> Hello everybody, I'm making my MP3 collection db importing xls files
> created
> by an MP3 tag editor. I have the following code to append the xls file to
> my
> access table and everything work fine. Since the last row of the xls file
> is
> always "this list has been created with..." I'd like to modify the code to
> delete the last record appended on my access table and then refresh the
> form,
> in order to get immeditely the new records. I'm using access 2003.
> Tks in advance
>
> Private Sub Comando10_Click()
>
> Dim dlg As FileDialog
> Dim Importfile As String
>
> Set dlg = Application.FileDialog(msoFileDialogFilePicker)
> With dlg
> .Title = "Select the Excelfile to import"
> .AllowMultiSelect = False
> .Filters.Clear
> .Filters.Add "Excel Files", "*.xls", 1
> .Filters.Add "All Files", "*.*", 2
> If .Show = -1 Then
> Importfile = .SelectedItems(1)
>
> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
> "Db_MP3", Importfile, True, "A1:E1000"
> Else
> Exit Sub
> End If
> End With
>
> End Sub
>

==============================================================================
TOPIC: Repair/update database
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/2f11a0b02f34bf6c?hl=en
==============================================================================

== 1 of 2 ==
Date: Tues, Nov 13 2007 8:28 am
From: raja07

this works perfectly! thank you so much for saving me days, weeks of
fraustration
--
raj

"Douglas J. Steele" wrote:

> Take a look at http://support.microsoft.com/kb/210297
>
> Hopefully your application is split into a front-end (containing the
> queries, forms, reports, macros and modules), linked to a back-end
> (containing the tables and relations). Only the back-end should be on the
> server: every user should have his/her own copy of the front-end, ideally on
> his/her hard drive. If that's not the way your application is, it should be.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "raja07" <raja07@discussions.microsoft.com> wrote in message
> news:7A9A056C-8878-4928-AB3E-D9D8A9586CD3@microsoft.com...
> >I have a question that has been fraustrating me for some time. how do i
> >force
> > users out of my database to perform maintenance? short of literally
> > forcing
> > them out that is i'd like to be able to close the application so that i
> > can
> > make updates etc. folks leave at the end of the day and leave the
> > application
> > open and i have to hunt them down to close the application by which time
> > other people jump in; so fraustrating. any suggestions on work-arounds
> > would
> > be extremely helpful. thanks a bunch!
> > --
> > raj
>
>
>

== 2 of 2 ==
Date: Tues, Nov 13 2007 8:40 am
From: raja07

that's a great thought, thanks for letting me know. we actually sat around
trying to come up with the most ungracious things to pop up prior to quitting
the application but i think we'll be "graceful". this is so exciting; i just
passed the code around for everyone in my group now they're excited
--
raj

"Tom van Stiphout" wrote:

> On Tue, 13 Nov 2007 06:14:11 -0800, raja07
> <raja07@discussions.microsoft.com> wrote:
>
> In addition to what Doug said: you can write some code in the timer
> event of a (potentially hidden) form to once a minute inspect a value
> in some table. If that value indicates "Shut Down", it will do so. Of
> course you'll want to be graceful about that.
>
> -Tom.
>
>
> >I have a question that has been fraustrating me for some time. how do i force
> >users out of my database to perform maintenance? short of literally forcing
> >them out that is i'd like to be able to close the application so that i can
> >make updates etc. folks leave at the end of the day and leave the application
> >open and i have to hunt them down to close the application by which time
> >other people jump in; so fraustrating. any suggestions on work-arounds would
> >be extremely helpful. thanks a bunch!
>

==============================================================================
TOPIC: Passing a value from Access to Word
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/da52d420487d1ac6?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 13 2007 8:30 am
From: "Kathy Webster"

Anyone? :-)

"Kathy Webster" <slickdock@yahoo.com> wrote in message
news:47363c00$0$25167$4c368faf@roadrunner.com...
>I have an unbound combo box field in a form, which we will call
>[forms]![colorselection]![color]. The user will select either "pink" or
>"blue" in this unbound combo box.
>
> I already have vb code that opens word and runs a macro. The same macro
> will, and should, execute, regardless of the contents of the combo box.
> But...
> If the user has selected "pink", I want a certain IF statement in the word
> macro to execute.
> If the user has selected "blue", I want a different IF statement in the
> word macro to execute.
>
> So I believe I need to pass the value from that combo box to the word
> macro, and I figure the way to do that is through variables.
>
> Can anyone help?
> Thank you,
> Kathy
>
> "Tom van Stiphout" <no.spam.tom7744@cox.net> wrote in message
> news:qphaj3deogmr15n5iped5obpt2ard9sasi@4ax.com...
>> On Fri, 9 Nov 2007 13:32:42 -0800, "Kathy Webster"
>> <slickdock@yahoo.com> wrote:
>>
>> This is probably beyond what could be expected of a rookie.
>>
>> The details of the solution depend so much about the details of your
>> exact situation that a generic solution is hard to give.
>> Perhaps a public property in the Access application will do.
>> Perhaps it is simpler to store this "variable" in the db, and write an
>> expression in Word.
>> Is this a mailmerge situation, or what?
>>
>> If you need professional assistance, "Microsoft Solution Provider" in
>> your yellow pages may be a good place to start.
>>
>> -Tom.
>>
>>
>>
>>>Can I store a variable in access (variable x), then write a word macro
>>>that
>>>says "if variable x = "blue" then type "blue"?
>>>
>>>If I can, please know that I don't even know how to store a variable in
>>>Access, so I will really need help.
>>>
>>>Thanks,
>>>Kathy
>>>
>
>

==============================================================================
TOPIC: Balloons
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/c09dde47ac81bd1b?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 13 2007 8:44 am
From: Mike S

I've just upgraded to Access 2007 from 2003 version. I have many, many
modules with balloons for the users to make choices which in most cases
populate variables.

In references I have chosen Microsoft Access and Office 12.0 Object
Libraries but I cannot get the balloons to open.

Please help. Thanks.

==============================================================================
TOPIC: VBA Word Wrap?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/b744d811e070b9df?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 13 2007 8:47 am
From: "BruceM"

While I agree that assigning blocks of text to variables makes a lot of
sense, it is often useful to wrap text. In general, use the underscore
character:

Dim strMsg as String
strMsg = "I have a " & [Task/Problem] & " in " & _
[City] & ", " & [State] & " " & [Zip] & _
" and would like to see if you are available " & _
"some time in the next few days. " & _
"Parts will be onsite and the call pays " & _
"a flat rate of $30." & vbCrLf & _
"WE REQUEST THAT YOU RESPOND IN " & _
"SOME WAY TO THIS MESSAGE."

Note that there is a space before the underscore character. Also, in VBA
you can use vbCrLf to go to a new line. I find it more convenient than
Chr(13) & Chr(10). I see that you are using Chr(13) alone, but I believe
you will find this does not always work as intended, although I cannot
provide details.

You may find the most convenient option is to build the string as suggested
in the previous response, using the underscore as needed to make the text
easy to read.

"ServiceEnvoy" <serviceenvoy@gmail.com> wrote in message
news:1194950750.202210.38930@o38g2000hse.googlegroups.com...
> I've got a command button that creates an outlook email. I then use
> fields from the form to fill in places in the subject and body of the
> email. I use so much space in the body of the email that the VBA code
> tries to wrap and then it turns red and doesn't work. Is there a way
> to use more characters/length/word wrap in VBA code or am I just
> trying to make the VBA code do too much?
>
> Example code:
> .body = "I have a " & [Task/Problem] & " in " & [City] & ", " &
> [State] & " " & [Zip] & " and would like to see if you are available
> some time in the next few days. Parts will be onsite and the call pays
> a flat rate of $30. If you require a higher rate to do the call,
> please reply to this email with your offer. We will take the lowest
> qualified offer. Please respond ASAP if you're interested and I can
> give you more details." & Chr(13) & "WE REQUEST THAT YOU RESPOND IN
> SOME WAY TO THIS MESSAGE. After we attempt to contact a technician 3
> times and receive no response, we remove that technician." & Chr(13) &
> "If you can't do this call, simply reply to this message and let me
> know. I'll notate that you responded and keep you in mind for future
> work." & Chr(13) & "If you have moved, remember we do calls
> nationwide. Just reply to this email with any updates and we'll update
> your profile." (I RUN OUT OF SPACE SOMEWHERE HERE BUT STILL NEED
> ENOUGH SPACE FOR ANOTHER SENTENCE AND SPACE FOR MY SIGNATURE)
>

==============================================================================
TOPIC: TransferSpreadsheet Command error
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/f4438c755126e90b?hl=en
==============================================================================

== 1 of 7 ==
Date: Tues, Nov 13 2007 9:08 am
From: Ann B

I am new to VBA programming. I think I am doing this right but keep getting
the below error when typing in my TransferSpreadsheet Command. I am trying to
write code so my query after being opened and run via form exports the
results into Excel. My queries name is Subcycle Data and I want it to have a
field heading row. Can anyone see where I am going wrong?

Compile Error- Expected: Expression

DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"Subcycle
Data",,True,,,

== 2 of 7 ==
Date: Tues, Nov 13 2007 9:20 am
From: "Douglas J. Steele"

You're not supplying a file name to which to export. You also don't need the
commas at the end.

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

"Ann B" <AnnB@discussions.microsoft.com> wrote in message
news:7A26E4A1-B323-4424-A8BC-5930A3A02E6F@microsoft.com...
>I am new to VBA programming. I think I am doing this right but keep
>getting
> the below error when typing in my TransferSpreadsheet Command. I am trying
> to
> write code so my query after being opened and run via form exports the
> results into Excel. My queries name is Subcycle Data and I want it to
> have a
> field heading row. Can anyone see where I am going wrong?
>
> Compile Error- Expected: Expression
>
> DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"Subcycle
> Data",,True,,,

== 3 of 7 ==
Date: Tues, Nov 13 2007 9:28 am
From: fredg

On Tue, 13 Nov 2007 09:08:00 -0800, Ann B wrote:

> I am new to VBA programming. I think I am doing this right but keep getting
> the below error when typing in my TransferSpreadsheet Command. I am trying to
> write code so my query after being opened and run via form exports the
> results into Excel. My queries name is Subcycle Data and I want it to have a
> field heading row. Can anyone see where I am going wrong?
>
> Compile Error- Expected: Expression
>
> DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"Subcycle
> Data",,True,,,

1) You haven't told Access into what spreadsheet to export the data.

2) If you have no arguments to use after your last argument (i.e.
True,,,), you do not need to add commas.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Subcycle
Data", "c:\MyFolderName\SpreadsheetName.xls", True

The above will transfer your query data into a worksheet named
"Subcycle Data" in a spreadsheet named "SpreadsheetName.xls" in the
folder "MyFolderName".
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

== 4 of 7 ==
Date: Tues, Nov 13 2007 9:34 am
From: Ann B

It says that is optional, but I can insert something in there. What if I
just want it to create a new excel file? Should I put something in like
C:\Documents and Settings\What if I want a generic user name?\My
Documents\book1 ?

"Douglas J. Steele" wrote:

> You're not supplying a file name to which to export. You also don't need the
> commas at the end.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Ann B" <AnnB@discussions.microsoft.com> wrote in message
> news:7A26E4A1-B323-4424-A8BC-5930A3A02E6F@microsoft.com...
> >I am new to VBA programming. I think I am doing this right but keep
> >getting
> > the below error when typing in my TransferSpreadsheet Command. I am trying
> > to
> > write code so my query after being opened and run via form exports the
> > results into Excel. My queries name is Subcycle Data and I want it to
> > have a
> > field heading row. Can anyone see where I am going wrong?
> >
> > Compile Error- Expected: Expression
> >
> > DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"Subcycle
> > Data",,True,,,
>
>
>

== 5 of 7 ==
Date: Tues, Nov 13 2007 9:55 am
From: Ann B

How do I get it to open up and show me the excel file when it is finished and
not show me the results in Access? Thanks for everyones help. This is the
finishing touches on a long project and I am no good at this VBA stuff.

"fredg" wrote:

> On Tue, 13 Nov 2007 09:08:00 -0800, Ann B wrote:
>
> > I am new to VBA programming. I think I am doing this right but keep getting
> > the below error when typing in my TransferSpreadsheet Command. I am trying to
> > write code so my query after being opened and run via form exports the
> > results into Excel. My queries name is Subcycle Data and I want it to have a
> > field heading row. Can anyone see where I am going wrong?
> >
> > Compile Error- Expected: Expression
> >
> > DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"Subcycle
> > Data",,True,,,
>
>
> 1) You haven't told Access into what spreadsheet to export the data.
>
> 2) If you have no arguments to use after your last argument (i.e.
> True,,,), you do not need to add commas.
>
> DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Subcycle
> Data", "c:\MyFolderName\SpreadsheetName.xls", True
>
> The above will transfer your query data into a worksheet named
> "Subcycle Data" in a spreadsheet named "SpreadsheetName.xls" in the
> folder "MyFolderName".
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
>

== 6 of 7 ==
Date: Tues, Nov 13 2007 10:31 am
From: "Douglas J. Steele"

Remember that you need to be able to find the workbook again, so it's rather
important that you know where it is!

I suppose you could use "C:\Documents and Settings\All Users\My
Documents\book1.xls", or you can grab the code from

http://www.mvps.org/access/api/api0008.htm at "The Access Web" and use
"C:\Documents and Settings\" & fOSUserName & "\My Documents\book1.xls" or,
even better, grab the code from http://www.mvps.org/access/api/api0054.htm

and use fGetSpecialFolderLocation(CSIDL_PERSONAL) & "\book1.xls"

If you need to know whether or not the file already exists, you can use the
Dir function:

If Len(Dir(strFileLocation)) > 0 Then
' The file exists. Prompt the user to delete.
If MsgBox(strFileLocation & " already exists." & _
"Do you want to delete it?", vbYesNo + vbQuestion) = vbYes Then
Kill strFileLocation
End If
End If

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

"Ann B" <AnnB@discussions.microsoft.com> wrote in message
news:F5DE56AA-BC90-4054-91D9-691F50B48E00@microsoft.com...
> It says that is optional, but I can insert something in there. What if I
> just want it to create a new excel file? Should I put something in like
> C:\Documents and Settings\What if I want a generic user name?\My
> Documents\book1 ?
>
> "Douglas J. Steele" wrote:
>
>> You're not supplying a file name to which to export. You also don't need
>> the
>> commas at the end.
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Ann B" <AnnB@discussions.microsoft.com> wrote in message
>> news:7A26E4A1-B323-4424-A8BC-5930A3A02E6F@microsoft.com...
>> >I am new to VBA programming. I think I am doing this right but keep
>> >getting
>> > the below error when typing in my TransferSpreadsheet Command. I am
>> > trying
>> > to
>> > write code so my query after being opened and run via form exports the
>> > results into Excel. My queries name is Subcycle Data and I want it to
>> > have a
>> > field heading row. Can anyone see where I am going wrong?
>> >
>> > Compile Error- Expected: Expression
>> >
>> > DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"Subcycle
>> > Data",,True,,,
>>
>>
>>

== 7 of 7 ==
Date: Tues, Nov 13 2007 12:03 pm
From: fredg

On Tue, 13 Nov 2007 09:55:00 -0800, Ann B wrote:

> How do I get it to open up and show me the excel file when it is finished and
> not show me the results in Access? Thanks for everyones help. This is the
> finishing touches on a long project and I am no good at this VBA stuff.
>
> "fredg" wrote:
>
>> On Tue, 13 Nov 2007 09:08:00 -0800, Ann B wrote:
>>
>>> I am new to VBA programming. I think I am doing this right but keep getting
>>> the below error when typing in my TransferSpreadsheet Command. I am trying to
>>> write code so my query after being opened and run via form exports the
>>> results into Excel. My queries name is Subcycle Data and I want it to have a
>>> field heading row. Can anyone see where I am going wrong?
>>>
>>> Compile Error- Expected: Expression
>>>
>>> DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"Subcycle
>>> Data",,True,,,
>>
>> 1) You haven't told Access into what spreadsheet to export the data.
>>
>> 2) If you have no arguments to use after your last argument (i.e.
>> True,,,), you do not need to add commas.
>>
>> DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Subcycle
>> Data", "c:\MyFolderName\SpreadsheetName.xls", True
>>
>> The above will transfer your query data into a worksheet named
>> "Subcycle Data" in a spreadsheet named "SpreadsheetName.xls" in the
>> folder "MyFolderName".
>> --
>> Fred
>> Please respond only to this newsgroup.
>> I do not reply to personal e-mail
>>

In Access, don't open the query at all. The TransferSpreadsheet will,
behind the scenes, run and transfer the data. You won't see it.

To then open the spreadsheet (after transferring the data) add one
line of code:
Application.FollowHyperlink "c:\MyFolderName\SpreadsheetName.xls"

Of course, change the path to whatever your actual spreadsheet path
is.

Then click on the worksheet, if there is more than one in the
workbook.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

==============================================================================
TOPIC: Variables
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/193e1cc61b469f47?hl=en
==============================================================================

== 1 of 5 ==
Date: Tues, Nov 13 2007 9:18 am
From: Del

To refer to the value of a control on a form that does not have the focus I
can use the following code: Me.ControlOnFormWithFocus =
Forms!frmPull!txtControl1

How can I use a variable in this line of code in place of txtControl1? I've
tried Forms!frmPull(strLotControl) but Access tells me that it can not find
the field strLotControl.
--
Thank you,
Del

== 2 of 5 ==
Date: Tues, Nov 13 2007 9:35 am
From: "Stuart McCall"

"Del" <Del@discussions.microsoft.com> wrote in message
news:36E0B7F6-F89C-4391-BC47-D858BCFAFFBE@microsoft.com...
> To refer to the value of a control on a form that does not have the focus
> I
> can use the following code: Me.ControlOnFormWithFocus =
> Forms!frmPull!txtControl1
>
> How can I use a variable in this line of code in place of txtControl1?
> I've
> tried Forms!frmPull(strLotControl) but Access tells me that it can not
> find
> the field strLotControl.
> --
> Thank you,
> Del

Access is telling you it can't find the field, which means it's searching
the form's field collection (by default), so tell it to explicitly search
the controls collection instead:

Forms!frmPull.Controls(strLotControl)

== 3 of 5 ==
Date: Tues, Nov 13 2007 9:45 am
From: Klatuu

The syntax is simple enough:

Me.ControlOnFormWithFocus = strLotControl

But the issue here may be variable scoping. There are basically 3 levels of
variable scoping.
The highest level is Gloabal. It has to be Dimmed in a standard module and
is visible anywhere in the application. Now, in reality, avoid Global
varialbes. They have a problem in that an unhandled error will reset the
value of all global variables.

The Next is a module level variable. It is dimmed at the top of the module.
Any code in the module can see it. This includes standard, form, and report
modules.

The last is the local variable. It is dimmed in a procedure (function or
sub). Only the procedure in which it is declared can it be seen.

So, if the variable can't been seen in your code, it is out of scope. That
is, it is a local variable to another procedure. Two solutions are
available. Move the declaration to the module level, or make the procedure
in which you are trying to use it a function and pass the variable as an
argument. This, of course, will not work if the procedure is an event
procedure.
--
Dave Hargis, Microsoft Access MVP

"Del" wrote:

> To refer to the value of a control on a form that does not have the focus I
> can use the following code: Me.ControlOnFormWithFocus =
> Forms!frmPull!txtControl1
>
> How can I use a variable in this line of code in place of txtControl1? I've
> tried Forms!frmPull(strLotControl) but Access tells me that it can not find
> the field strLotControl.
> --
> Thank you,
> Del

== 4 of 5 ==
Date: Tues, Nov 13 2007 10:46 am
From: Del

This gives me the same message. I was not very specific when I wrote the
message before. It says " MS Access can't find the field 'txtLot1' referred
to in your expression."
--
Thank you,
Del

"Stuart McCall" wrote:

> "Del" <Del@discussions.microsoft.com> wrote in message
> news:36E0B7F6-F89C-4391-BC47-D858BCFAFFBE@microsoft.com...
> > To refer to the value of a control on a form that does not have the focus
> > I
> > can use the following code: Me.ControlOnFormWithFocus =
> > Forms!frmPull!txtControl1
> >
> > How can I use a variable in this line of code in place of txtControl1?
> > I've
> > tried Forms!frmPull(strLotControl) but Access tells me that it can not
> > find
> > the field strLotControl.
> > --
> > Thank you,
> > Del
>
> Access is telling you it can't find the field, which means it's searching
> the form's field collection (by default), so tell it to explicitly search
> the controls collection instead:
>
> Forms!frmPull.Controls(strLotControl)
>
>
>
>

== 5 of 5 ==
Date: Tues, Nov 13 2007 10:51 am
From: Del

My apologies for not being more specific. The message says " MS Access can't
find the field 'txtLot1' referred to in your expression." So Access
recognizes the variable.

I have also tried, without success: Forms!frmPull.Controls(strLotControl) =
... as recommended by Stuart McCall
--
Thank you,
Del

"Klatuu" wrote:

> The syntax is simple enough:
>
> Me.ControlOnFormWithFocus = strLotControl
>
> But the issue here may be variable scoping. There are basically 3 levels of
> variable scoping.
> The highest level is Gloabal. It has to be Dimmed in a standard module and
> is visible anywhere in the application. Now, in reality, avoid Global
> varialbes. They have a problem in that an unhandled error will reset the
> value of all global variables.
>
> The Next is a module level variable. It is dimmed at the top of the module.
> Any code in the module can see it. This includes standard, form, and report
> modules.
>
> The last is the local variable. It is dimmed in a procedure (function or
> sub). Only the procedure in which it is declared can it be seen.
>
> So, if the variable can't been seen in your code, it is out of scope. That
> is, it is a local variable to another procedure. Two solutions are
> available. Move the declaration to the module level, or make the procedure
> in which you are trying to use it a function and pass the variable as an
> argument. This, of course, will not work if the procedure is an event
> procedure.
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Del" wrote:
>
> > To refer to the value of a control on a form that does not have the focus I
> > can use the following code: Me.ControlOnFormWithFocus =
> > Forms!frmPull!txtControl1
> >
> > How can I use a variable in this line of code in place of txtControl1? I've
> > tried Forms!frmPull(strLotControl) but Access tells me that it can not find
> > the field strLotControl.
> > --
> > Thank you,
> > Del

==============================================================================
TOPIC: Adding Records
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e7ad8e5914a3034a?hl=en
==============================================================================

== 1 of 2 ==
Date: Tues, Nov 13 2007 9:19 am
From: "John Spencer"

Could you explain a little more abut what you want to do?

A table that isn't opened could mean a lot of things.
The database isn't open
The table is in another database and is not a linked table
You have a form open and it doesn't have the table in its record source
You have a query open and the table is not included in the tables used by
the query
...

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

"denise" <denise@discussions.microsoft.com> wrote in message
news:0E6DA2FC-7FB6-499E-8FF1-B5D3FED9318C@microsoft.com...
> How can I add records to a table that isn't opened?

== 2 of 2 ==
Date: Tues, Nov 13 2007 10:22 am
From: denise

The table is in the same database that I have a form opened in. No the form
is not based on the table.

I know that there has to be a way in VB to open the table and add a record,
I just don't know how to do it.

"John Spencer" wrote:

> Could you explain a little more abut what you want to do?
>
> A table that isn't opened could mean a lot of things.
> The database isn't open
> The table is in another database and is not a linked table
> You have a form open and it doesn't have the table in its record source
> You have a query open and the table is not included in the tables used by
> the query
> ....
>
>
>
> --
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
>
> "denise" <denise@discussions.microsoft.com> wrote in message
> news:0E6DA2FC-7FB6-499E-8FF1-B5D3FED9318C@microsoft.com...
> > How can I add records to a table that isn't opened?
>
>
>

==============================================================================
TOPIC: Calling Module function in query
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/5cdae39d26228303?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 13 2007 9:57 am
From: Klatuu

Brian, I believe the way you have it coded it will choke on a circular
reference.
It is the Slab as Slab part:
SELECT Incentive.SlbValue, Slab([SlbValue]) As Slab from Incentive

It may be necessary to use a different name:
SELECT Incentive.SlbValue, Slab([SlbValue]) As SlabType from Incentive
--
Dave Hargis, Microsoft Access MVP

"Brian" wrote:

> Try this:
>
> Make sure your Function is Public, like this:
>
> Public Function Slab(SlabVal As Integer) As Integer
> If SlabVal > 0 And SlabVal <= 20 Then
> Slab = 1
> ElseIf SlabVal > 20 And SlabVal <= 50 Then
> Slab = 2
> ElseIf SlabVal > 50 And SlabVal <= 100 Then
> Slab = 3
> ElseIf SlabVal > 100 Then
> Slab = 4
> End If
> End Function
>
> Now, select like this:
>
> SELECT Incentive.SlbValue, Slab([SlbValue]) As Slab from Incentive
>
> In the query builder, the Slab field will look like this:
>
> Slab:Slab([SlbValue])
>
> In addition, if SlbValue could ever be null, the function variables will
> need to be Variant types instead of Integers, and you will need to handle the
> null value somewhere in the If...ElseIf statement.
>
> "Hitesh Chauhan" wrote:
>
> > Hi,
> > I am having a user defined function in module. i want to call in my select
> > statement. is it possible. if yes How?
> >
> > Function Slab(SlabVal As Integer)
> > If SlabVal > 0 And SlabVal <= 20 Then
> > Slab = 1
> > ElseIf SlabVal > 20 And SlabVal <= 50 Then
> > Slab = 2
> > ElseIf SlabVal > 50 And SlabVal <= 100 Then
> > Slab = 3
> > ElseIf SlabVal > 100 Then
> > Slab = 4
> > End If
> > End Function
> >
> > the above function i want to call in select statement like
> >
> > select SlbValue,slab(slbvalue) from Incentive
> >
> >
> > regards,
> > hiteshc

==============================================================================
TOPIC: How to list existing variables ?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/d7e35b8114b19a03?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 13 2007 11:00 am
From: Marshall Barton

Nicodemus wrote:
>I wish to list the already defined Public Variables from VBA, just like I
>list my existing tables. Is there a kind of "VariableDefs", like "TableDefs"
>I could use ?

Here's the outline of a procedure that lists the plublic
decalarations in standard modules:

Public Sub ListGlobals()
Dim db As DAO.Database
Dim doc As Document
Dim mdl As Module
Dim LinePos As Long
Dim CodeLine As String
Dim StmtWordEnd As Long

Set db = CurrentDb()

For Each doc In db.Containers("Modules").Documents
DoCmd.OpenModule doc.Name
With Modules(doc.Name)
If .Type = acStandardModule Then

For LinePos = 1 To .CountOfDeclarationLines
CodeLine = Trim(.Lines(LinePos, 1))
If Len(CodeLine) > 4 Then
StmtWordEnd = InStr(CodeLine, " ")
If StmtWordEnd > 3 Then
Select Case Left(CodeLine,
StmtWordEnd - 1)
Case "Public", "Dim", "Global",
"Const"
Debug.Print .Name; Spc(5);
CodeLine
End Select
End If
End If
Next LinePos

End If
End With
'Don't close this module
If doc.Name <> "CountLinesOfCode" _
Then DoCmd.Close acModule, doc.Name, acSaveNo
Next doc
Set db = Nothing
End Sub

--
Marsh
MVP [MS Access]

==============================================================================
TOPIC: Check SQL Server Table Permissions
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e82f6b4820a46875?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 13 2007 11:32 am
From: BillyRogers

I have an Access program that users outside of our office are using. The
program calls stored procedures (using ADO) in SQL Server from Access. Last
month the program didn't work and I finally tracked it down to the
permissions having been dropped for the group from one of the tables. Our
IT dept never explained to me how this happened, they just reset the
permissions. I'm getting the same problem this month. The stored
procedure uses a lot of tables.

Is there some way to find out which table it is that the permissions are
missing from? Or is there a way to check the persmissions before calling
the stored procedure?

I'm just trying to make this easier to troubleshoot if it happens again.

Thanks,
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

==============================================================================
TOPIC: Desktop shortcut for secured database
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/fbc439df5655e8e4?hl=en
==============================================================================

== 1 of 1 ==
Date: Tues, Nov 13 2007 12:01 pm
From: Mike Binger

My desktop has several shortcuts for secured Access databases. The target
for one of them is: "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"
"N:\Common\ACCT\branch analysis.mdb" /wrkgrp"N:\COMMON\FED.MDW"

That is for Access 2000. I would like to use a universal notation for the
target and the Start In to run the Access whatever version is present.

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

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

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

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

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

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

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

Excel for Life

Selasa, 2007 November 13

25 new messages in 12 topics - digest

0 komentar:

Arsip Blog

Blogeroll

Mengenai Saya