Most popular unanswered question in this forum...

[jdjewell]

I have searched high and low for the answer to this question, and yet, nothing...

I have a tabular form, which is being populated by a query. One of my fields is unbound, and is derived based upon values of a couple other fields. If my recordset is:

Name Address1 Address2 City State
------- ------------- ------------ ----- ------
John 128 Easy St. Suite 202 Chicago IL
Joe 253 Wilson Ave. Apt. 3 Milwaukee WI

and I have a single address field on my form, I would expect to see:

Name Address City State
------- ---------- ---- ------
John 128 Easy St. Suite 202 Chicago IL
Joe 253 Wilson Ave. Apt. 3 Milwaukee WI

What I get is:

Name Address City State
------- ---------- ---- ------
John 128 Easy St. Suite 202 Chicago IL
Joe 128 Easy St. Suite 202 Milwaukee WI


Trying to address this problem in the OnCurrent() event of the form would work fine, but when you have 1000 records, who wants to click through each and everyone of them to get the Address field to calculate itself correctly?

BTW, this is an example, this is only to illustrate my problem. Concatenating my two or three database fields into one single field in the table is not at all an option.

I do appreciate any help you can give me.

 

[John.Woody]

Try creating your name and address field in the query and then use that field as the control source on your form.

 

[jdjewell]

Maybe I wasn't clear enough...

What I was saying at the end of the first post, is that this can't be handled in the SQL code. It has to be handled at runtime.

Essentially, shouldn't there be an event procedure that is firing every time a new record is output in the detail line of a tabular form?

 

[Rich]

Why can't it be handled in sql?

 

[shay]

Hi

I don't think you stated how you were trying to accomplish this task. This should work - put

=[Name] & " " & [Address] & " " & [City] & " " & [State]

as the Control Source property of your unbound text box. If this is what you're already using, ignore me!

hth

shay

 

[jdjewell]

More details...

Obviously, I need to expand on the details so we can have a clear picture. Forget about the example I gave before.

I have an Apartment table, which contains a couple of fields -- StatusID and StatusDate.

The StatusID performs a lookup to a Status table, which also has a Description field.

Here are the possible statuses:

Vacant
Move-Out Notice (if the status was previously occupied and the current date is < 30 days from the lease end date)
Eviction
Application Pending
Occupied

If the status is occupied, it will never show up in the query (where clause will handle this).

If the status is Vacant, Eviction or App. Pending -- we want to show the status description if it is on Move Out Notice, we want to show the date. Also, if it is Vacant, we are going to want to be able to show "Vacant since" and show the date.

As it sits, you cannot use any vba to handle any of this, because it will only evaluate the first record...hence the dilemma, we need to evaluate the current row in the tabular format.

Thanks again guys for all of your thoughts on this.

 

[shay]

What do you mean by this?

As it sits, you cannot use any vba to handle any of this, because it will only evaluate the first record...hence the dilemma, we need to evaluate the current row in the tabular format.

What exactly are you trying to do? Are you viewing the data on a form a record at a time? Are you wanting to print a report with this info for all non-occupied properties? The solution will depend on what you want to do.

shay

 

[jdjewell]

I want to see the same data for all rows, all at once in a tabular form. Again, the problem is that there isn't an "As You Go" event to process the logic against each new record that is output into the tabular form (that I know of) -- hence I am here.

 

[shay]

I suggest you write a query to give you the info you want to see. You can concatenate fields if you wish eg [StatusDate] & " " & [Description].

You can then create a datasheet form to view the data.

There is nothing unusual about what you want to do - unless I'm missing something here!

Ah, wait a minute, are you trying to display the status for all records except Occupied and one other 'comment' field which contains moving date, vacant since or description depending on Status?

If so try putting this SQL code in your query:

SELECT Table1.status, IIf([status]="Vacant"," " & [VacantSince],(IIf([status]="Move"," " & [MoveDate],[Description]))) AS Notes
FROM Table1
WHERE (((Table1.status)<>"occupied"));

You'll need to change the table and field names!

hth

shay

 

[jdjewell]

Thanks Shay!

I think that I can do something with the logic you gave me. This should do the trick. I'll let you guys know after I try it tonight.

 

[graedus]

On the subject of the "as you go" event for tabular forms: Say one wanted a textbox to be either enabled or disabled depending on the value of a field within a record. Is there a way to do this with a tabular form? A way to have a textbox enabled on one line for one record and disabled for the next?

 

[jdjewell]

Emphatic NO!

Sorry, but as I have come to learn, the intuitive "As You Go" event -- referred to in Crystal as "While Printing Records" -- is not exposed on a tabular form. This seems intuitive to me, but apparently not to Microsoft. Anyone who understands this differently, feel free to speak up.

 

[FoFa]

I am not going to pretend I totally understand Countinous/Tabular forms in Access, but from what I do understand it is like this. The controls are basically one control, showing different rows of data. So any changes made to one, affects the entire column of controls. Also because of this implementation, I see no use of an unbound field in this type of form because it becomes useless. Now logic dictates since each row is displaying different data, there must be some way to access each row on it's own. But MS has not implemented any way to do this to my knowledge. maybe in the next version

 

[jdjewell]

That's all well and fine, but...

If I can do it on the web, I should be able to do it in Access! If you create a control on a tabular form, it wouldn't take much effort for Access to create a control array, and you would reference the control by its name + the identifier. Most programmers understand this methodology, and employ it when creating web-enabled or vb forms.

So, I can see all "12" properties of my form in VB, and in the web; the problem with Access is that the programmers have limited us in the functionality that they have made available.

 

[Rich]

graedus
On the subject of the "as you go" event for tabular forms: Say one wanted a textbox to be either enabled or disabled depending on the value of a field within a record. Is there a way to do this with a tabular form? A way to have a textbox enabled on one line for one record and disabled for the next?

"Emphatic no"
replace that with "Emphatic yes" the Form_Current event will handle that request

 

[jdjewell]

Wow, I didn't know that Pat and Rich work for MS (Just kidding guys)...let me see if I can address these:

"Emphatic no"
replace that with "Emphatic yes" the Form_Current event will handle that request

I suppose you're right...If you want to go through and click every single record in the form!!!!!!!!

Pat, I think you're missing the mark just a little bit. The identifier correlates to a row. If I have a column of textboxes (now a textbox array), text(0) = the first row's textbox, text(1) the second and so on and so forth.

As for rebuilding your arrays, why would you need to do that? Once you have uniquely identified each cell of a matrix, you don't have to re-uniquely identify them, just because someone changes the sort on the recordset.

The web interface is not a form, it is a report. You can tell because it doesn't support any events. Creating an array of 12 records is one thing. What about 1200 or 12,000 or 120,000. Where do you draw the line? Surely you don't want to wait for an array of even 1200 records to be built and sent around the world.

Well, I didn't elaborate on how to do it in the web, but you don't have to build a control array in the web. Take the following example:

<%
count = 0
While not myRecordset.EOF%>
  <tr><td><input type="text" name="text<%=count%>"></td></tr>
  myRecordset.MoveNext
  count = count + 1
Wend

Notice, I will do the same amount of processing on 12,000, 120,000 or 1,200,000 records as you will! I think my programmer's hat fits just fine, thank you very much

 

[Rich]

I suppose you're right...If you want to go through and click every single record in the form!!!!!!!!
Why would a user have to do that?

 

[jdjewell]

The answer is:

The reason a user would have to go through and click on every record, is that the onCurrent event (a.k.a. Form_Current) for a tabular form is kicked off when you open the form and when you click on the record selector. When you open the form, the Form_Current doesn't fire off for every row, only the first one.

So, if you wanted to disable and enable textboxes based on something within the record, and you put the code in the Form_Current, the only record which will be evaluated will be the first row (unless you click the record selector for another row -- then it will evaluate that record).

Also, let's say that you decided to go with the route of putting your code in the form_current. Since each cell is referenced by the same name as the cell above and below it in the tabular form, if the first textbox is disabled, all textboxes below it are disabled, if the first is enabled, all textboxes below it are enabled. Again, each row would update when you clicked the selector.

And that my dear Watson, is the reason I said a user would have to go through and click every single record.

 

[jdjewell]

Not so fast my friend...

Rebuild because there is nothing in your array that coorelates to data. It is all positional. And once you sort the data, you change its position.

In VB, you can get the number of elements in the control array. You then can iterate through each row (albeit that row 8 might be first and row 1 might be last now) and perform your operation on the row, because all of the other information for the record will still be associated with it. Maybe this isn't making sense.

Take this example:

[b][u]Name[/u]  [u]Address[/u]       [u]City[/u]      [u]State[/u] [u]Shipping Covered[/u][/b]
Adam  118 Easy St   Elinwood  KS
Brad  1018 Main St  Boston    MA
Chad  804 E St      New York  NY

As we output each line, we can evaluate whether a checkbox in our "Shipping Covered" should be checked or not. Our logic could look something like this

for i = 0 to uBound(txtState) 'txtState is the name of the control array for the State
  if txtState(i) <> "NY" then
    chkShipping(i).value = 1 'checked
  end if
next

You see, it doesn't matter if we re-order the rows, since each control for a given row will have the same identifier, and we will always be able to perform our logic for any given row.

This would work pretty much the same way on the web, since we would be associating all records in a row by a single common identifier.

So, let's recap:
VB: Can
Web: Can
Access: CAN'T

 

[Rich]

Funnily enough we do this in Access with an unbound control without the need for a slow array=Iif([State]="NY",True,False)