TextBox update from ComboBox value

[SidLancing]

Hello again ScriVerb & PHV

I'm back with another question...
This time I have a Textbox (named SponsorName) that is a calculated field which is updated in the after_update event procedure of a combobox field (named StudyNum).
In the after_update of that field I have the following:

Private Sub StudyNum_AfterUpdate()
JobNumforYear = DLookup("JobWithinyear", "Projectlog", "Company=" & [ReceivingCompanyID] & " And Study='" & [StudyNum] & "'")
Cur_YR = DatePart("yyyy", [OrderDate])
CP = DLookup("CustNum", "Company", " CompanyID=" & [ReceivingCompanyID]) & Cur_YR & "-" & JobNumforYear
          SponsorName = DLookup("Sponsor", "Projectlog", "Company=" & [ReceivingCompanyID] & " And Study='" & [StudyNum] & "'")
          
    MsgBox "The sponsor is " & SponsorName
    Me.Refresh
End Sub

The problem is that SponsorName is blank on the screen.
I'm sure that it is calculated correctly in the Dlookup because the msgbox and the value in the table are correct.

Any Ideas what that might be ???

P.S... CP & JobNumForYear all display correctly on the screen. SponsorName is Text just like CP

Regards,
SidLancing

 

[TheAceMan1]

How are ya SidLancing . . . . .

[blue]The problem is that SponsorName is blank on the screen.[/blue]

The question is, what are you looking to see/happen if SponsorName is blank?

See Ya! . . . . . .

 

[SidLancing]

I'm not sure what your question means?..but SponsorName is supposed to be the value saved in the projectlog table. It is the Sponsor associated with the Company selected in ReceivingCompany and the Study selected in StudyNum. This is calculated in the after_update od StudyNum which is when I want this to happen. It's done with the Dlookip command and gives the correct value.I know this because the SponsorName field is updated correctly in the Table and I double check the value in the project table to make sure it works. but on the form the SponsorName remains blank even after the studyNum After_update runs.

 

[SidLancing]

Is anyone else available to answer my question posted above?
Scriverb? ... PHV? ... Anybody else?

 

[randy700]

Can you give us an example of what the sponsorname should be? Include the values returned to each of the other fields when you run the code.

Randy

 

[TheAceMan1]

SidLancing . . . .

Should'nt [purple]Company[/purple] below be [blue]CompanyID[/blue]?

[blue]SponsorName = DLookup("Sponsor", "Projectlog", "[purple][b]Company[/b][/purple]=" & [ReceivingCompanyID] & " And Study='" & [StudyNum] & "'")[/blue]

See Ya! . . . . . .

 

[SidLancing]

Randy700, - A value for SponsorName is Blimpie.
Here is a sample of the table ProjectLog:

ID	Company	Project	DateStarted	Study JobWithinYear	Sponsor	Description	DateCreated	
10	6	WRAP	6/3/2004	BLT	1	BLIMPIE		*	6/30/2004		
11	6	WRAP	6/4/2004	CLUB	2	SUBWAY		*	6/30/2004

Note that I put a * in the space for descripition because that is actually empty for now
So when ReceivingCompanyID is selected (in this case 6) it should bring back Blimpie if the selected Study is BLT and it should bring back Subway if the selected study CLUB.


Here is a copy of the Company table for that rec

CompanyID	CompanyName	Remarks	CustNum	CreatedDate:
6	New Guys	unknown customer	12347	6/4/2004

Here is a copy of the table Order that the form is working off of.

OrderId	OrderNumber	ShipingLocationID	OrderDate	SentDate	StudyNum	PurchaseOrder	Notation	BillingCompanyID	Recipient	Protocol	CitationProjectNumber	JobNumforYear	Resupply	SponsorName	BillingLocationID	Payer	Shipped	ReceivingCompanyID
4	1	5	6/30/2004	5/31/2004	BLT	MNSXP01		6	Senator Bob Roberto Robertson		123472004-1	1	No		14	OCD Brian  Leetch	No	6

So when I run the code...
I have ReceivingCompanyID =6 so Dlookup finds 6 in the projectlog table and when the value selected for StudyNum=
BLT
Dlookup uses that value to select the 10th record and brings back SponsorName as "BLIMPIE"...
How do I know that ...well when I select a study and go to another field the Msgbox prints "BLIMPIE" as indicated in the code above that is in the after_update of StudyNum.

I keep looking at this over and over and cann't figure out why the value doesn't show up in the field or the Order table where SponsorName is being saved.

**********

AceMan- No Company is the right field .. see a copy of the table above...

 

[TheAceMan1]

And if you disable Me.Refresh?

See Ya! . . . . . .

 

[SidLancing]

Thanks PHV ...
Is this happening because of the tables that I included above?...
In any case... Can you help with the above problem?..
I've tried taking out the Me.Refresh as suggested and still get nothing printed...