Use unbound textbox to open form to correct record

[kaylachris]

Hopefully I explain my intentions clearly. I have a form named MainSwitch which contains a unbound textbox named searchSSN and a button named open_SMoverview. What I want to be able to do is type in a SSN into the textbox and then click the button which would open another form named SMoverview to the correct record for the SSN provided. Also, pressing the enter key to preform this operation is not ideal because I intend to use this same textbox with various other buttons for similar purposes (i.e. type in a SSN and pull up a report on an individual). I also thought about using a query to populate a listbox... but theres over 400 individuals you'd have to scroll through so it would be practical.

I realize by allowing users to enter search criteria is somewhat dangerous and will require some error checking and validation. However, I'm certain I can figure that portion out later down the line once I have the inital portion figured out. What I'm asking for is a good referance source to figure out how to right the VBA to make this work. Any help would be greatly appreciated. Lastly, I'm fairly new to Access but do have a lil bit of programming experiance and understand the basic concepts of how VBA should work... i just don't know the proper syntax.

forms:
MainSwitch(unbound form,custom switchboard)
-searchSSN(unbound textbox)
-open_SMoverview(button)

SMoverview(bound form to data_SM, shows all data for an individual)

tables:
data_SM

 

[John Big Booty]

Have a look at the DoCmd.OpenForm method and use the Where Condition to open the form filtered to the record that matches the SSN that has been input into your Text box. The code might look something like;

If IsNull(Me.YourTextBox) or Me.YourTextBox = "" Then[COLOR="Green"] 'Check that textbox is not Null and not a zero length string[/COLOR]
     MsgBox "Please enter an SSN"
     Me.YourTextBox.SetFocus
     Cancel = True
     Exit Sub
End If

If [URL="http://www.techonthenet.com/access/functions/domain/dcount.php"]Dcount[/URL]("SSN", "YourTableThatStoresSSN", "SSN = " & Me.YourTextBox) = 0 Then[COLOR="Green"] 'Check that the input SSN exists[/COLOR]
     MsgBox "Please enter a Valid SSN"
     Me.YourTextBox.SetFocus
     Cancel = True
     Exit Sub
End If

DoCmd.OpenForm "YourFormName", , ,"SSN = " & Me.YourTextBox

 

[John Big Booty]

Rather than having an unbound text box, why not use an unbound Combo Box that that shows the available SSN's this will simplify things both for the user and for your coding, the code might then look something like;

If IsNull(Me.YourCombo)  Then [COLOR="Green"]'Check that a value has been seleceted from the list[/COLOR]
     MsgBox "Please selecet an SSN"
     Me.YourCombo.SetFocus
     Cancel = True
     Exit Sub
End If

DoCmd.OpenForm "YourFormName", , ,"SSN = " & Me.YourCombo

 

[kaylachris]

Creating the button and text/combo box isn't where I'm having the issue. The problem I'm facing is passing the value from the first form to the second. Though I do like your idea of using a combo box rather than textbox.

Right now when I select the record in the combobox and press the command button the form opens. However, instead of opening to the correct record and displaying the data it opens as a blank form. I've attached a slimed down version of my DB for review. Thanks in advance

 

[John Big Booty]

Any chance you can save that DB back to an '03 version as I wont have access to '07 until this evening, at which point I wont have the time to have a look at what you've done.

 

[John Big Booty]

Never mind. I had some unexpected free time on the '07 machine

Now because your SSN is a text field rather than a numeric field as I had assumed, I've made a slight change to the code;

If IsNull(Me.YourCombo)  Then 'Check that a value has been seleceted from the list
     MsgBox "Please selecet an SSN"
     Me.YourCombo.SetFocus
     Cancel = True
     Exit Sub
End If

DoCmd.OpenForm "YourFormName", , ,"SSN = [COLOR="Red"]'[/COLOR]" & Me.YourCombo [COLOR="Red"]& "'"[/COLOR]

As we are matching text the text must be contained within quotation marks, hence the highlighted additions.

Additionally I note that you have used table level lookups, I would suggest you read this link and this link as to why this is a bad idea, you may then want to rethink your data structure. If you read up on Data Normalisation this task will become a whole lot clearer.

Finally you might also want to consider implementing a naming protocol, something along the lines of TBL_TableName, FRM_FormName, QRY_QueryName etc. is a good way to go. It will make it a lot easier to understand the nature of DB objects you are referring to once you really start getting into writing code.

 

[kaylachris]

Works fantastic! Thanks for all the great info. I will definitly have to rethink the lookup methods that I've used and pay closer attention to ensuring the DB is optimized for performance. I have also normalized my DB... I had done so previously within the tables themselves but not thought about doing the same for forms, reports, etc... but after playin around with building a bit more code I've come to find that it is absolutly necessary expecially when you have forms that are similarly named to the tables they are built on. Once again thx for all the valuable information.