Access2000, contionous form: Determining destination control and it's bookmark in LostFocus event

A

Arvi Laanemets

Hi

I'm trying to create a continous form which includes 2 linked combos, let's
name them Group and Item - selection in first determines available
selections in second. At moment I can select between 2 possible solutions,
which both aren't perfect.

1. In LostFocus event, the 2nd combo is requeried. All works well, unless I
move to same (Item) control on another row. When I move to same control, in
all rows with different Group, Item controls are displayed as empty.

2. In LostFocus event, the form is requeried. In this case Item control
always displays proper value for all rows, but the first control of same row
is activated. I have to click on destination control twice to activate it.

Now my question. Is there a relatively easy way to determine in LostFocus
event, which control on which row was attempted to activate (trough mouse,
or from keyboard like arrow key or Enter or Tab, or from form's record
selector, etc.), so I can improve the 2nd solution? (Otherwise I probably
prefer the 1st solution as less evil).

The code used:

Private Sub cbbItem_Enter()
If Nz(Me!cbbGroup) = 0 Then
MsgBox ("Select Group before!")
Me!cbbGroup.Enabled = True
Me!cbbGroup.SetFocus
Exit Sub
End If
cbbItem.RowSource = _
"SELECT [Items].[Item], " & _
"[Items].[Name] " & _
"FROM Items" & _
"WHERE (((Items.Group)=" & Me![cbbGroup] & "));"
End Sub

Private Sub cbbItem_LostFocus()
cbbItem.RowSource = "SELECT [Items].[Item], " & _
"[Items].[Name] " &_
"FROM Items;"
' For 2nd solution, from here until end of sub, comments and code rows
are reversed
' Dim CurrentRecord As Variant
' CurrentRecord = Me.Bookmark
Me!cbbItem.Requery
' Me.Requery
'Me.Bookmark = CurrentRecord
End Sub

Private Sub Form_Current()
If Nz(Me!cbbItem) = 0 Then
Me!cbbGroup.Enabled = True
Me!cbbGroup.SetFocus
Else
Me!cbbGroup.Enabled = False
End If
End Sub


Thanks in advance!
Arvi Laanemets
 
A

Arvi Laanemets

Hi


Thanks for answering, but the form is contionous - and on every row the
second combo can have different items list available.

Arvi Laanemets.


Lynn Trapp said:
Requery your second combo in the AfterUpdate event of the first combo.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Arvi Laanemets said:
Hi

I'm trying to create a continous form which includes 2 linked combos,
let's
name them Group and Item - selection in first determines available
selections in second. At moment I can select between 2 possible solutions,
which both aren't perfect.

1. In LostFocus event, the 2nd combo is requeried. All works well, unless
I
move to same (Item) control on another row. When I move to same control,
in
all rows with different Group, Item controls are displayed as empty.

2. In LostFocus event, the form is requeried. In this case Item control
always displays proper value for all rows, but the first control of same
row
is activated. I have to click on destination control twice to activate it.

Now my question. Is there a relatively easy way to determine in LostFocus
event, which control on which row was attempted to activate (trough mouse,
or from keyboard like arrow key or Enter or Tab, or from form's record
selector, etc.), so I can improve the 2nd solution? (Otherwise I probably
prefer the 1st solution as less evil).

The code used:

Private Sub cbbItem_Enter()
If Nz(Me!cbbGroup) = 0 Then
MsgBox ("Select Group before!")
Me!cbbGroup.Enabled = True
Me!cbbGroup.SetFocus
Exit Sub
End If
cbbItem.RowSource = _
"SELECT [Items].[Item], " & _
"[Items].[Name] " & _
"FROM Items" & _
"WHERE (((Items.Group)=" & Me![cbbGroup] & "));"
End Sub

Private Sub cbbItem_LostFocus()
cbbItem.RowSource = "SELECT [Items].[Item], " & _
"[Items].[Name] " &_
"FROM Items;"
' For 2nd solution, from here until end of sub, comments and code rows
are reversed
' Dim CurrentRecord As Variant
' CurrentRecord = Me.Bookmark
Me!cbbItem.Requery
' Me.Requery
'Me.Bookmark = CurrentRecord
End Sub

Private Sub Form_Current()
If Nz(Me!cbbItem) = 0 Then
Me!cbbGroup.Enabled = True
Me!cbbGroup.SetFocus
Else
Me!cbbGroup.Enabled = False
End If
End Sub


Thanks in advance!
Arvi Laanemets
Click to expand...
Click to expand...
 
L

Lynn Trapp

Sorry for not noticing that. In a continuous form, you can only do that if
your combo boxes are not bound to a field in the form's recordsource.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Arvi Laanemets said:
Hi


Thanks for answering, but the form is contionous - and on every row the
second combo can have different items list available.

Arvi Laanemets.


Lynn Trapp said:
Requery your second combo in the AfterUpdate event of the first combo.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Arvi Laanemets said:
Hi

I'm trying to create a continous form which includes 2 linked combos,
let's
name them Group and Item - selection in first determines available
selections in second. At moment I can select between 2 possible solutions,
which both aren't perfect.

1. In LostFocus event, the 2nd combo is requeried. All works well, unless
I
move to same (Item) control on another row. When I move to same
control,
in
all rows with different Group, Item controls are displayed as empty.

2. In LostFocus event, the form is requeried. In this case Item control
always displays proper value for all rows, but the first control of
same
row
is activated. I have to click on destination control twice to activate it.

Now my question. Is there a relatively easy way to determine in LostFocus
event, which control on which row was attempted to activate (trough mouse,
or from keyboard like arrow key or Enter or Tab, or from form's record
selector, etc.), so I can improve the 2nd solution? (Otherwise I probably
prefer the 1st solution as less evil).

The code used:

Private Sub cbbItem_Enter()
If Nz(Me!cbbGroup) = 0 Then
MsgBox ("Select Group before!")
Me!cbbGroup.Enabled = True
Me!cbbGroup.SetFocus
Exit Sub
End If
cbbItem.RowSource = _
"SELECT [Items].[Item], " & _
"[Items].[Name] " & _
"FROM Items" & _
"WHERE (((Items.Group)=" & Me![cbbGroup] & "));"
End Sub

Private Sub cbbItem_LostFocus()
cbbItem.RowSource = "SELECT [Items].[Item], " & _
"[Items].[Name] " &_
"FROM Items;"
' For 2nd solution, from here until end of sub, comments and code rows
are reversed
' Dim CurrentRecord As Variant
' CurrentRecord = Me.Bookmark
Me!cbbItem.Requery
' Me.Requery
'Me.Bookmark = CurrentRecord
End Sub

Private Sub Form_Current()
If Nz(Me!cbbItem) = 0 Then
Me!cbbGroup.Enabled = True
Me!cbbGroup.SetFocus
Else
Me!cbbGroup.Enabled = False
End If
End Sub


Thanks in advance!
Arvi Laanemets
Click to expand...
Click to expand...
Click to expand...
 
L

Lynn Trapp

My apologies again... I should have said that the combo box MUST be bound to
a field in the recordsource.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Arvi Laanemets said:
Hi


Thanks for answering, but the form is contionous - and on every row the
second combo can have different items list available.

Arvi Laanemets.


Lynn Trapp said:
Requery your second combo in the AfterUpdate event of the first combo.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Arvi Laanemets said:
Hi

I'm trying to create a continous form which includes 2 linked combos,
let's
name them Group and Item - selection in first determines available
selections in second. At moment I can select between 2 possible solutions,
which both aren't perfect.

1. In LostFocus event, the 2nd combo is requeried. All works well, unless
I
move to same (Item) control on another row. When I move to same
control,
in
all rows with different Group, Item controls are displayed as empty.

2. In LostFocus event, the form is requeried. In this case Item control
always displays proper value for all rows, but the first control of
same
row
is activated. I have to click on destination control twice to activate it.

Now my question. Is there a relatively easy way to determine in LostFocus
event, which control on which row was attempted to activate (trough mouse,
or from keyboard like arrow key or Enter or Tab, or from form's record
selector, etc.), so I can improve the 2nd solution? (Otherwise I probably
prefer the 1st solution as less evil).

The code used:

Private Sub cbbItem_Enter()
If Nz(Me!cbbGroup) = 0 Then
MsgBox ("Select Group before!")
Me!cbbGroup.Enabled = True
Me!cbbGroup.SetFocus
Exit Sub
End If
cbbItem.RowSource = _
"SELECT [Items].[Item], " & _
"[Items].[Name] " & _
"FROM Items" & _
"WHERE (((Items.Group)=" & Me![cbbGroup] & "));"
End Sub

Private Sub cbbItem_LostFocus()
cbbItem.RowSource = "SELECT [Items].[Item], " & _
"[Items].[Name] " &_
"FROM Items;"
' For 2nd solution, from here until end of sub, comments and code rows
are reversed
' Dim CurrentRecord As Variant
' CurrentRecord = Me.Bookmark
Me!cbbItem.Requery
' Me.Requery
'Me.Bookmark = CurrentRecord
End Sub

Private Sub Form_Current()
If Nz(Me!cbbItem) = 0 Then
Me!cbbGroup.Enabled = True
Me!cbbGroup.SetFocus
Else
Me!cbbGroup.Enabled = False
End If
End Sub


Thanks in advance!
Arvi Laanemets
Click to expand...
Click to expand...
Click to expand...
 
A

Arvi Laanemets

Hi

Both combos are bound to fields in forms recordsource.

MyTable: ID, Date, Group, Item, ...
Primary Key: ID (autonumber)

Groups: Group, Name
Primary Key: Group (autonumber)

Items: Item, Name, Group
Primary Key: Item (String)

MyForm (continous form):
RowSource: MyTable

cbbGroup:
ControlSource=Group
RowSource=SELECT [Groups].[Group], [Groups].[Name] FROM Groups;
ColumnCount=2 (width of 1st column is set to 0)

cbbItem:
ControlSource=Item
RowSource - is varied by events (see the starting message of current
thread), originally the SQL string is like
cbbItem.RowSource = "SELECT [Items].[Item], " & _
"[Items].[Name] " &_
"FROM Items;"
ColumnCount=2 (width of 1st column is set to 0)



Arvi Laanemets


Lynn Trapp said:
My apologies again... I should have said that the combo box MUST be bound to
a field in the recordsource.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Arvi Laanemets said:
Hi


Thanks for answering, but the form is contionous - and on every row the
second combo can have different items list available.

Arvi Laanemets.


Lynn Trapp said:
Requery your second combo in the AfterUpdate event of the first combo.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Hi

I'm trying to create a continous form which includes 2 linked combos,
let's
name them Group and Item - selection in first determines available
selections in second. At moment I can select between 2 possible solutions,
which both aren't perfect.

1. In LostFocus event, the 2nd combo is requeried. All works well, unless
I
move to same (Item) control on another row. When I move to same
control,
in
all rows with different Group, Item controls are displayed as empty.

2. In LostFocus event, the form is requeried. In this case Item control
always displays proper value for all rows, but the first control of
same
row
is activated. I have to click on destination control twice to
Click to expand...
activate
it.
Now my question. Is there a relatively easy way to determine in LostFocus
event, which control on which row was attempted to activate (trough mouse,
or from keyboard like arrow key or Enter or Tab, or from form's record
selector, etc.), so I can improve the 2nd solution? (Otherwise I probably
prefer the 1st solution as less evil).

The code used:

Private Sub cbbItem_Enter()
If Nz(Me!cbbGroup) = 0 Then
MsgBox ("Select Group before!")
Me!cbbGroup.Enabled = True
Me!cbbGroup.SetFocus
Exit Sub
End If
cbbItem.RowSource = _
"SELECT [Items].[Item], " & _
"[Items].[Name] " & _
"FROM Items" & _
"WHERE (((Items.Group)=" & Me![cbbGroup] & "));"
End Sub

Private Sub cbbItem_LostFocus()
cbbItem.RowSource = "SELECT [Items].[Item], " & _
"[Items].[Name] " &_
"FROM Items;"
' For 2nd solution, from here until end of sub, comments and code rows
are reversed
' Dim CurrentRecord As Variant
' CurrentRecord = Me.Bookmark
Me!cbbItem.Requery
' Me.Requery
'Me.Bookmark = CurrentRecord
End Sub

Private Sub Form_Current()
If Nz(Me!cbbItem) = 0 Then
Me!cbbGroup.Enabled = True
Me!cbbGroup.SetFocus
Else
Me!cbbGroup.Enabled = False
End If
End Sub


Thanks in advance!
Arvi Laanemets
Click to expand...
Click to expand...
Click to expand...
 
Top