first record gets updated

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

Guest

I 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 form 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 each 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.*, tblPersstatistics.STATUS
FROM tblPersstatistics
ORDER BY tblPersstatistics.STATUS;

I want all records 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 hope this is enough information
Please help thank You!
 
I 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 form are attached to the same table

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

If you're using controls to select records, they MUST be *UNBOUND* -
have nothing in their Control Source property.

You may want to have two sets of controls - one, unbound, in the form
header for searching; and a separate set of controls, bound, on the
form for data editing and display.

John W. Vinson[MVP]
 
I am sorry I am new to MS-Access and don't understand. I have no entry in
the Control Source and it does say Unbound. But now there is nothing on the
Y or N question coming back on the form. The problem I am having is that it
only writes to the first sorted record and does not write to the other
records.

Ex:
I smith is the first sorted record it writes the field that is in question

but the next record say jones it does not write the field in question

If you can help Thank You!
 
I don't know what you mean by:
You may want to have two sets of controls - one, unbound, in the form
header for searching; and a separate set of controls, bound, on the
form for data editing and display.

I thought you said nothing in the control Source.

Also I have this form that has a lot of combox's in that form yet all have
some naming in the control source option and they all write correctly.

And I have the Y or N option(combox) with a name in the Control Source
Option and that also writes correctly to the table.

I have noticed that I have one combox (Y or N) and that is calling another
combox. Could it be that you can not open one combox and still have another
combox open? If so could you tell me how to get around it.

I think maybe I am miss understanding your solution...

Please help Thank You!
 
Sorry I am new to this, but after investiagating I noticed that even though I
am on another record when I change the field in question it does not change
for that record, but it does change the first sorted record which is not the
current record I am on.
Ex:
Smith is the first sorted record
I go into say Waltons record and change the field
or say I go into Wester record and change that field

The only field it changes (and does not add) is the first record Smith

Please Help Thank You Very Much!
 
The only field it changes (and does not add) is the first record Smith
Click to expand...

In that case, your Combo Box is bound to a field in the first record,
or there's something in your code writing this data.

Please open your form in design view. Check the Properties of this
combo box. What is its Control Source property? Is there any code in
any of the Events? If so, please post the code here.

John W. Vinson[MVP]
 
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 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
 
Back
Top