Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Run-time error 3127

Posted on 2006-07-11
23
Medium Priority
?
1,647 Views
Last Modified: 2010-10-05
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.Column(0)
    strWorkType = Me.cboLivestockWorkType.Column(1)
    strWorkCost = Me.txtWorkCost.Value
   
    strSQL = "SELECT tblLivestock.LivestockID, tkpAnimalType.AnimalTypeDescription, "
    strSQL = strSQL & " tkpLivestockType.LivestockTypeDescription , 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.LivestockTypeID = tblLivestock.LivestockTypeID) "
    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("qryWorkLivestock")
    rsSubformData.MoveFirst
    Do Until rsSubformData.EOF
        strLivestockID = rsSubformData.Fields("LivestockID").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.Requery
    Exit Sub
ExitDuplicate:
    MsgBox "error message is " & Err.Description
End Sub
0
Comment
Question by:zubin6220
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 11
23 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17084837
does tblWorkLiveStock have a field named WorkType?
J
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17084874
If it exists, is it textual or numeric? right now you're passing it " & strWorkTypeID & "
0
 

Author Comment

by:zubin6220
ID: 17085060
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.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 34

Expert Comment

by:jefftwilley
ID: 17085156
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
0
 

Author Comment

by:zubin6220
ID: 17085258
ok, I follow 'ya, so then I can still use the combo box row source:
SELECT tkpLivestockWorkType.WorkTypeID, tkpLivestockWorkType.WorkPerformed FROM tkpLivestockWorkType;

Will this work?
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17085712
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
0
 

Author Comment

by:zubin6220
ID: 17085833
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(WorkPerformed, WorkCost) values ( " & strWorkTypeID & ",  '" & strWorkCost & "');"

 but nothing is being written to tblWorkLivestock.  
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17085971
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(WorkPerformed, 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("qryWorkLivestock")
    rsSubformData.MoveFirst
    Do Until rsSubformData.EOF
        strLivestockID = rsSubformData.Fields("LivestockID").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
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17085990
Z, do you still have that old code we originally put together?
0
 

Author Comment

by:zubin6220
ID: 17086088
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?)
0
 

Author Comment

by:zubin6220
ID: 17086129
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.
0
 

Author Comment

by:zubin6220
ID: 17086226
>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.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17086705
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.
0
 

Author Comment

by:zubin6220
ID: 17087620
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.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17088006
I'll take a look at this tomorrow...we'll get it going.
J
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17088010
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.
0
 

Author Comment

by:zubin6220
ID: 17112846
Jeff,

I've attached the db and responded to your email, now just waiting to hear from you.
Thanks
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17112946
All done John, In your e-mail
J
0
 

Author Comment

by:zubin6220
ID: 17116530
jeff,

Could you please zip and resend.  I'm unable to open.

Thanks
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 2000 total points
ID: 17117591
done
0
 

Author Comment

by:zubin6220
ID: 17119148
jeff,

Thank you very much.......works great.
0
 

Author Comment

by:zubin6220
ID: 17119158
This did it:

strInsert = "Insert into tblWorkLivestock(LivestockID,WorkTypeID, WorkCost, WorkDate) values"
strInsert = strInsert & " ('" & strLivestockID & "' , '" & strWorkTypeID & "' , " & strWorkCost & ", #" & strWorkDate & "#);"

Thanks again
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17119200
You're very welcome Z. Let us know if you get jammed up again.
J
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question