VBA code not working as expected

[Tupacmoche]

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]

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]

Use your DLookup to populate a variable and then a MsgBox to display the results to troubleshoot your issue.

 

[Tupacmoche]

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?

 

[plog]

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]

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.

 

[plog]

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]

The [GiftID] gets it's value from the current record/row in the table.

 

[Tupacmoche]

So what do I need to add to have it work on the current record?

 

[CJ_London]

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]

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]

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]

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.

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.