Help with formatting String

[Tupacmoche]

I have code to dynamically assign a string to a Combo Box's Row Source. If, I type it into the Row Source property Sheet it works fine:



"Soft";"Joint";"IHO";"IMO";"Faculty & Friends"


But here is the vba:


Dim strPerson As String
Dim strOrg As String

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.Value = strPerson
Else
Me.SoftGiftType.Value = strOrg
End If


The assignment of the two strings strPerson, and strOrg gives me an syntax error msg. How can, I fix the string and make this work?

 

[theDBguy]

Hi. How about using a single quote instead?

strPerson = "'Soft';'Joint';'IHO';'IMO';'Faculty & Friends'"
strOrg = "'Soft';'IHO';'IMO';'Faculty & Friends'"

 

[Tupacmoche]

Thanks! theDBguy it's been a long day.

 

[theDBguy]

Hi. You're welcome. Have a good weekend!

 

[Tupacmoche]

New problem follow-up. This string is being assigned to a combo box row source. When entered using the GUI this string works fine:


"Soft";"Joint";"IHO";"IMO";"Faculty & Friends" That is with the double quotes but when I change to single quotes:


"'Soft';'Joint';'IHO';'IMO';'Faculty & Friends'" Instead of getting a drop down with items to choose from a get one long string:


"'Soft';'Joint';'IHO';'IMO';'Faculty & Friends'" How can this be fixed?

 

[theDBguy]

Ah, how about let's try not using any quotes at all? For example:

strPerson = "Soft;Joint;IHO;IMO;Faculty & Friends"
Me.cboName.RowSource = strPerson
Me.cboName.RowSourceType = "Value List"

 

[Tupacmoche]

Here is all the code. Now, the combo box is not being populated.


Private Sub SoftGiftType_AfterUpdate()
Dim strPerson As String
Dim strOrg As String

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

 

[theDBguy]

Hi. I just tried it and it worked for me. Can you post a sample copy of your database with test data?

 

[Tupacmoche]

Should this be in the on load or after update event?

 

[theDBguy]

Should this be in the on load or after update event?

Hi. It all depends on when you want it to happen. In my quick test, I just used a button's Click event. What exactly are you trying to do? If you're trying to modify the Row Source of a combo based on the current record, then maybe you want to use the Form's Current event.

 

[Tupacmoche]

I put it into the after update event. But when, I researched it further everyone said to put it into the On load event. In any event it's now working thanks again!

 

[theDBguy]

Okay. Glad to hear you got it sorted out. Good luck with your project.