Updating MS Access with User Input 1

[bsawers]

Hi, I'm new to Access, databases, and this forum. I have a table called STNLINE with 2 fields, IDSTD and WINGSPAN_FT. I want the user to be able to update the field WINGSPAN_FT with whatever value they wish based on the value in the field IDSTD...Whats the easiest way I can do this?

 

[bsawers]

I was quoted earlier as saying "it doesnt have to update more than one record at a time". My supervisor now wants all like values ie. (all L1 values in the IDLIN column) that when selected will update all corresponsing values in the WINGSPAN_ID column.

For example:

IDLIN WINGSPAN_ID

L1 10
L1 10
L1 10

I have built a form and added a combo box that allows the user to select from the column IDLIN. I have also built a text box what is bound to the column WINGSPAN_ID...When the user selects L1 from the combo box, the value 10 appears in the WINGSPAN_ID textbox...The user can then edit that 10 (maybe he inputs 100) but instead of editing all values of L1, it only edits the first value..

For example:

IDLIN WINGSPAN_ID

L1 100
L1 10
L1 10

What I would like is:

IDLIN WINGSPAN_ID

L1 100
L1 100
L1 100

Thanks again.

 

[PHV]

In the AfterUpdate event procedure of your textbox:

DoCmd.RunSQL "UPDATE TXILINE SET WINGSPAN_ID='" & Me![name of textbox] & "' WHERE IDLIN=' & Me![name of combo].Column(1) & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I think PH missed a double-quote

DoCmd.RunSQL "UPDATE TXILINE SET WINGSPAN_ID='" & Me![name of textbox] & "' WHERE IDLIN='[b][COLOR=red yellow]"[/color][/b] & Me![name of combo].Column(1) & "'"

The code assumes both of these fields are text and not numeric.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Good catch Duane, thanks.

 

[bsawers]

Just to gain a better understanding, what is the significance of .Column(1)?

 

[dhookom]

A combo box can have more than one column. For instance a Row Source of the following can create 2 columns:
[tt][blue]
SELECT StateCode, StateName FROM tblStates;[/blue][/tt]
If you want to reference the value of the Bound Column, you can use an expression like:
cboStates
If you want to reference a non-bound column, you can reference its column number. The column numbering is zero-based so cboStates.Column(1) would return the StateName value.



Duane
Hook'D on Access
MS Access MVP

 

[bsawers]

One quick question, for my combo box the row source is:

SELECT Min(MSLINK),IDSTD FROM STNDAREA GROUP BY IDSTD;

Is there any way I can order them further? Perhaps sort by Ascending or Decending so when the user drops down the list the values are not only grouped but sorted as well.

thanks

 

[PHV]

SELECT Min(MSLINK),IDSTD FROM STNDAREA GROUP BY IDSTD ORDER BY IDSTD

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886