By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,959 Members | 1,139 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,959 IT Pros & Developers. It's quick & easy.

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

P: 4
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
Oct 29 '06 #1
Share this Question
Share on Google+
11 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Oct 29 '06 #2

NeoPa
Expert Mod 15k+
P: 31,343
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).
Oct 29 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 29 '06 #4

P: 4
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
Oct 30 '06 #5

NeoPa
Expert Mod 15k+
P: 31,343
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...
Oct 30 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim strSQL As String
  3.  
  4. strSQL="INSERT INTO Lookup_Value (sValue) VALUES ('" & Me.txtNewValue & "');"
  5. DoCmd.RunSQL
  6.  
  7. Me.lstBoxName.Requery    ' This will requery the listbox with the new value
  8.  
  9.  
Oct 30 '06 #7

P: 4
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.

Expand|Select|Wrap|Line Numbers
  1. Dim dbs As Database
  2.  
  3.     Set dbs = CurrentDb
  4.  
  5.     dbs.Execute "UPDATE Table_Complications " _
  6.         & "SET Cardiovascular = 'NewValue' " _
  7.         & "WHERE Cardiovascular = ‘xxxx’;"
  8.  
  9.     dbs.Close
  10.  
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:

Expand|Select|Wrap|Line Numbers
  1. Dim dbs As Database
  2.  
  3.     Set dbs = CurrentDb
  4.  
  5.     dbs.Execute "UPDATE Table_Complications " _
  6.         & "SET Cardiovascular = 'NewValue' " _
  7.         & "WHERE Cardiovascular = Null;"
  8.  
  9.     dbs.Close
  10.  
  11. End Sub
  12.  
And I have tried variations such as ...

Expand|Select|Wrap|Line Numbers
  1. & 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)?
Nov 1 '06 #8

NeoPa
Expert Mod 15k+
P: 31,343
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?
Nov 1 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. Dim dbs As Database
  2.  
  3. Set dbs = CurrentDb
  4.  
  5. dbs.Execute "UPDATE Table_Complications " _
  6. & "SET Cardiovascular = 'NewValue' " _
  7. & "WHERE Cardiovascular = '" & "" & "';"
  8.  
  9. dbs.Close
  10.  
  11.  
Nov 1 '06 #10

P: 4
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
Nov 6 '06 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 7 '06 #12

Post your reply

Sign in to post your reply or Sign up for a free account.