Listbox selection changes data in field

I have a simple form with a listbox(SelectScreen) that shows all names and
social security numbers in the database. I use this list to select the
correct name and open another form(MainScreen) for data input. Everything
works great except one thing. Right now I only have 3 people in the database
for testing purposes and here's what my listbox looks like:

111111111 John Doe
222222222 Jane Smith
333333333 Betty Sue

Now when I select "Betty Sue" the form opens properly and I can edit and Save.
But, when I open the listbox again for another selection the first record's
SSN changed to the last record that was accessed as such.

333333333 John Doe
222222222 Jane Smith
333333333 Betty Sue

Private Sub SelectIndividual_Click()
If Not IsNull(Me!SSN) Then
DoCmd.OpenForm "MainScreen", , , "SSN = """ & Me!SSN & """"
DoCmd.Close acForm, "SelectScreen"
End If
End Sub

Any ideas?
Bill

You need to use 2 list boxes: one to store the *current* person, and another
one to navigate to a different record.

The navigation one must be unbound, i.e. there must not be anything in its
Control Source property.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bill H via AccessMonster.com" wrote in message
...
I have a simple form with a listbox(SelectScreen) that shows all names and
social security numbers in the database. I use this list to select the
correct name and open another form(MainScreen) for data input. Everything
works great except one thing. Right now I only have 3 people in the
database
for testing purposes and here's what my listbox looks like:

111111111 John Doe
222222222 Jane Smith
333333333 Betty Sue

Now when I select "Betty Sue" the form opens properly and I can edit and
Save.
But, when I open the listbox again for another selection the first
record's
SSN changed to the last record that was accessed as such.

333333333 John Doe
222222222 Jane Smith
333333333 Betty Sue

Private Sub SelectIndividual_Click()
If Not IsNull(Me!SSN) Then
DoCmd.OpenForm "MainScreen", , , "SSN = """ & Me!SSN & """"
DoCmd.Close acForm, "SelectScreen"
End If
End Sub

Any ideas?
Bill

Thanks for your help on the code. I actually have it working fine now using
this, but I need one more where condition added to the MainScreen command.
MainScreen must also contain the condition "DutyClass = A" else the selection
opens the AltScreen. I have worked many hours trying to get it into the code
the right way AND work. More help please.

Bill

Private Sub Select_Click()
If Not IsNull(Me!lstSelect) Then
DoCmd.OpenForm "MainScreen", , , "SSN = """ & Me!lstSelect & """"
DoCmd.Close acForm, "SelectScreen"

Else: DoCmd.OpenForm "AltScreen", , , "SSN = """ & Me!lstSelect & """"
End If
End Sub


--
Message posted via http://www.accessmonster.com

If you haven't selected anything from the listbox (i.e. if Me!lstSelect is
in fact Null), you won't have a value to use as an opening argument when
opening AltScreen, so that all you need is DoCmd.OpenForm "AltScreen"

Additionally, I'm not sure that you can use the colon after the Else to put
everything on one line in this case (although I could be wrong about this):

Private Sub Select_Click()
If Not IsNull(Me!lstSelect) Then
DoCmd.OpenForm "MainScreen", , , "SSN = "" & Me!lstSelect & """"
DoCmd.Close acForm, "SelectScreen"
Else
DoCmd.OpenForm "AltScreen"
End If
End Sub

However, that doesn't do anything with your DutyClass requirement. Where
does DutyClass = A come from? Are you saying that you need to use it as a
condition in the Where clause? If so, try:

Private Sub Select_Click()
If Not IsNull(Me!lstSelect) Then
DoCmd.OpenForm "MainScreen", , , "SSN = """ & Me!lstSelect & """ AND
DutyClass = ""A"""
DoCmd.Close acForm, "SelectScreen"
Else
DoCmd.OpenForm "AltScreen"
End If
End Sub

If there's a control named lstDutyClass that lets the user select from a
bunch of DutyClass values, and you only want to open MainScreen if they've
selected DutyClass = A, try:

Private Sub Select_Click()
If Not IsNull(Me!lstDutyClass) Then
DoCmd.OpenForm "AltScreen"
Else
If Not IsNull(Me!lstSelect) Then
DoCmd.OpenForm "MainScreen", , , "SSN = """ & Me!lstSelect & """"
DoCmd.Close acForm, "SelectScreen"
End If
End If
End Sub


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



"Bill H via AccessMonster.com" wrote in message
...
Thanks for your help on the code. I actually have it working fine now
using
this, but I need one more where condition added to the MainScreen command.
MainScreen must also contain the condition "DutyClass = A" else the
selection
opens the AltScreen. I have worked many hours trying to get it into the
code
the right way AND work. More help please.

Bill

Private Sub Select_Click()
If Not IsNull(Me!lstSelect) Then
DoCmd.OpenForm "MainScreen", , , "SSN = """ & Me!lstSelect & """"
DoCmd.Close acForm, "SelectScreen"

Else: DoCmd.OpenForm "AltScreen", , , "SSN = """ & Me!lstSelect &
""""
End If
End Sub


--
Message posted via http://www.accessmonster.com

Thanks Douglas,

OK, almost there. Here's what I've got now. With what you gave me I am able
to load the MainScreen Form with the selection I made if DutyClass = "A". It
shows the correct records, all good.

However, if I end the Else with "AltScreen" I get the AltScreen Form but no
records. So, I added the , , , "SSN = "" & Me!lstSelect & """"" and still
get nothing but a blank AltScreen.

Should the Else contain AND DutyClass = "O" or "C" or "Y". I can't find
examples on how to write the multiple ORs.


Private Sub Select_Click()
If Not IsNull(Me!lstSelect) Then
DoCmd.OpenForm "MainScreen", , , "SSN = """ & Me!lstSelect & """ AND
DutyClass = ""A"""
DoCmd.Close acForm, "SelectScreen"

Else
DoCmd.OpenForm "AltScreen", , , "SSN = "" & Me!lstSelect & """""

End If

End Sub


--
Message posted via http://www.accessmonster.com

As I believe I said in my response, since Me!lstSelect is Null, you're
effectively using:

DoCmd.OpenForm "AltScreen", , , "SSN = "" & """""

In other words, you're only going to show those records where SSN is a
zero-length string ("")

Perhaps what you want is

DoCmd.OpenForm "AltScreen", , , "DutyClass ""A"""

or

DoCmd.OpenForm "AltScreen", , , "DutyClass IN (""O"", ""C"", ""Y"")"""


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



"Bill H via AccessMonster.com" wrote in message
...
Thanks Douglas,

OK, almost there. Here's what I've got now. With what you gave me I am
able
to load the MainScreen Form with the selection I made if DutyClass = "A".
It
shows the correct records, all good.

However, if I end the Else with "AltScreen" I get the AltScreen Form but
no
records. So, I added the , , , "SSN = "" & Me!lstSelect & """"" and
still
get nothing but a blank AltScreen.

Should the Else contain AND DutyClass = "O" or "C" or "Y". I can't find
examples on how to write the multiple ORs.


Private Sub Select_Click()
If Not IsNull(Me!lstSelect) Then
DoCmd.OpenForm "MainScreen", , , "SSN = """ & Me!lstSelect & """
AND
DutyClass = ""A"""
DoCmd.Close acForm, "SelectScreen"

Else
DoCmd.OpenForm "AltScreen", , , "SSN = "" & Me!lstSelect & """""

End If

End Sub


--
Message posted via http://www.accessmonster.com

I visited your beer page. Pretty cool. I'm just an Army guy trying to make
this thing work. It still isn't working. I'm not a novice at this and
definately not an expert. Let me explain what IS happening though.

Right now the 2 forms are identical. When I open the AltScreen by itself
from the Database window, it shows information on the form, however it is the
first DutyClass=A guy in the db. When I open it using the coded button,
selecting a DutyClassA guy, the form remains empty. But, when I view the
properties and look at the filter is states SSN=" ", which is the correct
social for the selected DutyClassA guy, but then it says DutyClass="A"
which is not in the coding for the A people. Half is working and half is
not. Now, if I change the = to in the button properties filter box the
form fills out properly with the contents as it should be, but of course I
can't save the properties | data | filter source. Something is not working
right somewhere.

I tried all three variations of your code and I'm sure their good. They look
good and I understand them. Shouldn't I simply cut and paste and it work
properly? Could it be something else? I really appreciate you taking time
to help me out. I'm definately putting you on my credits list.

Bill


--
Message posted via http://www.accessmonster.com

Paste exactly what you're using now.

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



"Bill H via AccessMonster.com" wrote in message
...
I visited your beer page. Pretty cool. I'm just an Army guy trying to
make
this thing work. It still isn't working. I'm not a novice at this and
definately not an expert. Let me explain what IS happening though.

Right now the 2 forms are identical. When I open the AltScreen by itself
from the Database window, it shows information on the form, however it is
the
first DutyClass=A guy in the db. When I open it using the coded button,
selecting a DutyClassA guy, the form remains empty. But, when I view
the
properties and look at the filter is states SSN=" ", which is the correct
social for the selected DutyClassA guy, but then it says DutyClass="A"
which is not in the coding for the A people. Half is working and half
is
not. Now, if I change the = to in the button properties filter box the
form fills out properly with the contents as it should be, but of course I
can't save the properties | data | filter source. Something is not
working
right somewhere.

I tried all three variations of your code and I'm sure their good. They
look
good and I understand them. Shouldn't I simply cut and paste and it work
properly? Could it be something else? I really appreciate you taking
time
to help me out. I'm definately putting you on my credits list.

Bill


--
Message posted via http://www.accessmonster.com

I also noticed when I select a DutyClass "A" guy and the screen opens
empty, the form is AltScreen. When I select design view the form changes to
MainScreen?????


Private Sub Select_Click()
If Not IsNull(Me!lstSelect) Then
DoCmd.OpenForm "MainScreen", , , "SSN = """ & Me!lstSelect & """ AND
DutyClass = ""A"""
DoCmd.Close acForm, "SelectScreen"

Else
DoCmd.OpenForm "AltScreen", , , "DutyClass ""A"""
DoCmd.Close acForm, "SelectScreen"
End If

End Sub


--
Message posted via http://www.accessmonster.com

Whenever you've selected a name from lstSelect, it's going to open
MainScreen.

The only time it's going to open AltScreen is if you click on the Select
button and no name has been selected in lstSelect.

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



"Bill H via AccessMonster.com" wrote in message
...
I also noticed when I select a DutyClass "A" guy and the screen opens
empty, the form is AltScreen. When I select design view the form changes
to
MainScreen?????


Private Sub Select_Click()
If Not IsNull(Me!lstSelect) Then
DoCmd.OpenForm "MainScreen", , , "SSN = """ & Me!lstSelect & """
AND
DutyClass = ""A"""
DoCmd.Close acForm, "SelectScreen"

Else
DoCmd.OpenForm "AltScreen", , , "DutyClass ""A"""
DoCmd.Close acForm, "SelectScreen"
End If

End Sub


--
Message posted via http://www.accessmonster.com