How to include Memo field linked to a Combo Box in a Form

Jose

Registered User.
Local time
Today, 08:33
Joined
Nov 3, 2010
Messages
31
Hello,
I have a combo box with three fields: ID, Funds, Exceptions. These fields are linked to a query and the query has been created from a table called "DailyPrices". I would like to use the ID record from the Combo box to search for it in the table "DailyPrices" and provide me the information contained in another field of the table called "Exceptions" which is related to that particular ID. The "Exceptions" field from the table is a memo field. I would like to include this field in the form which includes the combo box is so users can update the table using the form. is this possible?

I tried to use a text box linked to the combo box including Dlookup in the data control source but it doens't work. I read that memo fields can't be indexed, is that the problem?

Jose
 
Last edited:
If the field you want forms part of the Control Source for your combo, you can use the following in an unbound text box.
Code:
=ComboName.Column([B][COLOR="Red"]x[/COLOR][/B])
Where x is the column number that holds the data you wish to display. Remember the the columns in a combo or list box are numbered from zero up.
 
John,
thanks for your message. It was useful but I dont get the results I want to. Let me give you an example. In one of the columns of my combo box I have record IDs. The combo box has been created using a query. This query has been created using a table called "DailyPrices". If i clicked in one of these record ID, I would like to do a search for that record in the DailyPrices table and give me the field called "Exceptions" related to the record. All records have the "Exceptions" field empty so I would like to add that field to my form therefore the that field related to that record in the table can be updated.

Combo box record ID 15 -----> Finds record ID15 in Table Daily Prices ----> extract the "Exceptions" field related to record ID15 from the table to my form. If the field is empty i will use the form to pass information to the Exceptions field related to the record ID15 in the table.

I hope it makes sense now.

Jose
 
In the true Microsoft tradition, it should do something that you wouldn't guess in a dozen tries, based on the name! :D
 
John,
Thanks for your help. I used a text box linked to the combo box including Dlookup in the data control source and i get the field i wanted in my form from the table. The problem I have now is that I cant type any information on the text box. Do you know what I should do to be able to type info?

I am really sorry if I am asking very simple questions. I am teaching myself VBA for Ms Access and I am a beginner.



Jose
 
You're apparently doing the data assignment to the textbox using its Control Source, which is why you can't edit the control. When the Control Source is a calculation (the DLookup calculation, in your case) this is normal behavior.

You need to
  1. Remove the DLookup() from the Control Source of the textbox
  2. Place the DLookup() in the AfterUpdate event of the combobox.
  3. Bind the textbox to a field in the form's underlying table/query, using the Control Source
You'll get the data you want in the textbox and you can now edit it, if necessary.
 
Alternatively you could run an SQL update procedure, that might look something like;
Code:
    DoCmd.RunSQL "UPDATE [YourTableName] SET [YourTableName].[YourMemoField] = [forms]![YourFormName]![MemoField] WHERE " & _
    "((([YourTableName].[LinkingID)=[forms]![YourFormName]![LinkingID]));"
I'd probably put it in the Memo field's On Lost Focus event.
 
Guys,
Thanks for your help. I think I will try both of them just to learn how both solutions work.

Missingling,
If I typed something in the textbox, does it get recorded in the table bound to the textbox?

Jose
 
John,
How can I avoid the window message alerting about updating 1 row appearing?

Thanks

Jose
 
John,
How can I avoid the window message alerting about updating 1 row appearing?

Thanks

Jose

You can do this on a case by case basis by using the following code;
Code:
DoCmd.SetWarnings False
     <<Action Query>>
DoCmd.SetWarnings True
 
You can also suppress, the messages associated with Action queries if you the Tools Menu and select Options, and then select the Edit/Find tab, and in the Confirm option group remove the check from Action Queries.
 
Cool guys.. it works now.. Thanks for the lesson. Where should I send the beers?


Jose
 
Well, now I have another problem which I cant find a solution. I have a two forms Form 1 and Form 2. In form 1 I have a combo list ExceptionsList. When I click on a record of the combo box ExceptionsList it opens Form 2 and passes the value to combo box FundIDCb. I used the FundIDCb default value to pass the value from the ExceptionsList to the FundIDCb. If I open the Form 2 before the Form 1 I get the #name? in the FundIDCb, I think the reason for this is that ( obviously ) nothing is passed as default value. Do you know how to sort out this problem, please? In the default value, I have tried to write an iif condition saying if nothing is passed then choose a certain value but it doesn't work.

Jose
 

Users who are viewing this thread

Back
Top Bottom