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

 

[Pat Hartman]

jdjewell, Reports and Forms are VERY different objects. Like Crystal, an Access report would have no trouble with the type of formatting that you want. The difference is essentially that reports are "dead" and forms are "live". Reports are a sequential process and once a record is past, it is done with and Access doesn't need to "remember" anything about it. I know we don't use continuous form paper any more but that is how reports work. One record after another, until the paper runs out. Not so with a form. A form is a like a window that moves back and forth over a live recordset and a subform record is one of the individual panes of glass. I have double-hung windows in my house that are 6 over 6. So using that analogy, I can "see" 12 sub form records at once. However, there is only 1 subform so how can the 1 subform keep separate properties for 12 records. And of course 12 is not the limit because I can resize the form and show 30 records or more. Were should Microsoft set its limit of how many records it will store separate control properties for?

 

[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.

 

[Pat Hartman]

name + the identifier

- Aren't you missing an important dimension here? What about the row? And how would one identify the row? And don't forget that if you sort the recordset, you would need to rebuild the control arrays because the record positions would have changed. Put on your "programmer's" hat and really think about the problem.

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.

 

[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.