Re: Difficulties programming a command button

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Chris,

if the sheetname has a space in it, you need to put single quotes around it

mSheetname = "'sheetname with space'"

you can concatenate those in:

'~~~~~~~~~~~~
mSheetname = "'" & _
& me.subfrm.form.txtTSOfficer _
& "_" & me.txtEquipment _
& "'"
'~~~~~~~~~~~~


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




CB wrote:
Hi Crystal,

Well, I gave it a shot. Twice when I tried to compile Access hung and I just about died! I'm not sure exactly what I did differently the third time but things behaved that time.

Anyway, the dialogue box behaves properly (as in the message is okay and corresponds to the work*** names, and selecting 'Yes' opens the Excel file. However, when Excel opens I get an error that "Reference is not valid." Consequently, the file opens to the last work*** I was on rather than the referenced work***. I doubled checked the *** names to what I had in the field names and all looks okay. I added the underscore that you had in the code but no difference. I tried commenting out the cell reference but that didn't help either.

Chris

"strive4peace" wrote:

Hi Chris,

you're welcome ;)

try this:

'~~~~~~~~~~~~~~~~
dim mFilename as string _
, mSheetname as string

'make sure data that you need is filled out
if IsNull(me.subfrm.form.txtTSOfficer) then
msgbox "You have not specified a tech",,"Cannot open Excel"
exit sub
end if

if IsNull(me.txtEquipment) then
msgbox "You have not specified equipment",,"Cannot open Excel"
exit sub
end if

mFilename = "\\ISO\Inspection\Depot\" _
& "Digital MultiMeter Verification Records\" _
& "DMM_Verifications.xls"

mSheetname = me.subfrm.form.txtTSOfficer _
& "_" & me.txtEquipment

if MsgBox( _
"Sheetname is: " & mSheetname _
& vbCrLf & vbCrLf & "Click Yes to Open" _
, vbYesNo, "Open ***?") _
= vbYes then

Application.FollowHyperlink _
mFilename , mSheetName & "!A1"

end if

'~~~~~~~~~~~~~~~~

not sure if I got the sheetname properly constructed... probably the txtTSOfficer needs to get a column from the combo ... but this is a start anyway :)

It would be better to use the initials in the sheetname in case you have equipment that has a long number so the length of the sheetname doesn't go over the limit

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should always compile before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




CB wrote:
Hi Crystal,

The name for my subform is simply “subfrm”. Creative, huh?! 

The name for the control for the equipment is “txtEquipment”. This control is a textbox found on both the main form (named “Enter Primary Equipment Calibration History”) and the subform.

The name for the control for the techs’ surnames is “txtTSOfficer”. FWIW, this control is on the sub-form and is a combo box that queries the “Inspectors” table and returns just their initials (the table also contains their full name).

Just so you’re clear, I’ll give an example…

Let’s say John Smith has a Fluke 87-III multimeter. In the “Inspectors” table, he is recorded by both “John Smith” and “JS”. He shows up in the underlying records by his initials. The equipment is recorded in the records as “Fluke 87-III Multimeter.” The spread*** that I need to launch would be named “Smith 87 III”.

If these different iterations are problematic, it would be easy enough to quickly modify either field names/data in the database or the names of the sheets as I only have 15 records thus far.

Thanks so much for all your help! I really appreciate it.

Chris


"strive4peace" wrote:

Hi Chris,

there is no reason to add a work*** name if you can construct it from your data...

here is the statement you will need:

Application.FollowHyperlink "C:\path\filename.xls", "SheetName!Address"

so, in code we can do this:

'~~~~~~~~~~~~~~~~
dim mFilename as string _
, mSheetname as string

mFilename = "\\ISO\Inspection\Depot\Digital MultiMeter Verification Records\DMM_Verifications.xls"

mSheetname = "we need to work on this"

Application.FollowHyperlink _
mFilename , mSheetName & "!A1"
'~~~~~~~~~~~~~~~~

so, now we just have to write the code to calculate the sheetname by referencing the controls that contain the following fields on your form/subforms...

TechSurname
EquipmentModel

where are they located in relationship to where the code is going?

1. subform controlname
2. controlname


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




CB wrote:
Hi again,

My database field names are per your "i.e." below; however, the Excel worksheets are labelled differently. This was done by someone else to make it easier to ensure the techs to open the appropriate *** when they enter their data. The general format for the *** labelling is "TechSurname EquipmentModel".

When I was first trying to figure this out, I was wondering if would be helpful to add a field "WorksheetName" to the equipment table. Would this help??

Chris

"strive4peace" wrote:

Hi Chris,

how do the *** names correlate to your data?
ie:
1. EquipmentBrand_fieldname
2. _
3. SerialNumber_fieldname

if this is right, what are the respective fieldnames? If not, what is the correct rule?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




CB wrote:
Hi Crystal,

The filename is DMM_Verifications.xls and the path is \\ISO\Inspection\Depot\Digital MultiMeter Verification Records.

Some *** names include Jones_179, Smith_87_III, Hamil_87_III

Regards,
Chris

"strive4peace" wrote:

Hi Chris,

you're welcome ;)

what is the file name and path?

what are a few of your *** names?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




CB wrote:
Hi Crystal,

There is only one file. I need to reference different sheets within the one file depending on what is displayed for "Equipment" and "Serial Number" in the current record (result of a query).

Thanks for the link. I'll check it out.

Chris

"strive4peace" wrote:

Hi Chris,

are the Excel workbooks all in the same location or will the user need a File Open dialog box to browse to a file?

"Would I need to use a Select Case"

No, it sounds like you can get the filename and sheetname from your data -- just wondering about the path... and you should read this since you will need to use VBA:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

we can help you with the code

Warm Regards,
Crystal


*
(: have an awesome day :)
*




CB wrote:
Hi all,

I’m doing some database modification after being away from Access (and the calibration database I created) for over six years. I’m looking at what I’ve done and asking myself “How on earth did I do that???” Needless to say, I’m forgetting an awful lot so please bear with me.

I’m trying to program a command button such that it will open an Excel workbook to a specific spread*** depending on the equipment and serial number displayed on the form. (This is only a temporary work around until I
.

Quantcast