Change any one value in a table in Access from a form

[redantho]

As a relative beginner with Access/VBA (2003 version), I am looking for a solution to what seems to be a fairly simple problem...

When a user inputs a text string in a form object, I would like that string to become a new value in a table which already consists of a variety of fields and records. I don't want a new RECORD to be created, but would like to place this new value (as entered by the user) in any particular box I choose in the table.

For example, if field number 2 in my table has three values listed in its column in the records below, and field number 3 has 10 values in its column, I might want to add the user-entered text string as the fourth value in column 2, without appending an entire new record to the bottom of the table.

Help or guidance on this is appreciated...

Anthony

[MMcCarthy]

On your form you have a textbox in which the user enters data. Do you also have a field on the form that corresponds to the field on the table that you want this data to go to?

Is your form bound to this table. i.e. Is the Record Souce of your form based on this table?

[NeoPa]

If you're talking about dynamically adding fields to a table definition on the fly within a form, then perhaps you don't appreciate how easy (possible) this isn't.

Assuming I understand you correctly, then don't even go there in Access. It won't be worth your pain (assuming it's even possible).

[MMcCarthy]

If you're talking about dynamically adding fields to a table definition on the fly within a form, then perhaps you don't appreciate how easy (possible) this isn't.

Assuming I understand you correctly, then don't even go there in Access. It won't be worth your pain (assuming it's even possible).

It's possible to add a field using vba but for obvious reasons its fraught with danger. If you add a field to a table then where is that field used and/or referenced. What implications does it have throughout the database? The rule on something like this is don't do it unless you've explored all other ways of solving your problem.

If like NeoPa says this is what you are trying to do the WHY? There may be a better way.

[redantho]

Thanks for the replies. I will try to explain my purpose and perhaps there is a simpler way to achieve what I want to do…

The tables that I want to place values in (see previous post) are not tables set up for the purpose of listing “records” in. I am simply using the tables as store-houses of lists for combo-boxes.

There are probably as many as 50 or 60 different unique lists (found under fields in a selection of tables) which may appear as choices for a particular combo-box on my form depending on choices made in other combo-boxes. I am just using the fields in these tables as a convenient way of being able to view and edit my various lists that can appear as choices in a combo-box (because there are so many lists).

So these tables that I want to alter values in are not meant to contain “records” as such, rather lists for combo-boxes for the user to make a choice from. The actual choice made by the user from a combo-box IS stored as a proper record in another table.

What I really want to do is allow a user to ADD a unique choice for a particular combo-box by typing in a new string which could then be added as a new value under a field on one of those “store-house” tables therefore permanently added to the list of choices for that combo-box.

Have I confused anyone yet?!? Any thoughts on how this might be possible or should I try a different technique?

Anthony

[NeoPa]

I confess to being a little confused still, even though you have explained it quite well.
Do you have a separate table for each List / Combo-Box?
If so, why do you say you are not adding a record?
You mention fields, but this is where I come a little unstuck, surely each List / Combo-Box has a fixed number of fields...

[MMcCarthy]

Hi NeoPa

I think the problem is the old communication one again. Maybe we should post a sticky as to what is a field, record, control, etc.

Anyway,

To add a record to any table you can use the following (adding a value to a lookup list IS adding a record to a table).

For this example, I am naming my table Lookup_Value and the fields are an ID (Autonumber) field which gets populated automatically and a field called sValue. The textbox on the form that the user enters the value into is txtNewValue.

 
Dim strSQL As String
 
strSQL="INSERT INTO Lookup_Value (sValue) VALUES ('" & Me.txtNewValue & "');"
DoCmd.RunSQL
 
Me.lstBoxName.Requery    ' This will requery the listbox with the new value
 
 

[redantho]

Can I try a different tack with this problem? Here are two columns in my table:

(Field 1)
CARDIOVASCULAR
Atrial Fibrillation
Myocardial Infarction
Acute pulmonary oedema
xxxx

(Field 2)
RESPIRATORY
Pneumonia
Pneumothorax
Pleural Effusion
Haemothorax
Respiratory Failure
Pulmonary Embolus

If I want to change the value xxxx under field "CARDIOVASCULAR" I can use the following VBA code which works well. "NewValue" is a value entered by a user in a text box.

Dim dbs As Database
 
    Set dbs = CurrentDb
 
    dbs.Execute "UPDATE Table_Complications " _
        & "SET Cardiovascular = 'NewValue' " _
        & "WHERE Cardiovascular = ‘xxxx’;"
 
    dbs.Close
 

If, however, 'xxxx' is actually an empty cell (like the ones below it) but I want to put "NewValue" in that spot, I don't know how to do it from VBA ... I have tried the following which doesn't seem to work:

Dim dbs As Database
 
    Set dbs = CurrentDb
 
    dbs.Execute "UPDATE Table_Complications " _
        & "SET Cardiovascular = 'NewValue' " _
        & "WHERE Cardiovascular = Null;"
 
    dbs.Close
 
End Sub
 

And I have tried variations such as ...

& WHERE IsEmpty(Cardiovascular) = True;"

But I can't seem to get the table to alter at all with these experiments.

Is there a selection of code which can add my user's new string to the bottom of the Cardiovascular list in an empty cell (without adding a new record to the table as such)?

[NeoPa]

Unless I misunderstand badly (I've done it before), the data structure here is quite wrong.
Let me explain.
It seems you have a table 'Table_Complications' which has two separate fields in it 'Cardiovascular' & 'Respiratory'.
It appears that you're storing disparate values in the same record - 'Atrial Fibrilation' in the same record as 'Pneumonia'.
These are not logically connected, so should not be stored together.

Can you please confirm I'm reading this correctly here?

[MMcCarthy]

I agree with NeoPa that your structure is incorrect. However, if you use the below code it should update ALL empty fields to the new value.

Dim dbs As Database
 
Set dbs = CurrentDb
 
dbs.Execute "UPDATE Table_Complications " _
& "SET Cardiovascular = 'NewValue' " _
& "WHERE Cardiovascular = '" & "" & "';"
 
dbs.Close
 
 

[redantho]

I have tried a different approach to my problem which includes changing my data structure somewhat and have found a solution which works.

Thanks for the help and advice.

Anthony

[MMcCarthy]

I have tried a different approach to my problem which includes changing my data structure somewhat and have found a solution which works.

Thanks for the help and advice.

Anthony

You're welcome. Please repost if you have any further problems.