Summary Textbox Loop Issue

[access101]

I'm finally finished building my delivery system. I have a query which works fine the issue is every time i click on a item on my form the screen flickers. The form is a continuous form and on my form i have a list of menu items when i click on them it updates my summary textbox. but in order to show up on my textbox i need to run a docmd.openquery then refresh the form which is causing my flickering. below is the query that i run and was wondering if i could not use a query but use a vba string instead. i tried writing out the vba code me.summary = me.summary & me.sandwich & etc. but it reproduces all the previous entries.

Expr1: IIf([Bread] Like "*",[Bread] & " " & [Sandwich] & " " & [Expr2] & Chr(13)+Chr(10),[Sandwich] & " " & [Expr2] & Chr(13)+Chr(10)) & IIf([Extras]<"S*",[Extras]) & IIf([Veg]<"S*",[Veg]) & IIf([Packets] Like "*",[Packets]) & IIf([Chips] Like "*",[Chips]) & IIf([Cookies] Like "*",[Cookies]) & IIf([Othersides] Like "*",[Othersides]) & IIf([Drinks] Like "*",[Drinks]) & Chr(13)+Chr(10)

 

[lameid]

Why do you need to store the summary? Looks like you have all the underlying information in other fields.

Why not just make your control source the entire expression?

Then you should not need to do anything to show the data.

 

[access101]

right i dont need to to store the summary just display it. is there a for or do statement that can loop through and display the records in my continuous form in my summary box?

 

[dhookom]

There is no looping or continous in your original posting. I think I remember your previous thread and assumed you would still be struggling with this.

I would never use something that complex in a query or control source. My solution would probably involve sending the field values to a small user-defined function and returning a string. This would be much more maintainable.

Duane
Hook'D on Access
MS Access MVP

 

[access101]

can you please provide an example

 

[dhookom]

Let me preface this by suggesting IMO your table structure is not normalized. I cringe when I see fields with names of data values.

That said, a function might look like:

Public Function GetItem(varBread as Variant, varSandwich as Variant, _
		varExtras as variant, varVeg as Variant, varPackets as Variant, _
		varChips as variant, varCookies as Variant, varOthersides as Variant, _
		varDrinks as Variant) as String
	Dim strItemOut as String
	If not IsNull(varBread) Then
		strItemOut = strItemOut & varBread
	End If
	' more code based on your required logic
	GetItem = strItemOut
End Function

You would call the function with something like:

GetItem([Bread], [Sandwich], [Extras], [Veg], [Packets], [Chips], [Cookies], [Othersides], [Drinks])

Duane
Hook'D on Access
MS Access MVP

 

[access101]

Great i think that would work now my question is will it loop?
on my summary box i need it to list the records like below:

summarybox
(line 1 of my continuous form)
bread
sandwich
extras
veg
packets
chips
cookies
othersides
drinks

(line 2)
bread
sandwich
extras
veg
packets
chips
cookies
othersides
drinks

etc...

 

[dhookom]

Please provide actual table and field names, sample records, and desired output.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

access101,

Access generally can calculate each record with record scope on continuous forms for calculated expressions in the control source... There are some exceptions.

Have you tried the solution given?

 

[access101]

OrderQueue(Table)

ID
Bread
Sandwich
extras
veg
packets
chips
cookies
othersides
drinks

TicketOrder(MainForm)
Has summary textbox
Sub form Continuous form control source is OrderQueue

 

[dhookom]

Let's try again "Please provide actual table and field names, sample records, and desired output."

Do you expect your end result to contain values from more than one record?

Duane
Hook'D on Access
MS Access MVP

 

[access101]

My take name is called orderqueue and the fields are bread etc

 

[dhookom]

Let's try again "Please provide actual table and field names, [red]sample records, and desired output."

Do you expect your end result to contain values from more than one record?[/red]

Duane
Hook'D on Access
MS Access MVP

 

[access101]

Sample record from my table
Column:ID Sandwich Extras Drinks Veg Other Packets Chip Cookie
Rows: 35 Turkey Cheese Coke BBQ
Rows: 36 Roastbeef Dr Pep Ketchup Choc

i want my results (summary Textbox) on my form to show all records in my queue:

Turkey
Cheese
Coke
BBQ

Roastbeef
Dr Pep
Ketchup
Choc

Not sure if this is what your looking for? All these fields are bound to my continuous form.

 

[dhookom]

Define "cue". Apparently this is more than one record combined into the same string.

Duane
Hook'D on Access
MS Access MVP

 

[access101]

cue is just a table that holds the order until i print

 

[dhookom]

Since you seem to want multiple records in a single expression, search the web or tek tips for my Concatenate function.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya access101 . . .

I've been following this thread and it appears the end result is to make the [blue]big order![/blue] Wouldn't you rather know the count for each item? ... that is ... how many turkey sandwiches, how many turkey sandwiches with extra cheese, how many cokes, how many ketchup ... ect. If true I see something like:

[tt][blue]Bread: WholeWheat 12
Bread White 22
Sandwich: Turkey 33
Sandwich: Turkey/Extra Cheese 10
Sandwich: Roast Beef 13
Sandwich: Roast Beef/Extra Cheese 6
Drinks: Coke 25
Drinks: Dr Pep 5[/blue][/tt]
... or is this something you would need in addition to the actual orders?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[access101]

the summarybox is to simply show the values selected in each combo/list box so that i can read it back to the customer before i print the order. my goal is to show an itemized total next to each field. Its a continuous form so i need to loop through the records and display the recordset

so for example:
Order1
Item: Cost:
Roastbeef sandwich $5.00
Extra Meat $1.00
Dr Pep $1.50

Order2
Turkey sandwich $5.00
Sprite $1.50

 

[access101]

i think im close to getting it but if my table is blank i get errors
here is the code i have so far.

Me.Sandwich.Form.Refresh
Dim rst As Recordset
Dim rex As String
On Error Resume Next
Set rst = Me.Sandwich.Form.RecordsetClone
rst.MoveFirst
While Not rst.EOF
If IsNull(rst!Bread) Then
rex = rex & rst!Sandwich & " " & rst!Totals & vbCrLf & rst!Extras & rst!Veg & rst!Packets & rst!Chips & rst!Cookies & rst!OtherSides & rst!Drinks & vbCrLf
End If
If Not IsNull(rst!Bread) Then
rex = rex & rst!Bread & " " & rst!Sandwich & vbCrLf & rst!Extras & rst!Veg & rst!Packets & rst!Chips & rst!Cookies & rst!OtherSides & rst!Drinks & vbCrLf
End If

rst.MoveNext
Wend

rst.Close
Set rst = Nothing

Me.Summary = rex