Full Version: Command button to open the same record on another form, but not to conflict code
UtterAccess Forums > Microsoft® Access > Access Forms
I'm so close to report creation and database launch (and a big fat raise) I can taste it, but the fiddle factor on my forms is setting in. I think this is the last thing I want to do before I worry about pretty pictures and comical msgboxes for my boss to see when he tries to log into the database.
I have a Main Form. It has a tabbed subform, and one of the tabs is a continuous form cheated out to look like a datasheet (kudos to theDBGuy for that, sorry if I got your name wrong, he's the person with the Shrek avatar). This is a continuous form of facilities. At the end of each continuous record (on each line, essentially) is a button. I was able to hijack more of DBGuy's code for use in a similar subform in another main form to pull up the record that specific 'line' the clicked button was on (this was for surgeon details):
It works great. The only problem is, for this Facilities Form I want this new button to open, it already has an OnLoad event as the result of a NotInList event from another form, and this was with a ton of help from Jack:

If Not IsNull(OpenArgs) Then
Me.[NameField] = OpenArgs

So, if I paste DB Guy's code in there, then there's a possibility the NotInList scenario will play hob by grabbing the text entered in the combobox and trying to equate it to an ID number, with much wailing and gnashing of teeth. And msgboxes.
Is there a subroutine I can insert to tell it 'hey, if OpenArgs is text, then insert it into Me.[NameField], and if it's an integer, then do the FindFirst in the ID field'?
Opromise I'm getting better with code, but this is beyond me and my lousy dummies' guide :(
HAs to why it has to be this way: The subforms involved are used as 'snapshots'. I (being the sole data enterer, my boss just wants reports) may want to see more involved details, so I click the '...' button, up pops a dialog form showing those details, and I close it and go on my merry way. I'd like to have that for the Facilities, but I don't want to sacrifice that delicious NotInList event code either. But if I have to choose, I have to choose.
Jack Cowley
A couple of thoughts come to mind.. If the Not In List event is from a form that is not opened at the same time your other form is open you can check to see if that form is open and if it is then you know what value is stored in OpenArgs. Take a look here for how to use the IsLoaded() function.
If IsLoaded("MyForm") Then
Code to use OpenArgs for Not In List
Code to use OpenArgs for finding a record
End If

Another thought is to add an "=" sign in your:
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , "=" & Me.TableID
Then in the On Load event...
If (Left(OpenArgs, 1) = "=") Then
   Dim rs As Object
   Set rs = Me.RecordsetClone
   rs.FindFirst "[TableID] = " & Eval(Mid(OpenArgs,2))
   ....rest of code
   code for Not In List event...
End if

ALL OF THIS IS PURE AIR CODE!!! Nothing has been tested or even scrutinized so beware!!! This could ruin your chance for that fat raise...
I'm going to work these, this is an awesome starting point. It's ok, I work with backups, so if I have to do away with a pretty little button, no one's the wiser and I'll be rolling in dough. I'll report back shortly, thanks for all of this advice Jack!
The second set of code is the one I'm trying to go for here. My thought is 'what if I have that form open and I load this other form? I'll break something', so I'm really trying to get it to analyze the OpenArgs on load.
ight now, when it fires, it's throwing the ID number into the hospital name field, so I'm reckoning it's not recognizing special characters. I think this because I tried some alternate inserted code:

If (Left(OpenArgs, 1) = "=") Then  
Dim VarID as Integer
Dim rs As Object  
VarID = Eval(Mid(OpenArgs,2))
Set rs = Me.RecordsetClone   rs.FindFirst "[TableID] = " & VarID
'rest of code

And it's still throwing that ID number into the name field.
I've tried using an underscore instead of an equals sign (nothing in the Help said I couldn't), and it didn't insert the ID number into the name field, but it didn't go to the record I clicked for either.
This might sound like a stupid workaround, but I think what I might end up doing is having 2 copies of the form with slightly different names, each having its own OnLoad event. I don't know if that's bad practice (and everyone will jump up and down and scream), but it would absolutely work.
Jack Cowley
I did some testing and the code that I 'borrowed' that uses the equals sign does NOT work!! Try using this in the OpenArgs:
oCmd.OpenForm "FormName", , , , , , "X" & Me.TableID
Then in the On Load event:
If (Left(OpenArgs, 1) = "X") Then
Sorry for the bad code and I hope this solves your problem and you get that humongous raise...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.