Need help with changing values in a form, by row number

[Jon]

I have the following table for use in this form/subform:

Shipment
==============
Shipment_Number
Source
Destination
Miles
Trip_Duration
Weight
Arrival_Time
Departure_Time

Each shipment number has at least 2 rows.

When sorted by shipment_number and Arrival time, you can see where a
shipment goes.

Take this shipment as an example:

The data will be in this order: (Shipment_Number, Source, Destination,
Miles, Arrival_Time)

1234, Dallas, Austin, 200, 03/01/2007 12:00:00PM
1234, Dallas, San Antonio, 100, 03/01/2007 3:00:00PM
1234, Dallas, Laredo, 150, 03/02/2007 12:00:00AM
1234, Dallas, Waco, 450, 03/03/2007 6:00:00AM
1234, Dallas, Dallas, 100, 03/03/2007 10:30:00AM

This means that the shipment starts in Dallas, then goes to Austin,
then San Antonio, then Laredo, then Waco, then back to Dallas. The
shipment always ends at where it started (the source).

I have a form that has 2 forms on it. The top form is a group report
that shows Shipment_Number and the sum of the miles and the
min(arrival_time) and the max(departure_time) for each
shipment_number. The top report for the above example would look like
this (but with much more data in the real report):

1234 - 1,000 miles, 03/01/2007 Start Date, 03/03/2007 End Date
1235 - 687 miles, 03/01/2007 start date, 03/07/2007 end date

The form below it, is a subform that shows the detailed rows for each
shipment highlighted in the top report (this looks like the example
above).

I need to have a field in the top summary report that is a string that
combines all the different destinations. So, the example above would
look like this in the top summary report:

1234, Austin-->San Antonio-->Laredo-->Waco-->Dallas, 1,000 miles,
etc......

My strength is in Powerbuilder, and I am trying to do this in Access
(w/VBA).

I can build the location strings, but I do not know how to edit the
existing form and put the new string in whichever row it needs to be
in. These need to be in the top summary form as soon as the user opens
the form.

 

[Albert D. Kallal]

In this case, I would build a custom fucntion to build that "text" value.

You then simply place a text box on the form, and bind to that code we just
wrote.

We place our code in that forms code module.

This is air code..but, shoudl be close:

Public Function MyDestList2() As String

Dim rst As DAO.Recordset
Dim s As String

On Error GoTo EXIT1:
Set rst = Me.child1_test.Form.RecordsetClone

rst.MoveFirst
Do While rst.EOF = False

If s = "" Then
s = rst!ShipMent_Number & ", "
Else
s = s & "--->"
End If
s = s & rst!Destination
rst.MoveNext

Loop

MyDestList2 = s
EXIT1:

End Function

Now, for that text box, for the data souce of that text box, use the above
fucntion name:

=MyDestList2()

 

[Jon]

Thanks for the quick reply!

I made the changes, but nothing is happening. The new function is not
even being used (I put a breakpoint in it, and it never hit it). Here
is where I have everything:

I have a MAIN form that has the SUMMARY and the DETAIL forms within
it. When the user highlights a different Shipment in the SUMMARY
form, I have the DETAIL form requery based on the new shipment number.

On this line,

Set rst = Me.child1_test.Form.RecordsetClone,

I referenced it to the DETAIL form.

I put the function in the code module for the SUMMARY form. Not the
MAIN form.

I also put the new text box in the SUMMARY form in the detail
section. These all need the new text box populated as soon as the
report opens.


Make sense?

 

[christianlott1]

Thanks for the quick reply!

I made the changes, but nothing is happening. The new function is not
even being used (I put a breakpoint in it, and it never hit it). Here
is where I have everything:

Put the function in a module.

In the textbox put this:

=MyDestList2

 

[Albert D. Kallal]

I also put the new text box in the SUMMARY form in the detail

section. These all need the new text box populated as soon as the
report opens.

Ouch...I assume you mean form..not report????

Assuming we are still dealing with a form..then:

Since we need this information in the main form, then the code and example I
outlined assumes that you will place this code in the main form part.

It also not clear if the main form part is continues. that is a important
detail....

So, you place a un-bound text box in the main form part (the detail part as
you mention).

For the data source of this text box, we use our function name:

=MyDestList2()

The placing of function name in the text box is what makes the code run.

The text box should display as you navagte, or move the main record, or load
the form....

 

[Jon]

I still cannot get this to work.

My bottom form is the detail form. When the user tabs through the
rows in the top form (the summary form), it requeries the bottom form
to show the detailed shipments. The query used in the bottom form
isbased on the shipment_number = shipment_number in the top form.

I need the string I am building to be loaded once the form opens, not
as the user tabs through the rows.

So, when I can the function (as the datasource in my text field - ie
=MyDestList2() ) Would I need to pass that row's shipment number? so
would the data source for the textbox be something like this:
=MyDestList2(shipment_number)

So that I can build the destinations for just that shipment number?

Does this make sense?

 

[Albert D. Kallal]

So, when I can the function (as the datasource in my text field - ie

=MyDestList2() ) Would I need to pass that row's shipment number? so
would the data source for the textbox be something like this:
=MyDestList2(shipment_number)

I don't see why you would need to pass the shipment number. that text box
runs off of the current records in the sub-form.

I would test that your function actually works. Place a text box on your
main form, and have it call the code.

eg:

msgbox MyDestlist2()

It is possbile that you might have to add a me.contorlname.Requery in the
code that sets the sub-form data.

I would "test" your code behind a button to make sure it actually works. Try
the msgbox idea. You don't know even if the code works yet..and you need to
test that..and get it working behind that test button + msgbox as above...

You could also post the code you have (perhaps you translated it wrong. I
would also remove the error handling code from that test routine until you
get it working..

 

[Jon]

I don't see why you would need to pass the shipment number. that text box

runs off of the current records in the sub-form.

I need the text box to have values for all the rows, regardless of
what row the user has selected. The top form is a continuous form.
So, you see the first 15 rows. I would need the new text box
populated for all the rows on the summary form, as soon as the report
opens. Not as the user tabs through them.

 

[Albert D. Kallal]

I need the text box to have values for all the rows, regardless of
what row the user has selected.

Yes, the above makes sense. Of course, we talking about rows in the sub-form
...right?

The top form is a continuous form.

Ah, Ok..that is a / the problem! Ok, then we can't possible consider using
the sub-form records to fill our text box, because the sub-form records
are for only ONE of the upper selected records. So, we really don't give one
hoot what records are displaying in the sub-form, we care about what the
record is (shipment_number), and need to display related records in that
text box (the text box is to display the child records).

Ok, now that we have that clear, the child records in the sub-form are
of NO USE to us. So, yes, as you have, your question/suggestion that you
must pass the shipment_number makes perfect sense.

So, you see the first 15 rows. I would need the new text box
populated for all the rows on the summary form,

Right but the summary form is only showing records from ONE of the top
records. So, we want to display the same records IF IN FACT the record
WAS selected.

So, we don't really care, or even have to know that the sub-form is
currently displaying a set of records for ONE of the current top forms
records.
We need to grab child records FOR EACH OF the top forms records.

Since
we need to display those child records for EACH of the top most forms child
records, then we can't base our textbox fill routines on he sub-form
records,
since they are ONLY displaying records for ONE of the top forms records.

We have to go elsewhere for those child records. We can't use the sub-form.

as soon as the report
opens. Not as the user tabs through them.

Ouch!!1 Why now are we talking about a report? When did a report come into
play here? Do you have a paid group of people here MAKING EFFORTS to confuse
everyone here? By introducing the term report in the middle of a discussion
about code in a form, you are becoming AN EXPERT IN confusing people. Are
you building a report here, or a we talking about a form? which is it?

Anyway, I going to try keep some sanity here, and just play rudely ignore
your comments
about the a report. (else, we will never finish this discussion!!). Lets try
and work on
the reporting issues latter, or perhaps in another thread, or discussion. We
need to
stick to one subject and one area of software development and problem
solving here.

So, since is now clear that we have a continues form, and in that continues
form, you want to display information form EACH child record in a text box
IN
THAT continues form, then, yes, you have suggested, we do need to pass that
text box the primary key id used to relate those child records.

So, lets modify our code.

The text box would be un-bound. Well, actually we would use the
function for the data source. As you suggested we will pass the
shipmenu_number:

=MyDestList2([shipment_number])

So, we simply will ignore the sub-form all together here. We can't use the
sub-form as datasocue, since we have a repeating text box on the top form.

So, our public function code in that same forms code module would be:

Public Function MyDestList2(vShipID as Variant) As Variant

Dim rst As DAO.Recordset
Dim s As String
dim strSql as string

On Error GoTo EXIT1:

strSql = "select * from Shipment where shipment_number = " & vShipID

Set rst = currentdb.openReocordSet(strSql)

Do While rst.EOF = False

If s = "" Then
s = rst!ShipMent_Number & ", "
Else
s = s & "--->"
End If
s = s & rst!Destination
rst.MoveNext

Loop
rst.Close
set rst = nothing
MyDestList2 = s
EXIT1:

End Function

Note that the above assumes that shipment_number is a number data type
field. If shipment number is text type field, then the above code needs to
be modified to surround vShip with quotes.