Update field in table based on form field 1

[jallen919]

I have a form that allows a new code to be created (in the Codes table) or look for an existing code (in the Codes table).

I have a table of imported records and for each record there is a blank in the "Code" column. I want to insert a code into every record (the same code for each record) and want to base it on the code currently being shown on the form mentioned above.

Any suggestions?

 

[ghubbell]

Hi J Allen,
Yes indeed and these ones are fun to watch run: An update query. Make a backup copy of your table just in case...

Make a simple query that is based off of the table with your imported records. Drag your code field to the grid. In the criteria row your criteria should look something like: Forms![NameofForm]![NameofFieldWithBadCode]

You can even run the query now (with data in the form) just as a test.

Now choose Query type from the tool bar- Update Query.
You just gained a row in the grid that says "Update to".
In this row underneath your code field you should have something like: Forms![NameOfForm]![NameofFieldWithGoodCode]

Again, you can run a test (with data in the form), by clicking the query datasheet view, Not the ! button which will commit the action. You won't see the change, but you should see if anything "disagrees" with Access.

Save the query.

I would put a command button on the form to do this but you could certainly run it in the after update event of both fields on the form. Something like:

On error goto Err1

If IsNull(Me![NameofFieldWithGoodCode]) Or Me![NameofFieldWithGoodCode] = "" Then
MsgBox "There's nothing in the good code!",vbinformation
Exit sub
end if
'optional depending on your needs
If IsNull(Me![NameofFieldWithBadCode]) Or Me![NameofFieldWithBadCode] = "" Then
MsgBox "There's nothing in the bad code!",vbinformation
Exit sub
end if
'end of optional
DoCmd.setWarnings False 'if you don't want to see messages
Docmd.OpenQuery "NameofyournewQuery"

Exit1:
Docmd.setWarnings True
me.Requery
Exit Sub

Err1:
Msgbox Err.Number & " " & Err.Description,VbInformation,"Ooops"
Resume Exit1

The official way to do this if you want to run it from the after update of either field is to make it a "Private Sub" on its own: Private Sub FixMyCode()

'all the stuff above in here before the "End Sub"

Then in the After update event of each field (in visual basic as well), type in: FixMyCode

Should do it but best is to try on a copy of your table to make sure nothing blows up! ;-) Gord
ghubbell@total.net

 

[jallen919]

I searched the knowledge base extensively before I posted the question and found nothing. I am trying to do something that is out of Access database design norm or does everyone else just know how to do it your opinion is once again greatly valued. Thank you.

 

[jallen919]

Alright I read through your instructions and don't understand the difference between the Forms!formname!NameOfFieldwithBadCode and NameOfFieldwithGoodCode

my criteria and update fields look like
Forms!frmLeadSource!txtLeadSource -- where txtLeadSource is the name of the text box on the form.

Is there a way to "simply" capture the value of the text box, assign it to a variable and then reference that variable in a query? I researched that too but didn't find a solution.

 

[ghubbell]

Not at all J. This is a pretty normal thing to do but update queries are "wicked" powerful tools as with all the other action queries and are (maybe for that reason) not fully explained in many ways. As above, I say they are fun to watch run on their own, knowing that at the click of a button you are doing something that not too long ago would have taken someone days, maybe weeks to do. Really serious about the backup thing though. I've done it just as easy too: destroyed oops! days of work with one click. Backup until you're really, really comfortable with them! Gord
ghubbell@total.net

 

[ghubbell]

Sorry, I'm one behind you here!
I may have incorrectly assumed that you have one unbound field [GoodCode] and the real code from your table (bound) in a second field [BadCode]? Gord
ghubbell@total.net

 

[jallen919]

I need you to tell me if I'm missing something... I think I figured out a simple answer to my problem. I did an update query with the udpate field refering to the textbox on the form. Up to this point in the criteria field I was also putting the same info...

but if all the imported records in my table currently have a null value, I shouldn't specify any criteria on that field because there is none.

I created a button that just runs my update query and it updates all records in the table with the value of that field. Have I stared at this too long... it seems too simple? Your thoughts when you have time. Thanks again.

 

[ghubbell]

I'm still here! (already had my nap today!) You're figuring fast! If you really know what you want i.e. old code Is Null (in it's criteria) and "update to" would be i.e: 12345 then if this is basically a one time affair do it right in the query and don't bother with a form at all. Need to change all the records (criteria) = zzzzz ? Update to "not snoozing". (You can sit in front of one query and keep running it with different "updates" probably well, yes more easily than, via a form.) Oh, another little warning: if it is a one time thing, when you're done, delete the query as they will attempt to run just by opening them in any way other than design view. Under control? Gord
ghubbell@total.net