Modifying a form with subform generated by Wizard

[tigreeuropeo]

(posted earlier - 1 unsuccesful response)

Hi!!!.

I have a form and a subform in it.
The form is to type in orders, and the subform the items of the order, it has unlimited number of lines.

I generated these forms using the wizard a long time ago, and it has a lot of code in it, so I can't start it again.

I would like to add another column from another table in the subform, especifically the name of the item.

*********************************************************

I tried changing the recordsource of the subform, basing the subform in a query, but when I change the recordsource of the subform, everything that's contained in it disappears!!.
That's a tragedy for me...
Besides, I have code to add, modify and delete items from the order, and I think that's not possible to do it with a query.

Is there any way around not to loose everything I've done in it?.

Thanks.

 

[JamesMeadlock]

Hmmm... You need to change the recordsource of the subform. I'd just use a sql statement instead of creating a query.

Go to your query in design mode, pick sql view, and copy the sql code... paste it into your subform's record source property.

If you have problems doing that, an alternative method:

Create an unbound text-box for your itemname... and use the oncurrent event to put the itemname in it (this won't be an updateable field, which I assume is fine)...

Sub OnCurrent
me.txtItemName.Value = Dlookup(blahblah)
End Sub

HTH
James

 

[tempclerk]

"Besides, I have code to add, modify and delete items from the order, and I think that's not possible to do it with a query."

That is different. You probably don't want to join your master list of items to a record that may be deleted. (It would probably not be updateable, but that's a problem in itself.)
Instead, I would try adding a textbox to your (sub)form with it's control source set to DLookup the name of the item that matches the item ID.

 

[tigreeuropeo]

Thanks JamesMedlock and tempclerk!.
But DLookup doesn't work as origin of the control.
Besides an unbound text box in a subform (continous subform) makes the SAME value to appear in each line!!!.
I designed the order with a header. The order number, date and client number are located here.
Then, in the subform (lines of the order), I have the item number, quantity, price per unit, and total for the line.

I need to add the description in the line, but an unbound text box isn't the solution. I'm really sure about that.

I designed (as an example) another form with the wizard, with a subform, and I included the description of the article (from the table Stock) and it works ok.
But the problem remains the same, all the code and customization of the original form is losted.

Any new ideas?.

Thanks in advance.

 

[JamesMeadlock]

Hey; I screwed up w/my advice. Forget the oncurrent event... go to your field's control source, and change it to the dlookup expression and you should be good to go.

hth
James

 

[tigreeuropeo]

I tried that JamesMeadlock, but if I use an unbound text box, it shows the same description in each line of the subform (the subform appears as many times as lines it has).
And there's another problem, when I open the form (the main one), it doesn't show anything in the unbound text box.
It only shows something when I type a new line (add a record in the subform).

Remember that I modify, add and delete lines in this form/subform.

Thanks.

 

[JamesMeadlock]

I have a similar subform with a calculated value in it... it's not an unbound textbox, it's bound to an expression (in my case,=[Price]/[Quantity])... I believe this will work for you.

 

[tigreeuropeo]

I'm still trying to make that work, but it doesn't.

I'm using bounding the text box to this expression:

DLookup("Art_Name","[Stock]","[Art_Reference] = " & "'" & [Me]![Line_Art_Ref] & "'&quot

Perhaps this expression is incorrect, it shows an error in the text box.

- Art_Name is the name of the column where the description of
the article is stored.
- Stock is the name of the table of articles.
- Art_Reference is the name of the column of Stock, where
the reference is stored.
- Line_Art_Ref is the reference of the article of that
line of the order.

Do you think this expression is wrong?.
I compared it with the example, and read the help for DLookup, and I can't find the difference.

Thanks.

 

[JamesMeadlock]

I dunno; go to the vb editor immediate window, and do:
Debug.Print(YourExpression)

Also, in the control source property field, I believe the syntax is:
=Dlookup(yourdlookupexpression)

(Don't forget the = sign)