Need query guru

[Guest]

Hello -

I have four tables related to each other:

tblStates
StateID
State

tblDeclaration
DeclarationID
DeclarationNo
StateID

tblCalls
Agent
CallerName
Phone
CallDate
[ Some other columns]
CallTypeID
DeclarationID

tblCallType
CallTypeID
CallType

I need to join these tables for a form that can be navigated through and
added to.
Problem: I need two combo boxes and a text box so that people have the
ability to select a State from the first combobox that narrows down the
selections for the second combobox which is DeclarationNo, which
automatically fills in the textbox with the resulting Declaration.

Please help! I've been stressing over this for quite some time.

 

[tina]

presumably you're wanting to enter new records, or edit existing records, in
tblCalls. you don't need a query.
bind your form to tblCalls.
add an *unbound* combo box (to the Header section, maybe), with its'
RowSource (*not* ControlSource) property based on tblStates. i'll call the
control cboState. set the control's properties as follows:

ControlSource: <blank>
RowSource: tblStates
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

suggest you read up on the above properties in Access Help, to better
understand how the work in a combo box control.

next, make sure that the DeclarationID field (from the form's RecordSource
of tblCalls) is *bound* to a combo box control. set the control's properties
as follows:

ControlSource: DeclarationID
(this binds the control to the DeclarationID field in tblCalls.)
RowSource: SELECT DeclarationID, DeclarationNo FROM tblDeclaration WHERE
StateID = Forms!FormName!cboState;
(substitute the correct name of the form for FormName, of course.)
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

now what happens is that when the user selects a state from the droplist in
cboState, then the DeclarationID combo box is requeried, so that only the
declaration records with a matching state id will show in the droplist.

next, make sure that the CallTypeID field (from the form's RecordSource of
tblCalls) is *bound* to a combo box control. set the control's properties as
follows:

ControlSource: CallTypeID
(this binds the control to the CallTypeID field in tblCalls.)
RowSource: tblCallType
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

hth

 

[Guest]

Hi Tina -

I REALLY appreciate your help!

I still can't get the records to go backward. I had to also put in a
textbox that derives its value from cboDeclaration (the combobox with the
control source of DeclarationID). In code under cboDeclarationNo_AfterUpdate
I put:

Me.txtDeclaration = Me.cboDeclarationNo.Column(3)

I added the extra column to the query.

Also, I was unable to get anything to display in cboDeclarationNo when I
added the Where portion of the Select statement. I don't get it! I
inadvertently deleted that portion and only have "Select DeclarationID,
DeclarationNo, Declaration, StateID" For some reason that works -- do you
know why?

Is adding the textbox and using the strange Select statement causing it not
to go backwards (it's stuck on the same State - whatever state's in the
record I started clicking the Previous button from is the state that
continues to show as I keep clicking Previous).

Again, thank you so much for your time (and Brains)!
--
Sandy

presumably you're wanting to enter new records, or edit existing records, in
tblCalls. you don't need a query.
bind your form to tblCalls.
add an *unbound* combo box (to the Header section, maybe), with its'
RowSource (*not* ControlSource) property based on tblStates. i'll call the
control cboState. set the control's properties as follows:

ControlSource: <blank>
RowSource: tblStates
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

suggest you read up on the above properties in Access Help, to better
understand how the work in a combo box control.

next, make sure that the DeclarationID field (from the form's RecordSource
of tblCalls) is *bound* to a combo box control. set the control's properties
as follows:

ControlSource: DeclarationID
(this binds the control to the DeclarationID field in tblCalls.)
RowSource: SELECT DeclarationID, DeclarationNo FROM tblDeclaration WHERE
StateID = Forms!FormName!cboState;
(substitute the correct name of the form for FormName, of course.)
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

now what happens is that when the user selects a state from the droplist in
cboState, then the DeclarationID combo box is requeried, so that only the
declaration records with a matching state id will show in the droplist.

next, make sure that the CallTypeID field (from the form's RecordSource of
tblCalls) is *bound* to a combo box control. set the control's properties as
follows:

ControlSource: CallTypeID
(this binds the control to the CallTypeID field in tblCalls.)
RowSource: tblCallType
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

hth

Hello -

I have four tables related to each other:

tblStates
StateID
State

tblDeclaration
DeclarationID
DeclarationNo
StateID

tblCalls
Agent
CallerName
Phone
CallDate
[ Some other columns]
CallTypeID
DeclarationID

tblCallType
CallTypeID
CallType

I need to join these tables for a form that can be navigated through and
added to.
Problem: I need two combo boxes and a text box so that people have the
ability to select a State from the first combobox that narrows down the
selections for the second combobox which is DeclarationNo, which
automatically fills in the textbox with the resulting Declaration.

Please help! I've been stressing over this for quite some time.

 

[tina]

comments inline.

Hi Tina -

I REALLY appreciate your help!

I still can't get the records to go backward. I had to also put in a
textbox that derives its value from cboDeclaration (the combobox with the
control source of DeclarationID). In code under cboDeclarationNo_AfterUpdate
I put:

Me.txtDeclaration = Me.cboDeclarationNo.Column(3)

in the setup that i outlined in my previous post, cboDeclarationNo has only
two columns, not four. (note that combo box columns are zero-based in
Access, so a reference to .Column(3) will return a value from the *fourth*
column in a combo box control.)

beyond that, the combo box itself will display the DeclarationNo value,
while storing the value of the DeclarationID - as it should. so i don't
understand why you want a separate unbound textbox control to again display
the DeclarationNo value.

I added the extra column to the query.

Also, I was unable to get anything to display in cboDeclarationNo when I
added the Where portion of the Select statement. I don't get it! I
inadvertently deleted that portion and only have "Select DeclarationID,
DeclarationNo, Declaration, StateID"

do you have *four* fields in tblDeclaration? your previous post only listed
three. it's probable that i gave you inappropriate settings suggestions for
the cboDeclarationNo control, based on miscommunication about the RowSource
table.

For some reason that works -- do you
know why?

Is adding the textbox and using the strange Select statement causing it not
to go backwards (it's stuck on the same State - whatever state's in the
record I started clicking the Previous button from is the state that
continues to show as I keep clicking Previous).

that's the usual problem with looking at existing records in a form where
one combobox's "droplist" values are dependent on the value in another combo
box - in this case complicated somewhat by the fact that the first combo box
is unbound. here's one way to solve it:

re-add the WHERE clause to the RowSource property's SELECT statement, in the
cboDeclarationNo combo box control.

add the following code to the AfterUpdate event procedure of cboState, as

If IsNull(Me!cboState) Then
Me!cboDeclarationNo.RowSource = "SELECT DeclarationID, " _
& "DeclarationNo FROM tblDeclaration"
Me.Refresh
Else
Me!cboDeclarationNo.RowSource = "SELECT DeclarationID, " _
& "DeclarationNo FROM tblDeclaration WHERE " _
& "tblDeclaration.StateID=[Forms]![frmCalls]![cboState]"
Me.Refresh
End If

(note that the SELECT statements above are based on the 3 fields you
originally posted for tblDeclaration. if the table is different, as
addressed earlier in this post, then the Select statement may be different
as well.)

add the following code to the form's Current event procedure, as

Me!cboState = Null
Me!cboDeclarationNo.RowSource = "SELECT DeclarationID, " _
& "DeclarationNo FROM tblDeclaration"
Me.Refresh

Again, thank you so much for your time (and Brains)!

well, you're welcome, but let's wait until the form is working for you,
first!

--
Sandy

presumably you're wanting to enter new records, or edit existing records, in
tblCalls. you don't need a query.
bind your form to tblCalls.
add an *unbound* combo box (to the Header section, maybe), with its'
RowSource (*not* ControlSource) property based on tblStates. i'll call the
control cboState. set the control's properties as follows:

ControlSource: <blank>
RowSource: tblStates
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

suggest you read up on the above properties in Access Help, to better
understand how the work in a combo box control.

next, make sure that the DeclarationID field (from the form's RecordSource
of tblCalls) is *bound* to a combo box control. set the control's properties
as follows:

ControlSource: DeclarationID
(this binds the control to the DeclarationID field in tblCalls.)
RowSource: SELECT DeclarationID, DeclarationNo FROM tblDeclaration WHERE
StateID = Forms!FormName!cboState;
(substitute the correct name of the form for FormName, of course.)
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

now what happens is that when the user selects a state from the droplist in
cboState, then the DeclarationID combo box is requeried, so that only the
declaration records with a matching state id will show in the droplist.

next, make sure that the CallTypeID field (from the form's RecordSource of
tblCalls) is *bound* to a combo box control. set the control's properties as
follows:

ControlSource: CallTypeID
(this binds the control to the CallTypeID field in tblCalls.)
RowSource: tblCallType
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

hth

Hello -

I have four tables related to each other:

tblStates
StateID
State

tblDeclaration
DeclarationID
DeclarationNo
StateID

tblCalls
Agent
CallerName
Phone
CallDate
[ Some other columns]
CallTypeID
DeclarationID

tblCallType
CallTypeID
CallType

I need to join these tables for a form that can be navigated through and
added to.
Problem: I need two combo boxes and a text box so that people have the
ability to select a State from the first combobox that narrows down the
selections for the second combobox which is DeclarationNo, which
automatically fills in the textbox with the resulting Declaration.

Please help! I've been stressing over this for quite some time.

 

[Guest]

Hi Tina -

Thanks again so much for your response and for your time! I've had a
horrific day and haven't had a chance to try out the rest of your code. (Had
to deploy a part of this application - the part that moves forward - and ran
into major problems with the ocx file for the date time picker in another
part of the app)

I have to build in the ability to move backward through the files over the
weekend. Hopefully I can incorporate your code and it will work. By the
way, if you happen to have an example database that works, my email is
(e-mail address removed).

--
Sandy

comments inline.

Hi Tina -

I REALLY appreciate your help!

I still can't get the records to go backward. I had to also put in a
textbox that derives its value from cboDeclaration (the combobox with the
control source of DeclarationID). In code under cboDeclarationNo_AfterUpdate
I put:

Me.txtDeclaration = Me.cboDeclarationNo.Column(3)

in the setup that i outlined in my previous post, cboDeclarationNo has only
two columns, not four. (note that combo box columns are zero-based in
Access, so a reference to .Column(3) will return a value from the *fourth*
column in a combo box control.)

beyond that, the combo box itself will display the DeclarationNo value,
while storing the value of the DeclarationID - as it should. so i don't
understand why you want a separate unbound textbox control to again display
the DeclarationNo value.

I added the extra column to the query.

Also, I was unable to get anything to display in cboDeclarationNo when I
added the Where portion of the Select statement. I don't get it! I
inadvertently deleted that portion and only have "Select DeclarationID,
DeclarationNo, Declaration, StateID"

do you have *four* fields in tblDeclaration? your previous post only listed
three. it's probable that i gave you inappropriate settings suggestions for
the cboDeclarationNo control, based on miscommunication about the RowSource
table.

For some reason that works -- do you
know why?

Is adding the textbox and using the strange Select statement causing it not
to go backwards (it's stuck on the same State - whatever state's in the
record I started clicking the Previous button from is the state that
continues to show as I keep clicking Previous).

that's the usual problem with looking at existing records in a form where
one combobox's "droplist" values are dependent on the value in another combo
box - in this case complicated somewhat by the fact that the first combo box
is unbound. here's one way to solve it:

re-add the WHERE clause to the RowSource property's SELECT statement, in the
cboDeclarationNo combo box control.

add the following code to the AfterUpdate event procedure of cboState, as

If IsNull(Me!cboState) Then
Me!cboDeclarationNo.RowSource = "SELECT DeclarationID, " _
& "DeclarationNo FROM tblDeclaration"
Me.Refresh
Else
Me!cboDeclarationNo.RowSource = "SELECT DeclarationID, " _
& "DeclarationNo FROM tblDeclaration WHERE " _
& "tblDeclaration.StateID=[Forms]![frmCalls]![cboState]"
Me.Refresh
End If

(note that the SELECT statements above are based on the 3 fields you
originally posted for tblDeclaration. if the table is different, as
addressed earlier in this post, then the Select statement may be different
as well.)

add the following code to the form's Current event procedure, as

Me!cboState = Null
Me!cboDeclarationNo.RowSource = "SELECT DeclarationID, " _
& "DeclarationNo FROM tblDeclaration"
Me.Refresh

Again, thank you so much for your time (and Brains)!

well, you're welcome, but let's wait until the form is working for you,
first!

--
Sandy

presumably you're wanting to enter new records, or edit existing records, in
tblCalls. you don't need a query.
bind your form to tblCalls.
add an *unbound* combo box (to the Header section, maybe), with its'
RowSource (*not* ControlSource) property based on tblStates. i'll call the
control cboState. set the control's properties as follows:

ControlSource: <blank>
RowSource: tblStates
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

suggest you read up on the above properties in Access Help, to better
understand how the work in a combo box control.

next, make sure that the DeclarationID field (from the form's RecordSource
of tblCalls) is *bound* to a combo box control. set the control's properties
as follows:

ControlSource: DeclarationID
(this binds the control to the DeclarationID field in tblCalls.)
RowSource: SELECT DeclarationID, DeclarationNo FROM tblDeclaration WHERE
StateID = Forms!FormName!cboState;
(substitute the correct name of the form for FormName, of course.)
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

now what happens is that when the user selects a state from the droplist in
cboState, then the DeclarationID combo box is requeried, so that only the
declaration records with a matching state id will show in the droplist.

next, make sure that the CallTypeID field (from the form's RecordSource of
tblCalls) is *bound* to a combo box control. set the control's properties as
follows:

ControlSource: CallTypeID
(this binds the control to the CallTypeID field in tblCalls.)
RowSource: tblCallType
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

hth


Hello -

I have four tables related to each other:

tblStates
StateID
State

tblDeclaration
DeclarationID
DeclarationNo
StateID

tblCalls
Agent
CallerName
Phone
CallDate
[ Some other columns]
CallTypeID
DeclarationID

tblCallType
CallTypeID
CallType

I need to join these tables for a form that can be navigated through and
added to.
Problem: I need two combo boxes and a text box so that people have the
ability to select a State from the first combobox that narrows down the
selections for the second combobox which is DeclarationNo, which
automatically fills in the textbox with the resulting Declaration.

Please help! I've been stressing over this for quite some time.

 

[Guest]

Hi Tina -

I redid the form as you suggested and it almost works. The problem with the
additional column that I confused you with was that in my original post I
indicated tblDeclaration was:

tblDeclaration
DeclarationID
DeclarationNo
StateID

And it's really:

tblDeclaration
DeclarationID
DeclarationNo
Declaration
StateID

-- I forgot to add the Declaration column, which is really a description
field.

That's the field that needed to go into the textbox. I was able to get that
to work.

ONLY PROBLEM LEFT: I cannot get the records to go backwards and display
State. It is successfully displaying DeclarationNo and Declaration (the
txtbox).

I have the following code:

[CallType left out because I don't have a problem with it]

Private Sub cboDeclarationNo_AfterUpdate()
Me.txtDeclaration = Me.cboDeclarationNo.Column(2)
End Sub

Private Sub cboState_AfterUpdate()
If IsNull(Me!cboState) Then
Me!cboDeclarationNo.RowSource = "Select DeclarationID, " _
& "DeclarationNo, Declaration From tblDeclaration"
Me.Refresh
Else
Me!cboDeclarationNo.RowSource = "Select DeclarationID, " _
& "DeclarationNo, Declaration From tblDeclaration Where " _
& "tblDeclaration.StateID=[Forms]![frmCalls]![cboState]"
Me.Refresh
End If
End Sub


Private Sub Form_Current()
Me.cboState = Null
Me!cboDeclarationNo.RowSource = "Select DeclarationID, " _
& "DeclarationNo, Declaration From tblDeclaration"
Me.txtDeclaration = Me.cboDeclarationNo.Column(2)
Me.Refresh
End Sub

Any suggestions as to why State isn't displaying going backwards?

Thanks again for all of your help!
--
Sandy

comments inline.

Hi Tina -

I REALLY appreciate your help!

I still can't get the records to go backward. I had to also put in a
textbox that derives its value from cboDeclaration (the combobox with the
control source of DeclarationID). In code under cboDeclarationNo_AfterUpdate
I put:

Me.txtDeclaration = Me.cboDeclarationNo.Column(3)

in the setup that i outlined in my previous post, cboDeclarationNo has only
two columns, not four. (note that combo box columns are zero-based in
Access, so a reference to .Column(3) will return a value from the *fourth*
column in a combo box control.)

beyond that, the combo box itself will display the DeclarationNo value,
while storing the value of the DeclarationID - as it should. so i don't
understand why you want a separate unbound textbox control to again display
the DeclarationNo value.

I added the extra column to the query.

Also, I was unable to get anything to display in cboDeclarationNo when I
added the Where portion of the Select statement. I don't get it! I
inadvertently deleted that portion and only have "Select DeclarationID,
DeclarationNo, Declaration, StateID"

do you have *four* fields in tblDeclaration? your previous post only listed
three. it's probable that i gave you inappropriate settings suggestions for
the cboDeclarationNo control, based on miscommunication about the RowSource
table.

For some reason that works -- do you
know why?

Is adding the textbox and using the strange Select statement causing it not
to go backwards (it's stuck on the same State - whatever state's in the
record I started clicking the Previous button from is the state that
continues to show as I keep clicking Previous).

that's the usual problem with looking at existing records in a form where
one combobox's "droplist" values are dependent on the value in another combo
box - in this case complicated somewhat by the fact that the first combo box
is unbound. here's one way to solve it:

re-add the WHERE clause to the RowSource property's SELECT statement, in the
cboDeclarationNo combo box control.

add the following code to the AfterUpdate event procedure of cboState, as

If IsNull(Me!cboState) Then
Me!cboDeclarationNo.RowSource = "SELECT DeclarationID, " _
& "DeclarationNo FROM tblDeclaration"
Me.Refresh
Else
Me!cboDeclarationNo.RowSource = "SELECT DeclarationID, " _
& "DeclarationNo FROM tblDeclaration WHERE " _
& "tblDeclaration.StateID=[Forms]![frmCalls]![cboState]"
Me.Refresh
End If

(note that the SELECT statements above are based on the 3 fields you
originally posted for tblDeclaration. if the table is different, as
addressed earlier in this post, then the Select statement may be different
as well.)

add the following code to the form's Current event procedure, as

Me!cboState = Null
Me!cboDeclarationNo.RowSource = "SELECT DeclarationID, " _
& "DeclarationNo FROM tblDeclaration"
Me.Refresh

Again, thank you so much for your time (and Brains)!

well, you're welcome, but let's wait until the form is working for you,
first!

--
Sandy

presumably you're wanting to enter new records, or edit existing records, in
tblCalls. you don't need a query.
bind your form to tblCalls.
add an *unbound* combo box (to the Header section, maybe), with its'
RowSource (*not* ControlSource) property based on tblStates. i'll call the
control cboState. set the control's properties as follows:

ControlSource: <blank>
RowSource: tblStates
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

suggest you read up on the above properties in Access Help, to better
understand how the work in a combo box control.

next, make sure that the DeclarationID field (from the form's RecordSource
of tblCalls) is *bound* to a combo box control. set the control's properties
as follows:

ControlSource: DeclarationID
(this binds the control to the DeclarationID field in tblCalls.)
RowSource: SELECT DeclarationID, DeclarationNo FROM tblDeclaration WHERE
StateID = Forms!FormName!cboState;
(substitute the correct name of the form for FormName, of course.)
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

now what happens is that when the user selects a state from the droplist in
cboState, then the DeclarationID combo box is requeried, so that only the
declaration records with a matching state id will show in the droplist.

next, make sure that the CallTypeID field (from the form's RecordSource of
tblCalls) is *bound* to a combo box control. set the control's properties as
follows:

ControlSource: CallTypeID
(this binds the control to the CallTypeID field in tblCalls.)
RowSource: tblCallType
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

hth


Hello -

I have four tables related to each other:

tblStates
StateID
State

tblDeclaration
DeclarationID
DeclarationNo
StateID

tblCalls
Agent
CallerName
Phone
CallDate
[ Some other columns]
CallTypeID
DeclarationID

tblCallType
CallTypeID
CallType

I need to join these tables for a form that can be navigated through and
added to.
Problem: I need two combo boxes and a text box so that people have the
ability to select a State from the first combobox that narrows down the
selections for the second combobox which is DeclarationNo, which
automatically fills in the textbox with the resulting Declaration.

Please help! I've been stressing over this for quite some time.

 

[tina]

Any suggestions as to why State isn't displaying going backwards?

cboState is an unbound control, so without specific code to alter it, the
control will continue to show whatever value you last chose from the
droplist, as you move from record to record. it isn't tied to the Current
record in any way.

the code i gave you specifically sets the value of cboState to Null on the
form's Current event, which is how i usually handle unbound combobox
controls that the user chooses values from.

you have two choices: to write code that sets the value of the cboState to
the value of the DeclarationID's associated state, in the form's Current
event; or to add an unbound textbox control to display that associated state
for each record, and leave cboState blank.

i loaded an A2000 working demo, that shows both above solutions, to
http://home.att.net/~california.db/instructions.html
just scroll down to the "demo" link at the bottom of the page, to download
the file. BEFORE you open the file, make sure you change the file extension
from .bak to .mdb.

hth

Hi Tina -

I redid the form as you suggested and it almost works. The problem with the
additional column that I confused you with was that in my original post I
indicated tblDeclaration was:

tblDeclaration
DeclarationID
DeclarationNo
StateID

And it's really:

tblDeclaration
DeclarationID
DeclarationNo
Declaration
StateID

-- I forgot to add the Declaration column, which is really a description
field.

That's the field that needed to go into the textbox. I was able to get that
to work.

ONLY PROBLEM LEFT: I cannot get the records to go backwards and display
State. It is successfully displaying DeclarationNo and Declaration (the
txtbox).

I have the following code:

[CallType left out because I don't have a problem with it]

Private Sub cboDeclarationNo_AfterUpdate()
Me.txtDeclaration = Me.cboDeclarationNo.Column(2)
End Sub

Private Sub cboState_AfterUpdate()
If IsNull(Me!cboState) Then
Me!cboDeclarationNo.RowSource = "Select DeclarationID, " _
& "DeclarationNo, Declaration From tblDeclaration"
Me.Refresh
Else
Me!cboDeclarationNo.RowSource = "Select DeclarationID, " _
& "DeclarationNo, Declaration From tblDeclaration Where " _
& "tblDeclaration.StateID=[Forms]![frmCalls]![cboState]"
Me.Refresh
End If
End Sub


Private Sub Form_Current()
Me.cboState = Null
Me!cboDeclarationNo.RowSource = "Select DeclarationID, " _
& "DeclarationNo, Declaration From tblDeclaration"
Me.txtDeclaration = Me.cboDeclarationNo.Column(2)
Me.Refresh
End Sub

Any suggestions as to why State isn't displaying going backwards?

Thanks again for all of your help!
--
Sandy

comments inline.

Hi Tina -

I REALLY appreciate your help!

I still can't get the records to go backward. I had to also put in a
textbox that derives its value from cboDeclaration (the combobox with the
control source of DeclarationID). In code under cboDeclarationNo_AfterUpdate
I put:

Me.txtDeclaration = Me.cboDeclarationNo.Column(3)

in the setup that i outlined in my previous post, cboDeclarationNo has only
two columns, not four. (note that combo box columns are zero-based in
Access, so a reference to .Column(3) will return a value from the *fourth*
column in a combo box control.)

beyond that, the combo box itself will display the DeclarationNo value,
while storing the value of the DeclarationID - as it should. so i don't
understand why you want a separate unbound textbox control to again display
the DeclarationNo value.

I added the extra column to the query.

Also, I was unable to get anything to display in cboDeclarationNo when I
added the Where portion of the Select statement. I don't get it! I
inadvertently deleted that portion and only have "Select DeclarationID,
DeclarationNo, Declaration, StateID"

do you have *four* fields in tblDeclaration? your previous post only listed
three. it's probable that i gave you inappropriate settings suggestions for
the cboDeclarationNo control, based on miscommunication about the RowSource
table.

For some reason that works -- do you
know why?

Is adding the textbox and using the strange Select statement causing

it
not

to go backwards (it's stuck on the same State - whatever state's in the
record I started clicking the Previous button from is the state that
continues to show as I keep clicking Previous).

that's the usual problem with looking at existing records in a form where
one combobox's "droplist" values are dependent on the value in another combo
box - in this case complicated somewhat by the fact that the first combo box
is unbound. here's one way to solve it:

re-add the WHERE clause to the RowSource property's SELECT statement, in the
cboDeclarationNo combo box control.

add the following code to the AfterUpdate event procedure of cboState, as

If IsNull(Me!cboState) Then
Me!cboDeclarationNo.RowSource = "SELECT DeclarationID, " _
& "DeclarationNo FROM tblDeclaration"
Me.Refresh
Else
Me!cboDeclarationNo.RowSource = "SELECT DeclarationID, " _
& "DeclarationNo FROM tblDeclaration WHERE " _
& "tblDeclaration.StateID=[Forms]![frmCalls]![cboState]"
Me.Refresh
End If

(note that the SELECT statements above are based on the 3 fields you
originally posted for tblDeclaration. if the table is different, as
addressed earlier in this post, then the Select statement may be different
as well.)

add the following code to the form's Current event procedure, as

Me!cboState = Null
Me!cboDeclarationNo.RowSource = "SELECT DeclarationID, " _
& "DeclarationNo FROM tblDeclaration"
Me.Refresh

Again, thank you so much for your time (and Brains)!

well, you're welcome, but let's wait until the form is working for you,
first!

--
Sandy


:

presumably you're wanting to enter new records, or edit existing records, in
tblCalls. you don't need a query.
bind your form to tblCalls.
add an *unbound* combo box (to the Header section, maybe), with its'
RowSource (*not* ControlSource) property based on tblStates. i'll

call
the

control cboState. set the control's properties as follows:

ControlSource: <blank>
RowSource: tblStates
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

suggest you read up on the above properties in Access Help, to better
understand how the work in a combo box control.

next, make sure that the DeclarationID field (from the form's RecordSource
of tblCalls) is *bound* to a combo box control. set the control's properties
as follows:

ControlSource: DeclarationID
(this binds the control to the DeclarationID field in tblCalls.)
RowSource: SELECT DeclarationID, DeclarationNo FROM

tblDeclaration
WHERE

StateID = Forms!FormName!cboState;
(substitute the correct name of the form for FormName, of course.)
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

now what happens is that when the user selects a state from the

droplist
in

cboState, then the DeclarationID combo box is requeried, so that

only
the

declaration records with a matching state id will show in the droplist.

next, make sure that the CallTypeID field (from the form's

RecordSource
of

tblCalls) is *bound* to a combo box control. set the control's properties as
follows:

ControlSource: CallTypeID
(this binds the control to the CallTypeID field in tblCalls.)
RowSource: tblCallType
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

hth


Hello -

I have four tables related to each other:

tblStates
StateID
State

tblDeclaration
DeclarationID
DeclarationNo
StateID

tblCalls
Agent
CallerName
Phone
CallDate
[ Some other columns]
CallTypeID
DeclarationID

tblCallType
CallTypeID
CallType

I need to join these tables for a form that can be navigated

through
and

added to.
Problem: I need two combo boxes and a text box so that people

have
the

ability to select a State from the first combobox that narrows

down
the

selections for the second combobox which is DeclarationNo, which
automatically fills in the textbox with the resulting Declaration.

Please help! I've been stressing over this for quite some time.

 

[Guest]

Tina -

Thank you so much for your time and trouble. I finally have it working,
thanks to you.

I really appreciate the explicit example! It did the trick!

And . . . now that I have it up and running, I WILL thank you for your
brains too!!!
--
Sandy

Any suggestions as to why State isn't displaying going backwards?

cboState is an unbound control, so without specific code to alter it, the
control will continue to show whatever value you last chose from the
droplist, as you move from record to record. it isn't tied to the Current
record in any way.

the code i gave you specifically sets the value of cboState to Null on the
form's Current event, which is how i usually handle unbound combobox
controls that the user chooses values from.

you have two choices: to write code that sets the value of the cboState to
the value of the DeclarationID's associated state, in the form's Current
event; or to add an unbound textbox control to display that associated state
for each record, and leave cboState blank.

i loaded an A2000 working demo, that shows both above solutions, to
http://home.att.net/~california.db/instructions.html
just scroll down to the "demo" link at the bottom of the page, to download
the file. BEFORE you open the file, make sure you change the file extension
from .bak to .mdb.

hth

Hi Tina -

I redid the form as you suggested and it almost works. The problem with the
additional column that I confused you with was that in my original post I
indicated tblDeclaration was:

tblDeclaration
DeclarationID
DeclarationNo
StateID

And it's really:

tblDeclaration
DeclarationID
DeclarationNo
Declaration
StateID

-- I forgot to add the Declaration column, which is really a description
field.

That's the field that needed to go into the textbox. I was able to get that
to work.

ONLY PROBLEM LEFT: I cannot get the records to go backwards and display
State. It is successfully displaying DeclarationNo and Declaration (the
txtbox).

I have the following code:

[CallType left out because I don't have a problem with it]

Private Sub cboDeclarationNo_AfterUpdate()
Me.txtDeclaration = Me.cboDeclarationNo.Column(2)
End Sub

Private Sub cboState_AfterUpdate()
If IsNull(Me!cboState) Then
Me!cboDeclarationNo.RowSource = "Select DeclarationID, " _
& "DeclarationNo, Declaration From tblDeclaration"
Me.Refresh
Else
Me!cboDeclarationNo.RowSource = "Select DeclarationID, " _
& "DeclarationNo, Declaration From tblDeclaration Where " _
& "tblDeclaration.StateID=[Forms]![frmCalls]![cboState]"
Me.Refresh
End If
End Sub


Private Sub Form_Current()
Me.cboState = Null
Me!cboDeclarationNo.RowSource = "Select DeclarationID, " _
& "DeclarationNo, Declaration From tblDeclaration"
Me.txtDeclaration = Me.cboDeclarationNo.Column(2)
Me.Refresh
End Sub

Any suggestions as to why State isn't displaying going backwards?

Thanks again for all of your help!
--
Sandy

comments inline.

Hi Tina -

I REALLY appreciate your help!

I still can't get the records to go backward. I had to also put in a
textbox that derives its value from cboDeclaration (the combobox with the
control source of DeclarationID). In code under
cboDeclarationNo_AfterUpdate
I put:

Me.txtDeclaration = Me.cboDeclarationNo.Column(3)

in the setup that i outlined in my previous post, cboDeclarationNo has only
two columns, not four. (note that combo box columns are zero-based in
Access, so a reference to .Column(3) will return a value from the *fourth*
column in a combo box control.)

beyond that, the combo box itself will display the DeclarationNo value,
while storing the value of the DeclarationID - as it should. so i don't
understand why you want a separate unbound textbox control to again display
the DeclarationNo value.


I added the extra column to the query.

Also, I was unable to get anything to display in cboDeclarationNo when I
added the Where portion of the Select statement. I don't get it! I
inadvertently deleted that portion and only have "Select DeclarationID,
DeclarationNo, Declaration, StateID"

do you have *four* fields in tblDeclaration? your previous post only listed
three. it's probable that i gave you inappropriate settings suggestions for
the cboDeclarationNo control, based on miscommunication about the RowSource
table.

For some reason that works -- do you
know why?

Is adding the textbox and using the strange Select statement causing it
not
to go backwards (it's stuck on the same State - whatever state's in the
record I started clicking the Previous button from is the state that
continues to show as I keep clicking Previous).

that's the usual problem with looking at existing records in a form where
one combobox's "droplist" values are dependent on the value in another combo
box - in this case complicated somewhat by the fact that the first combo box
is unbound. here's one way to solve it:

re-add the WHERE clause to the RowSource property's SELECT statement, in the
cboDeclarationNo combo box control.

add the following code to the AfterUpdate event procedure of cboState, as

If IsNull(Me!cboState) Then
Me!cboDeclarationNo.RowSource = "SELECT DeclarationID, " _
& "DeclarationNo FROM tblDeclaration"
Me.Refresh
Else
Me!cboDeclarationNo.RowSource = "SELECT DeclarationID, " _
& "DeclarationNo FROM tblDeclaration WHERE " _
& "tblDeclaration.StateID=[Forms]![frmCalls]![cboState]"
Me.Refresh
End If

(note that the SELECT statements above are based on the 3 fields you
originally posted for tblDeclaration. if the table is different, as
addressed earlier in this post, then the Select statement may be different
as well.)

add the following code to the form's Current event procedure, as

Me!cboState = Null
Me!cboDeclarationNo.RowSource = "SELECT DeclarationID, " _
& "DeclarationNo FROM tblDeclaration"
Me.Refresh


Again, thank you so much for your time (and Brains)!

well, you're welcome, but let's wait until the form is working for you,
first!

--
Sandy


:

presumably you're wanting to enter new records, or edit existing
records, in
tblCalls. you don't need a query.
bind your form to tblCalls.
add an *unbound* combo box (to the Header section, maybe), with its'
RowSource (*not* ControlSource) property based on tblStates. i'll call
the
control cboState. set the control's properties as follows:

ControlSource: <blank>
RowSource: tblStates
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

suggest you read up on the above properties in Access Help, to better
understand how the work in a combo box control.

next, make sure that the DeclarationID field (from the form's
RecordSource
of tblCalls) is *bound* to a combo box control. set the control's
properties
as follows:

ControlSource: DeclarationID
(this binds the control to the DeclarationID field in tblCalls.)
RowSource: SELECT DeclarationID, DeclarationNo FROM tblDeclaration
WHERE
StateID = Forms!FormName!cboState;
(substitute the correct name of the form for FormName, of course.)
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

now what happens is that when the user selects a state from the droplist
in
cboState, then the DeclarationID combo box is requeried, so that only
the
declaration records with a matching state id will show in the droplist.

next, make sure that the CallTypeID field (from the form's RecordSource
of
tblCalls) is *bound* to a combo box control. set the control's
properties as
follows:

ControlSource: CallTypeID
(this binds the control to the CallTypeID field in tblCalls.)
RowSource: tblCallType
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

hth


Hello -

I have four tables related to each other:

tblStates
StateID
State

tblDeclaration
DeclarationID
DeclarationNo
StateID

tblCalls
Agent
CallerName
Phone
CallDate
[ Some other columns]
CallTypeID
DeclarationID

tblCallType
CallTypeID
CallType

I need to join these tables for a form that can be navigated

through

 

[tina]

me and my brain both say "you're welcome"

Tina -

Thank you so much for your time and trouble. I finally have it working,
thanks to you.

I really appreciate the explicit example! It did the trick!

And . . . now that I have it up and running, I WILL thank you for your
brains too!!!
--
Sandy

Any suggestions as to why State isn't displaying going backwards?

cboState is an unbound control, so without specific code to alter it, the
control will continue to show whatever value you last chose from the
droplist, as you move from record to record. it isn't tied to the Current
record in any way.

the code i gave you specifically sets the value of cboState to Null on the
form's Current event, which is how i usually handle unbound combobox
controls that the user chooses values from.

you have two choices: to write code that sets the value of the cboState to
the value of the DeclarationID's associated state, in the form's Current
event; or to add an unbound textbox control to display that associated state
for each record, and leave cboState blank.

i loaded an A2000 working demo, that shows both above solutions, to
http://home.att.net/~california.db/instructions.html
just scroll down to the "demo" link at the bottom of the page, to download
the file. BEFORE you open the file, make sure you change the file extension
from .bak to .mdb.

hth

Hi Tina -

I redid the form as you suggested and it almost works. The problem

with
the

additional column that I confused you with was that in my original post I
indicated tblDeclaration was:

tblDeclaration
DeclarationID
DeclarationNo
StateID

And it's really:

tblDeclaration
DeclarationID
DeclarationNo
Declaration
StateID

-- I forgot to add the Declaration column, which is really a description
field.

That's the field that needed to go into the textbox. I was able to

get
that

to work.

ONLY PROBLEM LEFT: I cannot get the records to go backwards and display
State. It is successfully displaying DeclarationNo and Declaration (the
txtbox).

I have the following code:

[CallType left out because I don't have a problem with it]

Private Sub cboDeclarationNo_AfterUpdate()
Me.txtDeclaration = Me.cboDeclarationNo.Column(2)
End Sub

Private Sub cboState_AfterUpdate()
If IsNull(Me!cboState) Then
Me!cboDeclarationNo.RowSource = "Select DeclarationID, " _
& "DeclarationNo, Declaration From tblDeclaration"
Me.Refresh
Else
Me!cboDeclarationNo.RowSource = "Select DeclarationID, " _
& "DeclarationNo, Declaration From tblDeclaration Where " _
& "tblDeclaration.StateID=[Forms]![frmCalls]![cboState]"
Me.Refresh
End If
End Sub


Private Sub Form_Current()
Me.cboState = Null
Me!cboDeclarationNo.RowSource = "Select DeclarationID, " _
& "DeclarationNo, Declaration From tblDeclaration"
Me.txtDeclaration = Me.cboDeclarationNo.Column(2)
Me.Refresh
End Sub

Any suggestions as to why State isn't displaying going backwards?

Thanks again for all of your help!
--
Sandy


:

comments inline.

Hi Tina -

I REALLY appreciate your help!

I still can't get the records to go backward. I had to also put in a
textbox that derives its value from cboDeclaration (the combobox

with
the

control source of DeclarationID). In code under
cboDeclarationNo_AfterUpdate
I put:

Me.txtDeclaration = Me.cboDeclarationNo.Column(3)

in the setup that i outlined in my previous post, cboDeclarationNo

has
only

two columns, not four. (note that combo box columns are zero-based in
Access, so a reference to .Column(3) will return a value from the *fourth*
column in a combo box control.)

beyond that, the combo box itself will display the DeclarationNo value,
while storing the value of the DeclarationID - as it should. so i don't
understand why you want a separate unbound textbox control to again display
the DeclarationNo value.


I added the extra column to the query.

Also, I was unable to get anything to display in cboDeclarationNo

when
I

added the Where portion of the Select statement. I don't get it! I
inadvertently deleted that portion and only have "Select DeclarationID,
DeclarationNo, Declaration, StateID"

do you have *four* fields in tblDeclaration? your previous post only listed
three. it's probable that i gave you inappropriate settings

suggestions
for

the cboDeclarationNo control, based on miscommunication about the RowSource
table.

For some reason that works -- do you
know why?

Is adding the textbox and using the strange Select statement

causing
it

not
to go backwards (it's stuck on the same State - whatever state's

in
the

record I started clicking the Previous button from is the state that
continues to show as I keep clicking Previous).

that's the usual problem with looking at existing records in a form where
one combobox's "droplist" values are dependent on the value in

another
combo

box - in this case complicated somewhat by the fact that the first

combo
box

is unbound. here's one way to solve it:

re-add the WHERE clause to the RowSource property's SELECT

statement, in
the

cboDeclarationNo combo box control.

add the following code to the AfterUpdate event procedure of

cboState,
as

If IsNull(Me!cboState) Then
Me!cboDeclarationNo.RowSource = "SELECT DeclarationID, " _
& "DeclarationNo FROM tblDeclaration"
Me.Refresh
Else
Me!cboDeclarationNo.RowSource = "SELECT DeclarationID, " _
& "DeclarationNo FROM tblDeclaration WHERE " _
& "tblDeclaration.StateID=[Forms]![frmCalls]![cboState]"
Me.Refresh
End If

(note that the SELECT statements above are based on the 3 fields you
originally posted for tblDeclaration. if the table is different, as
addressed earlier in this post, then the Select statement may be different
as well.)

add the following code to the form's Current event procedure, as

Me!cboState = Null
Me!cboDeclarationNo.RowSource = "SELECT DeclarationID, " _
& "DeclarationNo FROM tblDeclaration"
Me.Refresh


Again, thank you so much for your time (and Brains)!

well, you're welcome, but let's wait until the form is working for you,
first!

--
Sandy


:

presumably you're wanting to enter new records, or edit existing
records, in
tblCalls. you don't need a query.
bind your form to tblCalls.
add an *unbound* combo box (to the Header section, maybe), with its'
RowSource (*not* ControlSource) property based on tblStates.

i'll
call

the
control cboState. set the control's properties as follows:

ControlSource: <blank>
RowSource: tblStates
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

suggest you read up on the above properties in Access Help, to better
understand how the work in a combo box control.

next, make sure that the DeclarationID field (from the form's
RecordSource
of tblCalls) is *bound* to a combo box control. set the control's
properties
as follows:

ControlSource: DeclarationID
(this binds the control to the DeclarationID field in tblCalls.)
RowSource: SELECT DeclarationID, DeclarationNo FROM tblDeclaration
WHERE
StateID = Forms!FormName!cboState;
(substitute the correct name of the form for FormName, of course.)
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

now what happens is that when the user selects a state from the droplist
in
cboState, then the DeclarationID combo box is requeried, so that only
the
declaration records with a matching state id will show in the droplist.

next, make sure that the CallTypeID field (from the form's RecordSource
of
tblCalls) is *bound* to a combo box control. set the control's
properties as
follows:

ControlSource: CallTypeID
(this binds the control to the CallTypeID field in tblCalls.)
RowSource: tblCallType
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1

hth


Hello -

I have four tables related to each other:

tblStates
StateID
State

tblDeclaration
DeclarationID
DeclarationNo
StateID

tblCalls
Agent
CallerName
Phone
CallDate
[ Some other columns]
CallTypeID
DeclarationID

tblCallType
CallTypeID
CallType

I need to join these tables for a form that can be navigated

through