HELP - I just want this to do as I'm thinking!

G

Gina Whipp

Hello All,

Code example below... What I want is turn Prospect into Customer (working);
requery lstClientID and cboClientID (working); then select the first item in
lstClientID (listbox) so the subforms shows information (NOT working) - See
line with asterisks.

Thanks Guys AND Gals!
Gina


Dim Msg, Style, Title, Response, MyString

Msg = "This Sales Lead is now a Customer?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "Sales Lead"

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then
MyString = "Yes"
Me.txtCustomerID = DMax("cpCustomerID", "qryClientNames") +
1
Me.chkChecked = 0
Forms![frmProspectProfile]![lstClientID].SetFocus
Forms![frmProspectProfile]![lstClientID].Requery
Forms![frmProspectProfile]![lstClientID] ***This is where I
need this list box to be [ItemData](0)***
Forms![frmProspectProfile]![cboClientID].Requery
Else
MyString = "No"
DoCmd.CancelEvent
End If
 
G

Gina Whipp

Thanks anyway, I got it. I needed to set the DefaultValue, once done all
works perfectly!
 
D

Duncan Bachen

Gina said:
Hello All,

Code example below... What I want is turn Prospect into Customer (working);
requery lstClientID and cboClientID (working); then select the first item in
lstClientID (listbox) so the subforms shows information (NOT working) - See
line with asterisks.

Thanks Guys AND Gals!
Gina


Dim Msg, Style, Title, Response, MyString

Msg = "This Sales Lead is now a Customer?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "Sales Lead"

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then
MyString = "Yes"
Me.txtCustomerID = DMax("cpCustomerID", "qryClientNames") +
1
Me.chkChecked = 0
Forms![frmProspectProfile]![lstClientID].SetFocus
Forms![frmProspectProfile]![lstClientID].Requery
Forms![frmProspectProfile]![lstClientID] ***This is where I
need this list box to be [ItemData](0)***
Forms![frmProspectProfile]![cboClientID].Requery
Else
MyString = "No"
DoCmd.CancelEvent
End If
Click to expand...

You can use the selected property to either determine if something is
selected, or to select it. So you could use
Forms![frmProspectProfile]![lstClientID].Selected(0) = True

HTH
 
G

Gina Whipp

Thanks Duncan, you just showed me ANOTHER way to skin the cat!

Duncan Bachen said:
Gina said:
Hello All,

Code example below... What I want is turn Prospect into Customer
(working); requery lstClientID and cboClientID (working); then select the
first item in lstClientID (listbox) so the subforms shows information
(NOT working) - See line with asterisks.

Thanks Guys AND Gals!
Gina


Dim Msg, Style, Title, Response, MyString

Msg = "This Sales Lead is now a Customer?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "Sales Lead"

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then
MyString = "Yes"
Me.txtCustomerID = DMax("cpCustomerID", "qryClientNames")
+ 1
Me.chkChecked = 0
Forms![frmProspectProfile]![lstClientID].SetFocus
Forms![frmProspectProfile]![lstClientID].Requery
Forms![frmProspectProfile]![lstClientID] ***This is
where I need this list box to be [ItemData](0)***
Forms![frmProspectProfile]![cboClientID].Requery
Else
MyString = "No"
DoCmd.CancelEvent
End If
Click to expand...

You can use the selected property to either determine if something is
selected, or to select it. So you could use
Forms![frmProspectProfile]![lstClientID].Selected(0) = True

HTH
Click to expand...
 
M

Marshall Barton

Gina said:
Code example below... What I want is turn Prospect into Customer (working);
requery lstClientID and cboClientID (working); then select the first item in
lstClientID (listbox) so the subforms shows information (NOT working) - See
line with asterisks.

Dim Msg, Style, Title, Response, MyString

Msg = "This Sales Lead is now a Customer?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "Sales Lead"

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then
MyString = "Yes"
Me.txtCustomerID = DMax("cpCustomerID", "qryClientNames") +
1
Me.chkChecked = 0
Forms![frmProspectProfile]![lstClientID].SetFocus
Forms![frmProspectProfile]![lstClientID].Requery
Forms![frmProspectProfile]![lstClientID] ***This is where I
need this list box to be [ItemData](0)***
Forms![frmProspectProfile]![cboClientID].Requery
Click to expand...


If you really want it to be ItemData(0), then set it to
ItemData(0):

Forms!frmProspectProfile!lstClientID = _
Forms!frmProspectProfile!lstClientID.ItemData(0)

But, can't you use Me instead of the full form reference?

I don't see the need for all those Requeries though.
 
G

Gina Whipp

I am using the full form reference because I am requerying via a subform on
a tab control. Without the requery the list box and combo box do not clean
the names from their prospective list. (Once a customer they can no longer
be accessed or seen on the Sales Lead menu.)

MainForm = frmProspectProfile
TabControl = tabProspectProfile
SubformOnTab = sfrProspectMain

You change the Sales Lead to a Customer, hence removing them from the list.
If I don't requery the name remains AND the list shows an empty record on
sfrProspectMain, neight of which I want.

Hope I explained that well.

Marshall Barton said:
Gina said:
Code example below... What I want is turn Prospect into Customer
(working);
requery lstClientID and cboClientID (working); then select the first item
in
lstClientID (listbox) so the subforms shows information (NOT working) -
See
line with asterisks.

Dim Msg, Style, Title, Response, MyString

Msg = "This Sales Lead is now a Customer?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "Sales Lead"

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then
MyString = "Yes"
Me.txtCustomerID = DMax("cpCustomerID", "qryClientNames")
+
1
Me.chkChecked = 0
Forms![frmProspectProfile]![lstClientID].SetFocus
Forms![frmProspectProfile]![lstClientID].Requery
Forms![frmProspectProfile]![lstClientID] ***This is where
I
need this list box to be [ItemData](0)***
Forms![frmProspectProfile]![cboClientID].Requery
Click to expand...


If you really want it to be ItemData(0), then set it to
ItemData(0):

Forms!frmProspectProfile!lstClientID = _
Forms!frmProspectProfile!lstClientID.ItemData(0)

But, can't you use Me instead of the full form reference?

I don't see the need for all those Requeries though.
Click to expand...
 
G

Guest

i don't know if this is considered good form but if it is i'm going to ask
whoever's reading this thread to cast your gaze at my own which i posted
synchronously today having the subject "looking up value in combo box"; i too
would "want this to do as I'm thinking!".

-ted
 
G

Gina Whipp

I looked but am unable to help!

Ted said:
i don't know if this is considered good form but if it is i'm going to ask
whoever's reading this thread to cast your gaze at my own which i posted
synchronously today having the subject "looking up value in combo box"; i
too
would "want this to do as I'm thinking!".

-ted

Gina Whipp said:
Hello All,

Code example below... What I want is turn Prospect into Customer
(working);
requery lstClientID and cboClientID (working); then select the first item
in
lstClientID (listbox) so the subforms shows information (NOT working) -
See
line with asterisks.

Thanks Guys AND Gals!
Gina


Dim Msg, Style, Title, Response, MyString

Msg = "This Sales Lead is now a Customer?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "Sales Lead"

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then
MyString = "Yes"
Me.txtCustomerID = DMax("cpCustomerID", "qryClientNames")
+
1
Me.chkChecked = 0
Forms![frmProspectProfile]![lstClientID].SetFocus
Forms![frmProspectProfile]![lstClientID].Requery
Forms![frmProspectProfile]![lstClientID] ***This is
where I
need this list box to be [ItemData](0)***
Forms![frmProspectProfile]![cboClientID].Requery
Else
MyString = "No"
DoCmd.CancelEvent
End If
Click to expand...
Click to expand...
 
M

Marshall Barton

You can use Parent instead of the full form reference. This
may be a minor point, but it does make the code independent
of the name of the main form (i.e. you won't have to change
the code if you should ever change the name of the main form
or use this subform in a another mainform).

Me.Parent.lstClientID.SetFocus 'Why this line?
Me.Parent.lstClientID.Requery
Me.Parent.lstClientID = Me.Parent.lstClientID.ItemData(0)
Me.Parent.lstClientID.Requery 'Why this line?



Gina said:
I am using the full form reference because I am requerying via a subform on
a tab control. Without the requery the list box and combo box do not clean
the names from their prospective list. (Once a customer they can no longer
be accessed or seen on the Sales Lead menu.)

MainForm = frmProspectProfile
TabControl = tabProspectProfile
SubformOnTab = sfrProspectMain

You change the Sales Lead to a Customer, hence removing them from the list.
If I don't requery the name remains AND the list shows an empty record on
sfrProspectMain, neight of which I want.

Hope I explained that well.
Gina said:
Code example below... What I want is turn Prospect into Customer
(working);
requery lstClientID and cboClientID (working); then select the first item
in
lstClientID (listbox) so the subforms shows information (NOT working) -
See
line with asterisks.

Dim Msg, Style, Title, Response, MyString

Msg = "This Sales Lead is now a Customer?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "Sales Lead"

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then
MyString = "Yes"
Me.txtCustomerID = DMax("cpCustomerID", "qryClientNames")
+
1
Me.chkChecked = 0
Forms![frmProspectProfile]![lstClientID].SetFocus
Forms![frmProspectProfile]![lstClientID].Requery
Forms![frmProspectProfile]![lstClientID] ***This is where
I
need this list box to be [ItemData](0)***
Forms![frmProspectProfile]![cboClientID].Requery
Click to expand...

"Marshall Barton" wrote
If you really want it to be ItemData(0), then set it to
ItemData(0):

Forms!frmProspectProfile!lstClientID = _
Forms!frmProspectProfile!lstClientID.ItemData(0)

But, can't you use Me instead of the full form reference?

I don't see the need for all those Requeries though.
Click to expand...
Click to expand...
 
G

Guest

sure, gina. thanks anywhoo.

-ted

Gina Whipp said:
I looked but am unable to help!

Ted said:
i don't know if this is considered good form but if it is i'm going to ask
whoever's reading this thread to cast your gaze at my own which i posted
synchronously today having the subject "looking up value in combo box"; i
too
would "want this to do as I'm thinking!".

-ted

Gina Whipp said:
Hello All,

Code example below... What I want is turn Prospect into Customer
(working);
requery lstClientID and cboClientID (working); then select the first item
in
lstClientID (listbox) so the subforms shows information (NOT working) -
See
line with asterisks.

Thanks Guys AND Gals!
Gina


Dim Msg, Style, Title, Response, MyString

Msg = "This Sales Lead is now a Customer?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "Sales Lead"

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then
MyString = "Yes"
Me.txtCustomerID = DMax("cpCustomerID", "qryClientNames")
+
1
Me.chkChecked = 0
Forms![frmProspectProfile]![lstClientID].SetFocus
Forms![frmProspectProfile]![lstClientID].Requery
Forms![frmProspectProfile]![lstClientID] ***This is
where I
need this list box to be [ItemData](0)***
Forms![frmProspectProfile]![cboClientID].Requery
Else
MyString = "No"
DoCmd.CancelEvent
End If
Click to expand...
Click to expand...
Click to expand...
 
G

Gina Whipp

Marshall,

Using Me.Parent, never thought of that; been using the same way for so
long... As for Me.Parent.lstClientID.SetFocus because when I just had
requery it seemed not to find my parent form and nothing requeried. ANd the
last line to Me.Parent.lstClientID.Requery because after I set the
ItemData(0) once again nothing showed until I requeried.

And big THANKS for taking the time, I am learning ALOT

Gina

Marshall Barton said:
You can use Parent instead of the full form reference. This
may be a minor point, but it does make the code independent
of the name of the main form (i.e. you won't have to change
the code if you should ever change the name of the main form
or use this subform in a another mainform).

Me.Parent.lstClientID.SetFocus 'Why this line?
Me.Parent.lstClientID.Requery
Me.Parent.lstClientID = Me.Parent.lstClientID.ItemData(0)
Me.Parent.lstClientID.Requery 'Why this line?



Gina said:
I am using the full form reference because I am requerying via a subform
on
a tab control. Without the requery the list box and combo box do not
clean
the names from their prospective list. (Once a customer they can no
longer
be accessed or seen on the Sales Lead menu.)

MainForm = frmProspectProfile
TabControl = tabProspectProfile
SubformOnTab = sfrProspectMain

You change the Sales Lead to a Customer, hence removing them from the
list.
If I don't requery the name remains AND the list shows an empty record on
sfrProspectMain, neight of which I want.

Hope I explained that well.
Gina Whipp wrote:
Code example below... What I want is turn Prospect into Customer
(working);
requery lstClientID and cboClientID (working); then select the first
item
in
lstClientID (listbox) so the subforms shows information (NOT working) -
See
line with asterisks.

Dim Msg, Style, Title, Response, MyString

Msg = "This Sales Lead is now a Customer?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "Sales Lead"

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then
MyString = "Yes"
Me.txtCustomerID = DMax("cpCustomerID",
"qryClientNames")
+
1
Me.chkChecked = 0
Forms![frmProspectProfile]![lstClientID].SetFocus
Forms![frmProspectProfile]![lstClientID].Requery
Forms![frmProspectProfile]![lstClientID] ***This is
where
I
need this list box to be [ItemData](0)***
Forms![frmProspectProfile]![cboClientID].Requery

"Marshall Barton" wrote
If you really want it to be ItemData(0), then set it to
ItemData(0):

Forms!frmProspectProfile!lstClientID = _
Forms!frmProspectProfile!lstClientID.ItemData(0)

But, can't you use Me instead of the full form reference?

I don't see the need for all those Requeries though.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Do Until... Help Please! 11
Repost, 1st one didn't go thru... Please Help! Form not listening to me 6
Append multiple rows using DMax 1
Open several files and copy the contents 5
Help clean up this code... 8

Top