Populate additional fields

[Joe8915]

I have one table Name TblProclog the other is called Contractor Name. I have it now working., if they select a contractor it will populate a field called DUNNS.

Private Sub Contractor_Name_AfterUpdate()
Me.Dunns = Me.Contractor_Name.Column(1)

End Sub

Now I would like to add another field called TaskOrder.
I have tried
Private Sub Contractor_Name_AfterUpdate()
Me.Dunns = Me.Contractor_Name.Column(1)
Me.TaskOrder = Me.Contractor_Name.Column(2)

End Sub

I received error

Then I tried:

Private Sub Contractor_Name_AfterUpdate()
Me.TaskOrder = Me.Contractor_Name.Column(2)

End Sub
I also received error

Any thoughts on what I am doing wrong

 

[pbaldy]

What error? Is the additional field in the combo's row source? Is the column count property of the combo appropriate to the number of fields in the row source?

 

[Joe8915]

Thanks pbaldy for the quick reply.
I guess I should mention that I have SQL running as my BE

When I try to enter the following:

Private Sub Contractor_Name_AfterUpdate()
Me.Dunns = Me.Contractor_Name.Column(1)
Me.TaskOrder = Me.Contractor_Name.Column(2)

I received error msg
Run-time error '2147352567 (800200009)
You tried to assign the null value to a variable that is not Variant data type

Private Sub Contractor_Name_AfterUpdate()
Me.TaskOrder = Me.Contractor_Name.Column(2)
End Sub

I will get the same error msg

 

[pbaldy]

Is there a value in the third column? Try

Me.TaskOrder = nz(Me.Contractor_Name.Column(2), "None")

or something along those lines.

 

[Joe8915]

pbaldy, I will post the entire code this might give you better idea of what is going on. Once again thanks for the quick reply


Option Compare Database
Option Explicit

Sub Combo107_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo107]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Private Sub Combo107_GotFocus()

End Sub

Private Sub Contract_Number_Click()
MsgBox ("You must enter a dash between the first two digits and the last 4 digits. Sample 00-0000")
End Sub

Private Sub Contract_Number_GotFocus()
MsgBox ("You must enter a dash between the first two digits and the last 4 digits. Sample 00-0000")
End Sub

Private Sub Contractor_Name_Click()
MsgBox ("If Correct Contactor Name is selected, verify correct Duns Number before proceeding")
End Sub

Private Sub ContractType_AfterUpdate()
If (Me.ContractType) = "CO" Then
MsgBox "You have just now added a NEW Construction Contract, please fill in the the following fields: (NTP) (AE Design Firm) (CM COR) (AE COR) (Warntee Date) (Region) (Construction Description)!"
End If
End Sub

Private Sub ContractType_Change()
'If (Me.ContractType) = "CO" Then
' MsgBox "Please fill in the the following fields NTP, AE Design Firm, CM COR, AE COR Warntee Date and the Construction Description!"
'End If
End Sub

Private Sub ContractType_Click()
'If (Me.ContractType) = "CO" Then
' MsgBox "Please fill in the the following fields NTP, AE Design Firm, CM COR, AE COR Warntee Date and the Construction Description!"
'End If

End Sub

Private Sub Command139_Click()
On Error GoTo Err_Command139_Click


DoCmd.GoToRecord , , acNewRec

Exit_Command139_Click:
Exit Sub

Err_Command139_Click:
MsgBox Err.Description
Resume Exit_Command139_Click

End Sub
Private Sub Command141_Click()
On Error GoTo Err_Command141_Click


DoCmd.GoToRecord , , acNewRec

Exit_Command141_Click:
Exit Sub

Err_Command141_Click:
MsgBox Err.Description
Resume Exit_Command141_Click

End Sub
Private Sub Command142_Click()
On Error GoTo Err_Command142_Click


DoCmd.GoToRecord , , acNewRec

Exit_Command142_Click:
Exit Sub

Err_Command142_Click:
MsgBox Err.Description
Resume Exit_Command142_Click

End Sub

Private Sub Close_Form_Click()
On Error GoTo Err_Close_Form_Click

Dim stDocName As String

stDocName = "MacCloseAddNewKTR/TO"
DoCmd.RunMacro stDocName

Exit_Close_Form_Click:
Exit Sub

Err_Close_Form_Click:
MsgBox Err.Description
Resume Exit_Close_Form_Click

End Sub

Private Sub NTP_GotFocus()
MsgBox ("If this is AE Contract, change it to read as the Award Date. If CONSTRUCTION CONTRACT leave it to read 12/31/1899")
End Sub
Private Sub Contractor_Name_AfterUpdate()
Me.Dunns = Me.Contractor_Name.Column(1)
End Sub

Private Sub Text302_Click()
MsgBox ("If correct number, Please paste the this number in the Dunns field ")

End Sub

Private Sub Type_Click()
MsgBox ("If No Park Package number, please enter two **")
End Sub

Private Sub Task_Order_AfterUpdate()
Me.TaskOrder = Nz(Me.Contractor_Name.Column(2), "None")


End Sub

 

[pbaldy]

Can you post the db here?

 

[The_Doc_Man]

It is not the code but rather the .RowSource of your combo box that will answer the question PBaldy is trying to answer.

In order to use ComboBox.Column(2,rownumber), there has to be a column 2 in the .RowSource in the first place. Otherwise, that construct will fail.

 

[Joe8915]

pbaldy, I pasted the code in the correct place and it work:

Private Sub Contractor_Name_AfterUpdate()
Me.Dunns = Me.Contractor_Name.Column(1)
Me.TaskOrder = Nz(Me.Contractor_Name.Column(2), "None")

Doc Man thanks for chimming in as well


End Sub

 

[Joe8915]

Well guys, I think I spoke to soon. If I type the word none in that Column it show on the form.

So its pulling from Column (2), now I just have to pull what ever it loaded in that Column.

Pbaldy, the db is 176 kb, so I don't think its possible to post the db and its government owned. At one time I could do the shell only. I don't know if that would work for you or not

 

[pbaldy]

Well, type something different than what the Nz() function is going to put in there, so you know which value is being seen.

 

[Joe8915]

pbaldy, I have over 1500 different contractor names. The word "none" comes up on every record.
Why was the first one so easy to do with the DUNNS? I have 1500 different Contractor Names and 1500 different DUNNS numbers. When I select the particular Contractor the Dunns number that is associated with that individual contractor will be different.

Thanks for being so patient with me on this.

Private Sub Contractor_Name_AfterUpdate()
Me.Dunns = Me.Contractor_Name.Column(1)
Me.TaskOrder = Nz(Me.Contractor_Name.Column(2), "None")
End Sub

 

[pbaldy]

"None" is coming up because of the Nz() function. You never answered my other questions, so I don't really know why. What is the row source of the combo? If it's the name of a table, what are the fields, in order? What is the column count property of the combo?

 

[Joe8915]

pbaldy, first of all I would like to thank many times over. After a good night sleep and reviewed your replies. The magic word kept comming up row source, and that is where some how it was slipping thru my smal brain. As you would thought, that is where my mistake was comming from. I didn't add the field into my row source.

Once again many thanks to you

 

[pbaldy]

Happy to help!