zubin6220
asked on
Run-time error 3127
I'm receiving error 3127 - INSERT INTO statement containing the following unknown field name 'WorkType' .....
I've tried, but can't understand why it's showing as 'unknown'.
Column 0 field is named: WorkTypeID and Column 1 field is named WorkPerformed.
Your thoughts?
Dim rsSubformData As DAO.Recordset
Dim strWorkTypeID As String
Dim strWorkType As String
'Get the Event data from the main form
strWorkTypeID = Me.cboLivestockWorkType.Co lumn(0)
strWorkType = Me.cboLivestockWorkType.Co lumn(1)
strWorkCost = Me.txtWorkCost.Value
strSQL = "SELECT tblLivestock.LivestockID, tkpAnimalType.AnimalTypeDe scription, "
strSQL = strSQL & " tkpLivestockType.Livestock TypeDescri ption , tkpPasture.PastureName, "
strSQL = strSQL & " tlkpBreed.BreedDescription , tblLivestock.TagNumber, tblLivestock.ChBxFlag "
strSQL = strSQL & " FROM tlkpBreed INNER JOIN (tkpPasture INNER JOIN (tkpLivestockType INNER JOIN "
strSQL = strSQL & " (tkpAnimalType INNER JOIN tblLivestock "
strSQL = strSQL & " ON tkpAnimalType.AnimalTypeID = tblLivestock.AnimalTypeID) "
strSQL = strSQL & " ON tkpLivestockType.Livestock TypeID = tblLivestock.LivestockType ID) "
strSQL = strSQL & " ON tkpPasture.PastureID = tblLivestock.PastureID) "
strSQL = strSQL & " ON tlkpBreed.BreedID = tblLivestock.BreedID "
strSQL = strSQL & " WHERE ((tblLivestock.ChBxFlag)=- 1);"
'Open up the subform data so you can loop through all the ones that are checked
Set rsSubformData = CurrentDb.OpenRecordset("q ryWorkLive stock")
rsSubformData.MoveFirst
Do Until rsSubformData.EOF
strLivestockID = rsSubformData.Fields("Live stockID"). Value
' Insert the record into the Event Table (tblEvent)
strInsert = "Insert into tblWorkLivestock(WorkType, WorkCost) values ( " & strWorkTypeID & ", '" & strWorkCost & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strInsert
rsSubformData.MoveNext
Loop
rsSubformData.Close
Set rsSubformData = Nothing
' Reset all the check boxes
strResetChecks = "UPDATE tblLivestock SET tblLivestock.ChBxFlag = 0;"
DoCmd.RunSQL strResetChecks
Me.sfmWorkLivestock.Requer y
Exit Sub
ExitDuplicate:
MsgBox "error message is " & Err.Description
End Sub
I've tried, but can't understand why it's showing as 'unknown'.
Column 0 field is named: WorkTypeID and Column 1 field is named WorkPerformed.
Your thoughts?
Dim rsSubformData As DAO.Recordset
Dim strWorkTypeID As String
Dim strWorkType As String
'Get the Event data from the main form
strWorkTypeID = Me.cboLivestockWorkType.Co
strWorkType = Me.cboLivestockWorkType.Co
strWorkCost = Me.txtWorkCost.Value
strSQL = "SELECT tblLivestock.LivestockID, tkpAnimalType.AnimalTypeDe
strSQL = strSQL & " tkpLivestockType.Livestock
strSQL = strSQL & " tlkpBreed.BreedDescription
strSQL = strSQL & " FROM tlkpBreed INNER JOIN (tkpPasture INNER JOIN (tkpLivestockType INNER JOIN "
strSQL = strSQL & " (tkpAnimalType INNER JOIN tblLivestock "
strSQL = strSQL & " ON tkpAnimalType.AnimalTypeID
strSQL = strSQL & " ON tkpLivestockType.Livestock
strSQL = strSQL & " ON tkpPasture.PastureID = tblLivestock.PastureID) "
strSQL = strSQL & " ON tlkpBreed.BreedID = tblLivestock.BreedID "
strSQL = strSQL & " WHERE ((tblLivestock.ChBxFlag)=-
'Open up the subform data so you can loop through all the ones that are checked
Set rsSubformData = CurrentDb.OpenRecordset("q
rsSubformData.MoveFirst
Do Until rsSubformData.EOF
strLivestockID = rsSubformData.Fields("Live
' Insert the record into the Event Table (tblEvent)
strInsert = "Insert into tblWorkLivestock(WorkType,
DoCmd.SetWarnings False
DoCmd.RunSQL strInsert
rsSubformData.MoveNext
Loop
rsSubformData.Close
Set rsSubformData = Nothing
' Reset all the check boxes
strResetChecks = "UPDATE tblLivestock SET tblLivestock.ChBxFlag = 0;"
DoCmd.RunSQL strResetChecks
Me.sfmWorkLivestock.Requer
Exit Sub
ExitDuplicate:
MsgBox "error message is " & Err.Description
End Sub
If it exists, is it textual or numeric? right now you're passing it " & strWorkTypeID & "
ASKER
jeff,
I think that may be the problem. tblWorkLivestock has fields:
WorkID
WorkDate
LiveStockID
WorkTypeID
Notes
I should add field 'WorkDescription' so I know what the work performed is. I was trying to follow on your original Insert Statement but I've erred. I'll go back through it and check some more.
I think that may be the problem. tblWorkLivestock has fields:
WorkID
WorkDate
LiveStockID
WorkTypeID
Notes
I should add field 'WorkDescription' so I know what the work performed is. I was trying to follow on your original Insert Statement but I've erred. I'll go back through it and check some more.
wasn't your Work Description derived from the Work Type ID code? if you're storing the ID in this scenario, then you don't want to store the description again....you'll need theId only so that forms and reports can display description based on the ID, but you do that when you run the report or open the form.
J
J
ASKER
ok, I follow 'ya, so then I can still use the combo box row source:
SELECT tkpLivestockWorkType.WorkT ypeID, tkpLivestockWorkType.WorkP erformed FROM tkpLivestockWorkType;
Will this work?
SELECT tkpLivestockWorkType.WorkT
Will this work?
definately. You just need to change that one field name above and at least you won't get that error. Have you tried it yet?
J
J
ASKER
Sorry, now I'm lost again....what field name are you referring to? WorkPerformed? change to what?
I'm not getting any error messages now using:
strInsert = "Insert into tblWorkLivestock(WorkPerfo rmed, WorkCost) values ( " & strWorkTypeID & ", '" & strWorkCost & "');"
but nothing is being written to tblWorkLivestock.
I'm not getting any error messages now using:
strInsert = "Insert into tblWorkLivestock(WorkPerfo
but nothing is being written to tblWorkLivestock.
referencing
does tblWorkLiveStock have a field named WorkType?
your insert line before
strInsert = "Insert into tblWorkLivestock(WorkType, WorkCost) values ( " & strWorkTypeID & ", '" & strWorkCost & "');"
your table info
tblWorkLivestock has fields:
WorkID
WorkDate
LiveStockID
WorkTypeID
Notes
your insert now
strInsert = "Insert into tblWorkLivestock(WorkPerfo rmed, WorkCost) values ( " & strWorkTypeID & ", '" & strWorkCost & "');"
~~~~~~~~~~
The insert syntax reads : Insert data into these fields, the values from these sources where my key field = this key
You are missing some parts. I think we originally had to identify the animal to insert this data for...am I right? So ....
'Open up the subform data so you can loop through all the ones that are checked
Set rsSubformData = CurrentDb.OpenRecordset("q ryWorkLive stock")
rsSubformData.MoveFirst
Do Until rsSubformData.EOF
strLivestockID = rsSubformData.Fields("Live stockID"). Value '<<<<--------WE grabbed this so we could insert the record
' Insert the record into the Event Table (tblEvent)
strInsert = "Insert into tblWorkLivestock(WorkType, WorkCost) values ( " & strWorkTypeID & ", '" & strWorkCost & "');" '<---A where clause is missing
DoCmd.SetWarnings False
DoCmd.RunSQL strInsert
rsSubformData.MoveNext
Loop
does tblWorkLiveStock have a field named WorkType?
your insert line before
strInsert = "Insert into tblWorkLivestock(WorkType,
your table info
tblWorkLivestock has fields:
WorkID
WorkDate
LiveStockID
WorkTypeID
Notes
your insert now
strInsert = "Insert into tblWorkLivestock(WorkPerfo
~~~~~~~~~~
The insert syntax reads : Insert data into these fields, the values from these sources where my key field = this key
You are missing some parts. I think we originally had to identify the animal to insert this data for...am I right? So ....
'Open up the subform data so you can loop through all the ones that are checked
Set rsSubformData = CurrentDb.OpenRecordset("q
rsSubformData.MoveFirst
Do Until rsSubformData.EOF
strLivestockID = rsSubformData.Fields("Live
' Insert the record into the Event Table (tblEvent)
strInsert = "Insert into tblWorkLivestock(WorkType,
DoCmd.SetWarnings False
DoCmd.RunSQL strInsert
rsSubformData.MoveNext
Loop
Z, do you still have that old code we originally put together?
ASKER
Disregard by last statement about not getting anymore error messages. Still getting the same one. It was gone but I see that I added 'WorkPerformed' field in tblWorkLivestock which you said not to duplicate. So I'm back where I started.....
change the name to what? Again, I appreciate your patience. This is getting rather confusing - it really shouldn't be though (what can I say?)
change the name to what? Again, I appreciate your patience. This is getting rather confusing - it really shouldn't be though (what can I say?)
ASKER
Yes, I still have the old code and was trying to match it up to my current changes.
I'll certainly understand if you would rather pull out on this mess....no one to blame but myself.
I'll certainly understand if you would rather pull out on this mess....no one to blame but myself.
ASKER
>does tblWorkLiveStock have a field named WorkType?< No, just WorkTypeID
You mention about a missing WHERE clause. Yes, the original had two; pasture and the check box. I just simplified and dropped the pasture and kept the ChBxFlag in my Where clause.
You mention about a missing WHERE clause. Yes, the original had two; pasture and the check box. I just simplified and dropped the pasture and kept the ChBxFlag in my Where clause.
No, I'm not pulling out! lol. I was getting off of work at the time. Have you posted this database somewhere? www.savefile.com is a good place. Let me take a look at it. Also, EE has an upload place now too...but I don't remember the URL.
ASKER
Jeff,
Thanks for your help.
I've attached the file to: http://www.savefile.com/files/7078715
I'm fairly sure that another field will needed to be added to tblWorkLivestock?
Also, maybe the way I have the three tables (tblLivestock, tblWorkLivestock and tkpLivestockWorkType) linked is incorrect? I was really trying to use the (older) file to pattern this but with no success.
This is the first time in using 'savefile', so if it is not available for download let me know.
Thanks again.
Thanks for your help.
I've attached the file to: http://www.savefile.com/files/7078715
I'm fairly sure that another field will needed to be added to tblWorkLivestock?
Also, maybe the way I have the three tables (tblLivestock, tblWorkLivestock and tkpLivestockWorkType) linked is incorrect? I was really trying to use the (older) file to pattern this but with no success.
This is the first time in using 'savefile', so if it is not available for download let me know.
Thanks again.
I'll take a look at this tomorrow...we'll get it going.
J
J
Hey Z,
can you name your zip file something.dat and attach it to an e-mail for me. My addy is on my profile.
can you name your zip file something.dat and attach it to an e-mail for me. My addy is on my profile.
ASKER
Jeff,
I've attached the db and responded to your email, now just waiting to hear from you.
Thanks
I've attached the db and responded to your email, now just waiting to hear from you.
Thanks
All done John, In your e-mail
J
J
ASKER
jeff,
Could you please zip and resend. I'm unable to open.
Thanks
Could you please zip and resend. I'm unable to open.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
jeff,
Thank you very much.......works great.
Thank you very much.......works great.
ASKER
This did it:
strInsert = "Insert into tblWorkLivestock(Livestock ID,WorkTyp eID, WorkCost, WorkDate) values"
strInsert = strInsert & " ('" & strLivestockID & "' , '" & strWorkTypeID & "' , " & strWorkCost & ", #" & strWorkDate & "#);"
Thanks again
strInsert = "Insert into tblWorkLivestock(Livestock
strInsert = strInsert & " ('" & strLivestockID & "' , '" & strWorkTypeID & "' , " & strWorkCost & ", #" & strWorkDate & "#);"
Thanks again
You're very welcome Z. Let us know if you get jammed up again.
J
J
J