Urgent! only first sorted record gets updated

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

There is no code in any of the events with in this form. The name in the
Control Source is the field name of the table = Ret_Military.

This is the sequence of events:
1.) first go into the personal data base
2.) Select a name from the data base
3.) press button to go into the personnel Information
4.) answer a Y or N question on one of the combox fields (works fine and
writes corrently to the table) uses a query to sort:
SELECT DISTINCTROW tblPersstatistics.*, tblPersstatistics.LAST_NAME AS
LastNameIndex, tblPersstatistics.FIRST_NAME AS FirstNameIndex
FROM tblPersstatistics
ORDER BY tblPersstatistics.LAST_NAME, tblPersstatistics.FIRST_NAME;

5.) If you answer Y to the previous question then another combox comes up.
Uses the same query again:
SELECT DISTINCTROW tblPersstatistics.*, tblPersstatistics.LAST_NAME AS
LastNameIndex, tblPersstatistics.FIRST_NAME AS FirstNameIndex
FROM tblPersstatistics
ORDER BY tblPersstatistics.LAST_NAME, tblPersstatistics.FIRST_NAME;
6.) Select from the value list as indecated from below (This does not work
and only writes to the first sorted record)

The name and the control name are different:
Name = cbxMilitary
Control Source = Ret_Military(field name on table)


I use the Row Source Type = Value List
And the Row Source = '''';''Army";"Navy";"Air Force"
each of these comes up fine in the pull down and select of the combox, but
again it only writes to the first sorted record.

What am I doing wrong.

could it be that because I go through two comboxes that this might be
tripping to the first sorted record? and if so then how can I get around it.

Please note that I have another question out there that is the same, but
because I don't get a response until the evenings and I work during the day I
need to resolve this problem quickly for I am on a dead line.

Please help Thank You!
 
Your post does not provide details about the form's RecordSource query, so
it's not clear what is being updated by your process.

You have posted the queries of combo boxes' Row Source properties (though I
am confused by your later comment about using a Value List instead of a
query).

Please provide more details about the form's setup and operation. It is
possible that you are misinterpreting what type of update would be done by
your form -- forms will update one record at a time, not multiple records.
To do multiple records, you'd need to run some type of update query or
programming to do multiple updates.
 
have a form with a Y or N question. When I select Y I bring up a combox
form were you select which branch.

both forms are attached to the same table

For some reason only the first sorted record gets updated with the branch
that you choose.

example:
Select the Personnel button
select a name smith
press the personnel information button
Personnel information form comes up

Select a y or n option on the form (Y or N gets written to the selected
record
correctly) by using
row source Type = value list
Row Source "";"Y";"N"

Query SQL
SELECT DISTINCTROW tblPersstatistics.*, tblPersstatistics.LAST_NAME AS
LastNameIndex, tblPersstatistics.FIRST_NAME AS FirstNameIndex
FROM tblPersstatistics
ORDER BY tblPersstatistics.LAST_NAME, tblPersstatistics.FIRST_NAME;

VBA Statement
Private Sub cbxRet_MIL_Click()
Dim Picky As String
Picky = Me!cbxRet_MIL & vbNullString
If Me.Dirty Then
Me.Dirty = False
End If
If Picky = "Y" Then
DoCmd.OpenForm "frmPersMilitary", acNormal
Else
Me.[Military] = Null
End If
End Sub

When you select Y a combox comes up

Select which branch you want (only the first sorted record gets updated)
using
row source Type = value list
Row Source "Army";"Navy";"Air Force"

Query SQL
SELECT DISTINCTROW tblPersstatistics.*
FROM tblPersstatistics

I want only the record selected for the field of the branch to be updated
that you have
selected

I have a select query for the first form (this works correctly Y or N
question)
I have another select query for the second form (combox) (this does not work
correctly) only updates first sorted record.

I only want one record at a time to be updated

I hope this is enough information
Please help thank You!
 
Thank You very much it seams like I solved it by this statement:

LinkCriteria = "[ssn] = forms![tblmain]![ssn]"
docmd.Openform docname,,,LinkCriteria


Amour said:
have a form with a Y or N question. When I select Y I bring up a combox
form were you select which branch.

both forms are attached to the same table

For some reason only the first sorted record gets updated with the branch
that you choose.

example:
Select the Personnel button
select a name smith
press the personnel information button
Personnel information form comes up

Select a y or n option on the form (Y or N gets written to the selected
record
correctly) by using
row source Type = value list
Row Source "";"Y";"N"

Query SQL
SELECT DISTINCTROW tblPersstatistics.*, tblPersstatistics.LAST_NAME AS
LastNameIndex, tblPersstatistics.FIRST_NAME AS FirstNameIndex
FROM tblPersstatistics
ORDER BY tblPersstatistics.LAST_NAME, tblPersstatistics.FIRST_NAME;

VBA Statement
Private Sub cbxRet_MIL_Click()
Dim Picky As String
Picky = Me!cbxRet_MIL & vbNullString
If Me.Dirty Then
Me.Dirty = False
End If
If Picky = "Y" Then
DoCmd.OpenForm "frmPersMilitary", acNormal
Else
Me.[Military] = Null
End If
End Sub

When you select Y a combox comes up

Select which branch you want (only the first sorted record gets updated)
using
row source Type = value list
Row Source "Army";"Navy";"Air Force"

Query SQL
SELECT DISTINCTROW tblPersstatistics.*
FROM tblPersstatistics

I want only the record selected for the field of the branch to be updated
that you have
selected

I have a select query for the first form (this works correctly Y or N
question)
I have another select query for the second form (combox) (this does not work
correctly) only updates first sorted record.

I only want one record at a time to be updated

I hope this is enough information
Please help thank You!

Ken Snell (MVP) said:
Your post does not provide details about the form's RecordSource query, so
it's not clear what is being updated by your process.

You have posted the queries of combo boxes' Row Source properties (though I
am confused by your later comment about using a Value List instead of a
query).

Please provide more details about the form's setup and operation. It is
possible that you are misinterpreting what type of update would be done by
your form -- forms will update one record at a time, not multiple records.
To do multiple records, you'd need to run some type of update query or
programming to do multiple updates.
Click to expand...
Click to expand...
 
Back
Top