VBA code not working as expected (1 Viewer)

Tupacmoche

Registered User.
Local time
Today, 12:54
Joined
Apr 28, 2008
Messages
291
Hi VBA Masters,


I have this simple code:


'THIS CODE POPULATES THE GIFT TYPE COMBO BOX
strPerson = "Soft;Joint;IHO;IMO;Faculty & Friends"
strOrg = "Soft;IHO;IMO;Faculty & Friends"

If Nz(DLookup("PersonorOrgan", "dbo_tblTransmittalInfo", "GiftID= " & [GiftID] & " "), "P") = "P" Then
Me.SoftGiftType.RowSource = strPerson
Me.SoftGiftType.RowSourceType = "Value List"
Else
Me.SoftGiftType.RowSource = strOrg
Me.SoftGiftType.RowSourceType = "Value List"
End If


Using a DLookup it determines if a row belongs to a Person (p) or an Organization (o). Using a simple if statement it assigns the appropriate string to a combo box. But, I always get the same string assignment. Can anyone see what is wrong?:banghead:
 

plog

Banishment Pending
Local time
Today, 11:54
Joined
May 11, 2011
Messages
11,611
But, I always get the same string assignment

So, work backwards. Which string is that? That will tell you what your If statement is always returning. That will tell you if the DLookup always or never returns a value.

Then after that, I bet it all comes down to what is in [GiftID]? Not what you think is in there. What is actually in there?
 

RuralGuy

AWF VIP
Local time
Today, 10:54
Joined
Jul 2, 2005
Messages
13,826
Use your DLookup to populate a variable and then a MsgBox to display the results to troubleshoot your issue.
 

Tupacmoche

Registered User.
Local time
Today, 12:54
Joined
Apr 28, 2008
Messages
291
This is strange. I added a DLookup column to the subform that contains the Combobox and it returns an O (Organization) or P (Person) as it should nevertheless the ComboBox still does not work. It always return O (Organization) and never P (Person). But the DLookup is evaluating correctly. Any other ideas?:confused:
 

plog

Banishment Pending
Local time
Today, 11:54
Joined
May 11, 2011
Messages
11,611
Again, [GiftID] doesn't contain what you think it does.

In the form it is pulling that value from the current record of the control source.

In your code, there is no current record nor control source. You are using [GiftID] as a variable. So, how does that variable get populated? My guess is it doesn't.
 

Tupacmoche

Registered User.
Local time
Today, 12:54
Joined
Apr 28, 2008
Messages
291
GiftId is a FK in this table. The DLookup checks to see if the corresponding row in a parent table is either o or p. As, I mentioned in the DLookup expression it always returns the correct value which is an integer datatype that links the two tables. What it is looking up in the related table is the column PersonorOrg which has the o or p in it. This is what drives the if statement.:confused:
 

plog

Banishment Pending
Local time
Today, 11:54
Joined
May 11, 2011
Messages
11,611
GiftId is a FK in this table.

What table? We are talking about code. There are no tables in code.

As, I mentioned in the DLookup expression it always returns the correct value

As, I mentioned the DLookup in the form operates differently than the one in your code.

Again, how does [GiftID] get its value set in your code?
 

Tupacmoche

Registered User.
Local time
Today, 12:54
Joined
Apr 28, 2008
Messages
291
The [GiftID] gets it's value from the current record/row in the table.
 

Tupacmoche

Registered User.
Local time
Today, 12:54
Joined
Apr 28, 2008
Messages
291
So what do I need to add to have it work on the current record?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:54
Joined
Feb 19, 2013
Messages
16,553
sounds like you are using lookup fields in your tables

and how is this supposed to work?

"GiftID= " & [GiftID] & " "

what is the additional space for? ID implies a number

either way it returns a text value so you would need single quotes

"GiftID= '" & [GiftID] & " '"
 

Tupacmoche

Registered User.
Local time
Today, 12:54
Joined
Apr 28, 2008
Messages
291
I'm back to finally resolve this matter. I apologize as, I have multiple issues going on at the same time (who doesn't) so I just getting back to this. That said, the DLookup uses GiftId (Integer Data Type) to check the corresponding GiftID in the tblTransmittalInfo table and see if it is a person or Org that is refereed to and then simple returns p for person or o for Organization. The DLookup defaults to p (person) if the column is empty.

What, I have discovered is that whatever, I put into the 'Else' part of the if statement is always returned. So, apparently it is not evaluating the condition. But, I don't know why it isn't. Anyone see why?:(
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:54
Joined
Feb 19, 2013
Messages
16,553
you seem to keep asking questions and not responding to the solutions provided
So, apparently it is not evaluating the condition
it is evaluating it and returning a value, just not P.

"GiftID= " & [GiftID] & " "
if gift ID is numeric, why are you adding a space?

'THIS CODE POPULATES THE GIFT TYPE COMBO BOX
when does this code run? what event is it under

Provide some example data
 

plog

Banishment Pending
Local time
Today, 11:54
Joined
May 11, 2011
Messages
11,611
I think I got it.

You want the drop down selection to be based on the current record's GiftID value. Like I said before you can't reference form values like you did in your iniital code. Instead, like the combo box itself, you must reference the control and the control must be the current object that has focus.

So you must correct your code in 2 manners:

Form_Current() - the code you are trying to use must be in the Form_Current event of the form.

Me.GiftID.Value - that's how you reference the GiftID value on the form from code.

Code:
Private Sub Form_Current()
  ' changes drop down options in SoftGiftType vased on GiftID of record

strPerson = "a;b;c"
strOrg = "1;2;3;"
bool_Person = True
 ' determines if record is person (True) or Organization (False)

if (DCount("[PersonOrgan]"), "dbo_tblTransmittalInfo", "GiftID=" & Me.GiftID.Value & "  AND [PersonOrgan]<>'P'")>0 Then bool_Person=False
  ' sees if value of GiftID is for Organization 


If (bool_Person) Then Me.SoftGiftType.RowSource = strPerson Else Me.SoftGiftType.RowSource = strOrg
  ' loads correct values into drop down


End Sub

I also replaced your NZ(Dlookup) with a cleaner DCount which always returns a number. And I didn't test any of it, so there might be errors, but its 95% of the way there.
 

Users who are viewing this thread

Top Bottom