Reference a value from a Combo Box or Auto Complete field (1 Viewer)

[Lucas79]

Using the value from a Combo Box or Auto Complete field in Access Web App.

Hi All,

I'm using the Office 365 Access Web App to create a nice documentation system. Mostly I have it running the way I want, but I'm banging my head on something very simple, I just don't see it. :banghead:

On my main form I have a checkbox which is used to let our support engineers now if there is a project going on with that specific customer. Also on that main field is a Textbox for the ProjectNumber and the ProjectManager (Lookup from a table of ProjectManagers)

For now a simple MessageBox saying: "Attention: This customer has a Project ([ProjectNumber]) in progress, please contact [ProjectManager] for more information." is all that I need. Pretty straight forward, but I'm getting a number returned, instead of the selected Project Manager.

Here's what I've created:

[B]If[/B] [ProjectYes_No]=1 then
[B]setVariable[/B]
  Variable varMessage
  Value = "Attention! This customer has Project "+[ProjectNumberTekst]+" in progress, please contact "+[ProjectManagerCmb].[Value]+" for more information."

[B]MessageBox[/B]
  Message =[varMessage]

[B]End If[/B]

The above results in a MessageBox with the intended text, including the ProjectNumber, but instead of showing the ProjectManagers name, it shows a number (which I asume is the ID of the record)

Can anyone please tell me what I'm doing wrong?

Thanks,

Lucas

 

[pbaldy]

Try

Me.ProjectManagerCmb.Column(1)

 

[Lucas79]

Dear Paul,

Thank you for your quick reply. Sadly it didn't work. It gives an error, something like "invalid use of control" (can't be more specific, cause my error messages are in Dutch. )

There is quite a difference it seems in working with controls on forms with O365 Access Web app as opposed to the Offline Office 2013 app, even if the project is opened in the same offline app.

Any other ideas? I just can't figure out why I am able to reference the values of textboxes and other controls, and only the ID's within the comboboxes, instead of just the selected value (text).

Lucas

 

[pbaldy]

Oh wait, that's a macro? Me wouldn't work in a macro. Try

ProjectManagerCmb.Column(1)

 

[Lucas79]

Hi Paul,

Thanks again for your fast reply. Yes it is a Macro the runs when the focus from the form changes to another record. I had already tried without 'me' and got the same error.

To make it a bit easier, I put a text label on my form and change the caption after updating the ComboBox, with basically the same code, but again it gives the same result.

After update

[B]SetProperty[/B]
  Control Name: Label
    Property: Caption
    Value: =[ProjectManagerCmb].[Column(1)]

The above results in the same error (invalid use of control) When I change it to [ProjectManagerCmb].[Value] the caption changes into the ID of the selected value (same as with the MessageBox.

Any other ideas/ways to determine which text value is selected?

Lucas

 

[Lucas79]

Small update:

This problem seems to occur only when the Combobox (or AutoLookUp) is bound to a different table then where the form is based on. If I use a MessageBox to display the value of a ComboBox with a number of fixed values in it, the original code works and returns the value, instead of the ID.

Lucas

 

[pbaldy]

Should matter, presuming the combo has two columns in the row source, the ID and the text. Can you attach the db here?

 

[Lucas79]

Dear Paul,

Thank you for your help! My own small update got me thinking in the right direction and I have fixed it. It was a bit more complex then I first thought, but when looking back is actually quite logical. (as always )

The solution is to create a DataMacro to get the values from the "Employees" table where my Project Managers are stored. This is how I got it to work:

1. Created a DataMacro named "GetEmployee" with the following code:

Click "Create Paramater"

- in the Name field I typed "EmployeeID"
- in the Type field I choose "Number (Floating Decimal)"

Then I added the actions:

[B]Look Up A Record In[/B] [I]Employees[/I] (this is the name of the Employee table)
  [B]Where Condition = [Employees].[id]=[EmployeeID[/B] (This is the name given to the parameter above)

[B]SetReturnVar[/B]
 Name: varEmployee
 Expression: =[Emplyees].[FullName] (FullName is the name of the row in the Employees table with the fullname of the employee

2. When changing the focus of the form, I check on the form if a project is in progress, by checking if someone checked the "Project" box and has entered the Project Number and Project Manager.

If [ProjectYes_No]=1 then

[B]RunDataMacro[/B]

 Macro Name: GetEmployee
 [B]Parameters[/B]
  EmployeeID = [ProjectManagerCmb]
  SetLocalVar: varProjectManager = varEmployee

[B]SetVariable[/B]
  variable: varMessage
  Value: = "Attention! This customer has Project "+[ProjectNumberTekst]+" in progress, please contact "+[varProjectManager]+" for more information."

[B]MessageBox[/B]
  Value: =[varMessage]
[B]End If[/B]

 

[pbaldy]

Seems more complicated than it should be, but I'm glad you got it working.