Listbox selection changes data in field

  • Thread starter Bill H via AccessMonster.com
  • Start date
B

Bill H via AccessMonster.com

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
 
A

Allen Browne

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.
 
B

Bill H via AccessMonster.com

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
 
D

Douglas J. Steele

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
 
B

Bill H via 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
 
D

Douglas J. Steele

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"")"""
 
B

Bill H via 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 DutyClass<>A 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 DutyClass<>A 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
 
B

Bill H via 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
 
D

Douglas J. Steele

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.
 
B

Bill H via AccessMonster.com

wish I could show you. I'll do researching and maybe find another way. This
seems to be it though.
 
B

Bill H via AccessMonster.com

Thanks for your help. Let me know if you want to help further...by seeing
what I have so far. I need someone to help.
 
D

Douglas J. Steele

I'd prefer not getting your database.

Explain in words what your form looks like, and what you're trying to do.
 
B

Bill H via AccessMonster.com

Just kidding. This is so simple that it's starting to get stupid.

I want one form that lists the ssn and names, in alphabetical order, of
everyone in the db. I either double-click the name or have a command button
that opens up either form A or B depending on the dutyclass = or <> "A".

I have all of this and it works great except when dutyclass<>"A" the form
shows no data. I still can't find anything on how to do this.
 
B

Bill H via AccessMonster.com

I started a new db with a table, query and 3 forms(SelectForm, FormA and
FormB). I used the other code and it still didn't work so I tried to
simplify it. This is the code I'm using now, but can't seem to get line 2
correct with the field DutyClass="A". Any ideas?

thanks,
Bill


Private Sub lstSelect_DblClick(Cancel As Integer)

If Me!lstSelect = [DutyClass] = "A" Then

DoCmd.OpenForm "FormA", , , "SSN = """ & Me!lstSelect & """"

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

End If
End Sub
 
D

Douglas J. Steele

Presumably lstSelect contains an SSN value. Why, therefore, would you check
whether it's equal to DutyClass = "A"?

If, in fact, lstSelect has a value DutyClass = "A" in it, you'd need to use

If Me!lstSelect = "[DutyClass] = ""A""" Then

How do you determine whether it's DutyClass A or not?
 
B

Bill H via AccessMonster.com

The DutyClass field is in another form as initial in-processing. Afterward,
the different sections can access their dutyclass primarily, but also must
have access to the other dutyclasses. However, this data is already in the
db for testing purposes of this step.

I've reversed the DutyClass to the top. Now FormB opens with <>A data, but
FormA does not open at all when DutyClass=A person is selected. It opens in
FormB and has no data. After days of testing many different ways, it appears
that the Else funtions are not being recognized. This is in both my primary
db and the one I made up just for testing purposes of this code. What ever
is in the Else section, that form is not used and no data is shown. This is
why I wanted to put the DutyClass in the If...then line. That didn't work
either.

-----------------------------
Private Sub lstSelect_DblClick(Cancel As Integer)

If (Me!lstSelect) Then

DoCmd.OpenForm "FormB", , , "SSN = """ & Me!lstSelect & """ AND
DutyClass <> ""A"""

Else
DoCmd.OpenForm "FormA"

End If
End Sub
 
Top