clearing a value and assigning a new value

[RussOSU]

Ok here is my issue, I have a database that was created back in 1995/96 and I am trying to add a bandaid fix to it while I recreate it. I am an intermediate Access/VB programmer normally however it has been 5 years (thanks job market) since I have used it so my knowledge is rusty and very novice right now. What I was attempting I thought was easy but I am starting to think I was wrong.

Currently we have a value come in assigned to a person in most situations however there are 2 situations where it is assigned to a location. What I am wanting to do is change the data in the database after it comes in to be assigned to a person. My format below __.__ is for ease of typing the first is the table second is the field. My basic idea was:

If tblA.Name1 = Location30
and tblA.Mod = tblLocation30.Mod
then
(not sure how to but I want to clear the field tblA.Name2 and then assign the value tblLocation30.Name1 to the tblA.Name2 field)

This seemed easy till I started trying to remember what I was doing. The second issue with this is I have a form with a drop down box that looks at a tblUser for the names. I have the names and the additional names I want to filter for in this table. The tricky part is that this drop down filters the tblA.Name1 field. I want it to look and if tblA.Name1 starts with STT (STT-Name)I want it to filter by the tblA.Name2 field. Is this possible? If not does anyone have any suggestion?

Thanks for all your help.

-Russ

- Russ

 

[hwkranger]

Russ,

I'm going to take a shot in the dark and try to help you.

Try using a Recordset. Chances, are since you're database is from 1997, you'll have to use DAO. If not, try using ADO.

Yes, what you want is possible, you'll have to look at using the RIGHT() function, and the recordsource property of you drop down boxes.

Sadly, I can't help you with DAO. I have forgotten how to use DAO, and I have no desire to remember it

But hopefully this will have pointed you in the right direction.

Randall Vollen
National City Bank Corp.

 

[dpk136]

for help with DAO here's a site that's helpful. i used it before when i had a problem that couldn't be solved with ADO.

http://techrepublic.com.com/5100-22_11-1045296.html#

David Kuhn
------------------

http://www.davidpkuhn.com

 

[Zion7]

I don't quite understand, how you make your comparisons, in the If statement. How do you sync records, to make the comparison.

Outside of that, Something like this...


Dim recA As DAO.Recordset
Dim rec30 As DAO.Recordset

Set rec30 = CurrentDb.OpenRecordset("tblLocation30",dbOpenDynaset)
Set recA = CurrentDb.OpenRecordset("tblA")

Do Until recA.EOF

If recA!Name1 = Location30????
and recA!Mod = tblLocation30.Mod???
then
rec30.Edit
rec30!Name1 = recA!Name2
rec30.Update
End If

recA.MoveNext
Loop

recA.Close: Set recA = Nothing
rec30.Close: Set rec30 = Nothing


Again, How do you sync TblA with tblLocation30?

Is there a primary/foreign key relationship?

My code is useless, without knowing this...

 

[dpk136]

if you want to link the tables together you should have some way of doing it...like a foreign key.

David Kuhn
------------------

http://www.davidpkuhn.com