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]

For further info. I was trying to use the update query. Heres my SQL

UPDATE TXILINE SET TXILINE.WINGSPAN_FT = [Enter new WINGSPAN:]
WHERE (((TXILINE.IDLIN)=[Select* From IDLIN]));

 

[dhookom]

table called STNLINE with 2 fields, IDSTD and WINGSPAN_FT

Where did TXILINE and IDLIN come from.
Why not just create a bound form where the user can navigate to the record and update the value?

Does this code possibly update more than on record at a time?

IMO, parameter prompts are never good user interface. Use controls on forms for all user interation.

Duane
Hook'D on Access
MS Access MVP

 

[bsawers]

Thanks for the reply

I figured a form would work best...The problem is I don't know how to code the form as my programming abilities are tepid at best...but I do know how to design the form. Do you have any example code which I could utilize or at least, point me in the right direction?

To answer your questions, no the code doesnt have to update more than one record at a time.

thanks.

 

[dhookom]

Create a form bound to your table or query. Allow the user to open the form and find the record to edit. They can then enter the appropriate value. There is a combo box wizard that makes it easy to find a specific record in a form.

Duane
Hook'D on Access
MS Access MVP

 

[bsawers]

Hi dhookom

I added a combo box to my form which is linked to the Field IDLIN from the table TXILINE. The combo box now displays all the data from the field IDLIN from table TXILINE. After that I created a textbox and bound that textbox to the field IDLIN from the table TXILINE. Now when I run the form I can select any value listed in the combo box and it displays the correspoding WINGSPAN_FT values for me. My question is how can I update the WINGSPAN_FT field with any value the user enters into the textbox on the form? Do I build an event, perhaps using the Expression builder? Or code builder? If so what expression would make this work?

Thanks alot

Bryan

 

[dhookom]

The combo box should not have a Control Source. The form should have a Record Source of your TSILINE table. The combo box should have been created with the wizard to "find a record..."

Are the above statements true? If so, the text box bound to WINGSPAN_FT should allow users to edit the value in the table corresponding to the value selected in the combo box.

Duane
Hook'D on Access
MS Access MVP

 

[bsawers]

Thanks for the help...It seems to be working but the one last thing I would like would be to group the values in the combo box drop down list so that they dont repeat...For example I dont want L1, L1, L1, L1, but rather grouping them together...Saying that, when I right click the combo box and select properties I can edit the Raw Source SQL statement.

So far what I have, which doesnt seem to be working is;


SELECT [TXILINE].[MSLINK], [TXILINE].[IDLIN]
FROM [TXILINE]
GROUP BY [TXILINE].[MSLINK], [TXILINE].[IDLIN]
ORDER BY [TXILINE].[MSLINK], [TXILINE].[IDLIN];

 

[PHV]

What is the actual SQL code of the RowSource property of the unbound search combo and which field hold the 'L1' value ?

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

 

[bsawers]

Its

SELECT DISTINCT [TXILINE].[MSLINK], [TXILINE].[IDLIN] FROM [TXILINE];

The combo box drop down allows me to view and select all values from the IDLIN field within the TXILINE table, but they aren't distinc values.

 

[PHV]

So, why not using this ?
SELECT DISTINCT IDLIN FROM TXILINE

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

 

[bsawers]

When I do that it says I'm missing the MSLINK...

 

[dhookom]

I'm just confused since it seems table and field names are changing continuously.

Duane
Hook'D on Access
MS Access MVP

 

[bsawers]

When I use

SELECT DISTINCT IDLIN FROM TXILINE;

it populates the combo box with no values...It appears that I need the MSLINK field in my query for any values to populate my combo box...it must be noted that MSLINK field contains only unique values...When I use

SELECT DISTINCT TXILINE.MSLINK, TXILINE.IDLIN FROM TXILINE;

it populates my form combo box with values from the field IDLIN but not unique values...

 

[dhookom]

What are these properties of the combo box:
Row Source:
Bound Column:
Column Count:
Column Widths:

These are all inter-related and determine what is displayed and what is selected.

Duane
Hook'D on Access
MS Access MVP

 

[bsawers]

Row Source: SELECT DISTINCT [TXILINE].[MSLINK], [TXILINE].[IDLIN] FROM [TXILINE];


Bound Column: 1

Column Count: 2

Column Widths: 0";1

 

[PHV]

What about this RowSource ?
SELECT Min(MSLINK),IDLIN FROM TXILINE GROUP BY IDLIN

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

 

[bsawers]

Yes it works...Thanks alot!

 

[bsawers]

Now I have a combo box in a form which displays unique values from field IDLIN.

I also have a textbox on the form which is bound to the column WINGSPAN_ID and when a value is selected in the combo box (lets say L1 is selected) the corresponding value in WINGSPAN_ID is displayed in the text box...

The user can edit the value of WINGSPAN_ID in the text box to whatever they like...the problem is that it doesnt edit all values of L1, just one value...I would like it to edit all values of L1. Thanks.

 

[dhookom]

no the code doesnt have to update more than one record at a time

problem is that it doesnt edit all values of L1

Do you mind providing the big picture of your data and what you want to do?

Duane
Hook'D on Access
MS Access MVP