Change SourceObject using a wildcard and contents of a control

[dcdimon]

Greetings,



I am using buttons to change the sourceobject for a form. I'm having problem on setting up a script to call a form using a wildcard as part of the form name.

We have a number of forms that end in CILF with something unique in front of that text. After the CILF is a string of numbers [EquipmentNumber] that appears in a control on the main form. I did a test script to pull in a form using the specific name and the EqupmentNumber. However, my problem lies in just looking for the CILF part of the name along with the EquipmentNumber.

Here's my test script:

Me.BlankPageF.SourceObject = "Pallitizer 9 CILf " & Forms![Data Management Form]!EquipmentNumber

I'm not sure how to parse the script so it's only looking for that CILF string. We have dozens of machines and I dont' want to have to create a script for each one, nor do I want to rename the forms as the forms are referenced in other areas. I know this has to be possible, but I just can't find an example online that meets my needs. However, I've learnd a lot about queries.

Thanks, in advance for any pointers you can give me.

DD

[Dal Jeanis]

1) When you say "script", are you talking about VBA or macros or what?
2) If you want just CILF plus the Equipment number, then why not this
Me.BlankPageF.SourceObject = "CILf " & Forms![Data Management Form]!EquipmentNumber

3) Go to this page http://www.techonthenet.com/access/functions/index.php and review the string functions. The two that will help you parse a string are Instr and Mid. You may also need Left or Right in some instances.

You're probably looking for something like this -
strLeftOfCILF = Left(Me.BlankPageF.SourceObject,Instr(, Me.BlankPageF.SourceObject,"CILF")+3)
strRightOfCILF = Mid(Me.BlankPageF.SourceObject,Instr(, Me.BlankPageF.SourceObject,"CILF"))

[dcdimon]

I'm talking VBA - I don't really think of macros as scripting.

The option in line 2 would work if CILF was the full name of the form along with the EquipmentNumber, but it's not.

Form names are structured with something in front of the CILF part and I'm adding the equipment number to that. I had thought about just removing the number from the first part - but that's how everyone is use to referring to the equipment. The longer number I'm adding is the asset number.

Palletizer9 CILF 3914
Palletizer4 CILF 14720
Palletizer5 CILF 108021

What I want to do is use the form with CILF in the name and the number from the EquipmentNumber control to change the SourceObject.

I'll take a look at the link and see if I can use (and understand) what it tells me.

DD

[Dal Jeanis]

Okay, give me an example of the name of the form you want, and the the names and values of the input fields you want to use to generate that name.

Are you saying that there is a data field that has CILF somewhere in the middle, and you want everything down to the CILF? Then use the format I wrote for strLeftofCILF and use the name of your source field.

[dcdimon]

I have a number of forms named as I stated above.

Palletizer9 CILF 3914
Palletizer4 CILF 14720
Palletizer5 CILF 108021

and so on.

The value I'm pulling from the form is the EquipmentNumber, which is the last string of numbers. So I can use that as part of the file name. So I have say 3914 and I need to put that together with something to get Palletizer9 CILF 3914 to become the SourceObject.

i.e. "Palletizer9 CILF" & EquipmentNumber

However, because the number after the palletizer is different for each piece of equipment I'd like to just search for the CILF part of the name with the EquipmentNumber value to determine what the SourceObject should be.

[Dal Jeanis]

Okay, so you are saying that the form for any given equipment will be "Palletizer% CILF 99999" where % is 1-9 and 99999 is the equipment number.

Okay, we can work with that. There's code various places that will give you all form names in the database. Let me look it up.

Try this -

SELECT [Name] 
FROM MSysObjects 
WHERE Type = -32768
AND [NAME] LIKE "Palletizer* CILF " & EquipmentNumber

or

strFormName = "" & DLookup("[Name]","[MSysObjects]","[Type] = -32768 AND [NAME] LIKE 'Palletizer% CILF " & EquipmentName & "'")

ref: http://bytes.com/topic/access/answer...forms-database
http://compgroups.net/comp.databases...tabase/1465876