Editing a specific record on a different table (1 Viewer)

Theguyinthehat

Registered User.
Local time
Today, 13:49
Joined
Aug 17, 2009
Messages
46
Every time a tool is used, I would like the runcount of that tool to be increased by one. The tools are in a table "Tools", connecting to table "JobEntry" by field ToolID.
My code for the job Entry command button:

Dim runcount
Set runcount = db.OpenRecordset("Tools")
runcount.Edit
runcount("Runcount").Value = runcount("Runcount") + 1
runcount.Update


adds one to the runcount of the tool at the top of the list. I tried:

Set runcount = db.OpenRecorset(SELECT [RunCount] from Tools WHERE ToolID = [JobEntry].ToolID

where [JobEntry].ToolID is supposed to refer to the ToolID in job entry and the first ToolID is supposed to refer to the toolID in the table tools, but it becomes obvious this is completely wrong. I tried using FindRecord after Set runcount = db.OpenRecordset("Tools") but that also didn't work. I wasn't allowed to find/replace now. Can anyone help?
 

Scooterbug

Registered User.
Local time
Today, 16:49
Joined
Mar 27, 2009
Messages
853
Every time a tool is used, I would like the runcount of that tool to be increased by one. The tools are in a table "Tools", connecting to table "JobEntry" by field ToolID.
My code for the job Entry command button:

Dim runcount
Set runcount = db.OpenRecordset("Tools")
runcount.Edit
runcount("Runcount").Value = runcount("Runcount") + 1
runcount.Update

adds one to the runcount of the tool at the top of the list. I tried:

Set runcount = db.OpenRecorset(SELECT [RunCount] from Tools WHERE ToolID = [JobEntry].ToolID

where [JobEntry].ToolID is supposed to refer to the ToolID in job entry and the first ToolID is supposed to refer to the toolID in the table tools, but it becomes obvious this is completely wrong. I tried using FindRecord after Set runcount = db.OpenRecordset("Tools") but that also didn't work. I wasn't allowed to find/replace now. Can anyone help?


looks like you are missing some things:

Code:
[B][COLOR=red]dim db as dao.database[/COLOR][/B]
[B]Dim runcount [COLOR=red]as dao.recordset[/COLOR][/B]
[B]Set runcount = db.OpenRecordset("Tools")[/B]
 
[B]runcount.Edit[/B]
[B]runcount[COLOR=red].Fields[/COLOR]("Runcount").Value = [COLOR=red]![[/COLOR]Runcount[COLOR=red]][/COLOR] + 1[/B]
[B]runcount.Update[/B]

"Tools" will equal the sql of the record you want to change.
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:49
Joined
Aug 30, 2003
Messages
36,118
Try this, in addition to Scooter's comments (except the last one ;) ):

Set runcount = db.OpenRecorset("SELECT [RunCount] from Tools WHERE ToolID = " & Me.ToolID)

Or whatever the name of the control is on the form that holds the tool ID. I wouldn't name the recordset the same as the field, so as to avoid confusion.
 

Theguyinthehat

Registered User.
Local time
Today, 13:49
Joined
Aug 17, 2009
Messages
46
Tried that, got 'too few parameters, expected 2'. I ran into this earlier, but I'll admit I have no experience in parameterized queries (I use the SQL query builder whenever possible). I don't know what =" means either...yikes? In any case, it requires another parameter... If this is demanding too much, is there a site that can fill me in?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:49
Joined
Aug 30, 2003
Messages
36,118
2? What is the name of the field in the table, and what is its data type? Both the tool ID field and the runcount field actually.
 

Theguyinthehat

Registered User.
Local time
Today, 13:49
Joined
Aug 17, 2009
Messages
46
Thanks. Tool ID field is a long integer in table JobEntry and in table Tools

RunCount is only in table Tools and is a long integer.
 

Theguyinthehat

Registered User.
Local time
Today, 13:49
Joined
Aug 17, 2009
Messages
46
This just in. I changed the name to ToolID in tools (before it was tool, i figured I would make them the same for ease's sake), and now its 'too few parameters. expected 1'
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:49
Joined
Aug 30, 2003
Messages
36,118
What is the exact name of the field in the table? Does it have a space or not?
 

Theguyinthehat

Registered User.
Local time
Today, 13:49
Joined
Aug 17, 2009
Messages
46
Ok, I'm slowing down. The 1 parameter is to a different issue i can handle. There is no space in ToolID or Runcount.
code is now
Set runcount = db.OpenRecordset("SELECT [RunCount] from Tools WHERE ToolID = " & Me.cboToolSelect)


runcount.Edit
runcount("Runcount").Value = runcount("Runcount") + 1
runcount.Update



where cboToolSelect is the combobox selecting the tool. It runs without errors, but does not add to the runcount in the table Tools
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:49
Joined
Aug 30, 2003
Messages
36,118
Can you post the db?
 

Theguyinthehat

Registered User.
Local time
Today, 13:49
Joined
Aug 17, 2009
Messages
46
How do I do that?

I'll put it up if I have to, but maybe this conveys what I'm trying to do (db already defined CurrentDb):

Dim toolid
Dim runcount
Set toolid = db.OpenRecordset("SELECT [ToolID] FROM JobEntry WHERE LotNumber = '" & Me.LotNumber & "'")
Set runcount = db.OpenRecordset(" [ToolID] FROM Tools WHERE ToolID = toolid("ToolID"))
runcount("Runcount").Value = runcount("Runcount") + 1
runcount.Update



****
LotNumber is the primary key in job entry & the lookup value in the combobox
ToolID is the primary key in table Tools and a foreign key in Job Entry
To restate: I am trying to lookup the ToolID of the record with the selected LotNumber, then go to that tool in table Tools and add one to that tool's runcount. ToolID and Lotnumber are long integers
 

Scooterbug

Registered User.
Local time
Today, 16:49
Joined
Mar 27, 2009
Messages
853
Set runcount = db.OpenRecordset(" [ToolID] FROM Tools WHERE ToolID = toolid("ToolID"))
That is not valid Sql. Should be:
Code:
"SELECT Tools.ToolID From Tools WHERE ToolID =" & toolid
I would also define the second Sql statement before using it as a record source
Also use a Dlookup to get the ToolID:

Code:
Dim stSql as string
stSql = "SELECT Tools.ToolID From Tools WHERE ToolID =" & toolid
 
 
Dim runcount [COLOR=red]as DAO.Recordset[/COLOR]
[COLOR=#ff0000]dim stSql as String[/COLOR]
[COLOR=red]dim lngToolID as Long[/COLOR]
 
[COLOR=red]lngToolID = dlookup("[ToolID]","JobEntry","[LotNumber]=" & Me.LotNumber  (Only use quotes around LotNumber if it's a text field)[/COLOR]
 
[COLOR=red]stSql = "SELECT Tools.ToolID From Tools WHERE ToolID =" & lngToolID[/COLOR] 
Set runcount = db.OpenRecordset([COLOR=red]StSql[/COLOR])
[COLOR=red]runcount.Edit[/COLOR]
runcount("Runcount").Value = runcount("Runcount") + 1
runcount.Update
[COLOR=red]runcount.Close[/COLOR]
[COLOR=red][/COLOR] 
[COLOR=red]Set db = Nothing[/COLOR]
[COLOR=red]set runcount = Nothing[/COLOR]
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:49
Joined
Aug 30, 2003
Messages
36,118
You're going backwards. The runcount OpenRecordset line is completely wrong now, and you don't have the .Edit line. You're treating LotNumber as text in the ToolID OpenRecordset line. You haven't disambiguated the declarations as Scooter recommended earlier. You still use variables with the same names as your fields, which may not cause an error but can be very confusing in code.
 

Theguyinthehat

Registered User.
Local time
Today, 13:49
Joined
Aug 17, 2009
Messages
46
Sorry, I was getting flustered yesterday. LotNumber is a text field--that is easily corrected on the code. I've disambiguated the objects from their fields--now looking up dlookup and string usage. Sorry I was so back and forth--I think I need to learn the fundamentals of dlookup and recordset
 

Scooterbug

Registered User.
Local time
Today, 16:49
Joined
Mar 27, 2009
Messages
853
Take a deep breath :)

I dont normally use help files, but I use the Help in the VBA editor quite a bit. Trying to commit everything to memory the first time you use something can be difficult to say the least (at least for me anyways).

And just remember, to logically think about what you are trying to accomplish. Write it down if it helps. I know I work better sometimes if I see the progression on paper. In your case, it would be something like this:

What to accomplish?
1. Change the number of the runcount everytime a tool is used.

Data I need to accomplish task:
1. ToolID So I know which runcount I need to update
-Can be found by looking up the ToolID number.
-A Tool is used when a JobEntry is created.
Solution: Use a command that will lookup the ToolID based off of the JobEntry (Dlookup)
2. Once I have the ToolID number, I need to open that tool record so I can increase the count
-Use a recordset to get the specific tool runcount

From here, you need to know the steps on how to properly use a recordset.

After looking at this, it appears that you are storing calculations. Might I suggest that can use a dcount to find the runcount of a particular tool. This assumes that each Job that is done has it's own record.
Code:
Dim lngRunCount as Long
 
lngRunCount = DCount("[ToolID]","tblJobEntry","[ToolID] = " & Me.cboToolSelect)
 
me.txtDisplayCount = lngRunCount
 
Last edited:

Theguyinthehat

Registered User.
Local time
Today, 13:49
Joined
Aug 17, 2009
Messages
46
Finally got it.
Took your advice, scooter, took a deep breath then wrote out what I needed to do.
1. Find the toolID from JobEntry
2. Open record of ToolID in Tools
3. Go to RunCount field, add 1

This is what works:

Dim objtoolID As Object
Dim obruncount As Object
Set objtoolID = db.OpenRecordSet("SELECT [Tool] FROM [JobEntry] WHERE [LotNumber] = '" & Me.LotNumber.Column(0) & "'")
Set obruncount = db.OpenRecordSet("SELECT [RunCount] FROM [Tools] WHERE [ToolID] =" & objtoolID("Tool").Value)
obruncount.Edit
obruncount("RunCount").Value = obruncount("RunCount").Value + 1
obruncount.Update


I had to feed the first into the second. Thanks to the both of you for your help and patience.
 

Users who are viewing this thread

Top Bottom