UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Get The Second Column Value Of A Multi-column Listbox Control On A Web Form    
 
   
jakedrew
post May 21 2011, 11:23 PM
Post#1



Posts: 47
Joined: 16-May 09



Does anyone know a better way to get the second column value of a multi-column Listbox Control on a web form? I am currently using the following process:
. Concatenate fields with a delimiter in the listbox rowsource property.
(ex. SELECT [Transaction Name] & "~" & [Count Type] & "|" & [Data Type] AS Expr1, [Herds Transactions Type].[Transaction Name] FROM [Herds Transactions Type]; )
2. Add the listbox display field as the second field in the row source property.
3. Set the listbox column count property = 2
4. Set the listbox column widths property = 0".
(this keeps the first column from showing, which is returned as the value when as listbox item is selected)

5. Use After Update Event or Control Source property to parse out the value required.
2nd column ex. =Mid([lstTransType],InStr(1,[lstTransType],"~")+1,InStr(1,[lstTransType],"|")-(InStr(1,[lstTransType],"~")+1))
There must be an easier way to do this???
Go to the top of the page
 
AlbertKallal
post May 22 2011, 04:46 AM
Post#2


UtterAccess VIP
Posts: 2,723
Joined: 12-April 07
From: Edmonton, Alberta Canada


As you found out, there's no column() function available for combo boxes in a web based application.
However a great way to get additional column values is to use the fact that you're using a relational database system. Even in regular access, often in place of using the column function, all you have to do is simply base the form on a query that left joins in the additional column values from the query as a join. This works even if you have several different combo boxes from several different tables.
When you do this, you'll have those values available for display on the form anyway as regular columns in the database. Thus when building the form, you can simply drop in some additional text boxes on the form to display these additional values that are from the other table anyway. In a fact, you'll not be using the additional columns of the combo box to display and utilizes additional data, but the fact that the form is now based on a query.
HAs noted, you probably then will not need to copy those other values anyway. And I suppose if you really do have to copy the values, then you can certainly use code in the after update event of the combo box event to simply reference the text boxes that you've dropped on the form that you now have anyay. As noted, because you using a relational database system, you've likely in a good many cases do not actually want to copy these values, but just display them.
So you'll build a web query as follows:

Note the above, I simply dropped in all the columns of my main table, and then use a left join to bring in as many columns as I want from the table. In the above example, I needed two columns in addition to the ID that the combo box will store.
As pointed out, this technique works equally well in non web based applications and I often use this approach in regular Access, and it a good deal less work than attempting to use column functions – and this is a especially the case when building reports.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
jakedrew
post May 22 2011, 03:58 PM
Post#3



Posts: 47
Joined: 16-May 09



Thanks for the suggestions < Unfortunately, I am trying to display a list of all possible transaction types from the transactions types table for a new entry in a transactions table. The method you suggest above would not provide me with all records from the joined Transaction Type table, correct? I need to have the user click on a listbox or combo-box and see a list of all of the possible transaction type values from a transaction type table that could be updated at any point by other users. The transaction type table also carries 2 other fields of information (Count Type and Data Type) that I need for edit checks on the values being added into the form.
This part of the form(s) is not yet completed, but the image should give you an idea of what I mean:
Attached File  GRASS_ex_1.png ( 40.28K )Number of downloads: 21


The values in the drop down come from the Herd Transaction Types table, but the new record is going into the Herds Transactions table. Currently, the first field of the drop down (hidden using column widths = 0") actually contains the following:
Attached File  GRASS_ex_2.png ( 5.47K )Number of downloads: 15

These values are then populated into some hidden controls on the form that are passed along to the Herds Transactions table in the new record. Example below:
Attached File  GRASS_ex_3.png ( 18.75K )Number of downloads: 16

The After Update event uses Set Property to populate the hidden controls values as follows:
Attached File  GRASS_ex_4.png ( 20.81K )Number of downloads: 11

I am just looking for a simpler way to get to Columns(1) and (2) without going through all of the steps above. If I was just displaying information for review, I would definitely use the approach you suggested above. Unfortunately, I am not :/ Now, here is my big complaint about Access 2010 Web Databases. None of this would be needed, if I was allowed to TURN OFF the cascading updates that occur on Access Web Table Lookup Fields. If that were the case, I could just use web datasheets and lookup fields to easily add new records (as I have in many cases). Example below:
Attached File  GRASS_ex_5.png ( 38.93K )Number of downloads: 7

The problem I run into with the example above is historical reporting. See, if I use lookup fields (with cascading updates that I cannot seem to turn off) on historical data, I run into the situation where historical transaction data gets altered every time a transaction type, ranch, cell, pasture, or herd assignment is updated. This makes the current reports correct, but the historical reports incorrect as they would reflect today's transaction picture and not the transaction picture at the time the record was cut.
So, in closing, if anyone can tell me an easy way to get Columns(2) from a listbox / combobox, OR how to turn off cascading updates for lookup field in an access web database table, my life would be a lot easier <))
Thanks everyone for your quick responses and brilliant thoughts!
Go to the top of the page
 
AlbertKallal
post May 22 2011, 09:50 PM
Post#4


UtterAccess VIP
Posts: 2,723
Joined: 12-April 07
From: Edmonton, Alberta Canada


The technique I outlined above will work fine in your case.
should point out this not different using a list box or combo box, the idea and suggestion above still stands.
The only issue I pointed out is that the above approach allows you to display the additional columns from the other table without any code at all. Now it's possible in your case that those values could change over time, so you do want "real" copy of the values from those other columns. As I pointed out to just display and use those other columns, you have a relational database here, and you do not need to copy the values in this case.
I've uploaded the above sample where I display the two additional columns from my combo box. Note how in the form when you select a value from a combo box, the three column values are displayed below, and the three column values are displayed without any code at all using this approach.
Below those three values, I provided a copy button, just in case you want to realize how actually copy the values if in fact you do need to copy them.
So, the screen looks like this:

HAs noted if you do in fact really need to copy the values from the other table, then in the after update event of the combo box you can use the code I have behind the button in the above web example. The few simple lines of code to copy these relational columns to real columns in the main table would be:

The above thus shows how to use the fact that you have a relational database here.
In the past in regular access when you had a combo box that selects the part ID value, then when you built to report to display the part description or additional columns you did not have code in the form to "copy" the part description value, but you used the above approach of a query to pull in these additional columns (at least I hope you been doing it this way!).
If you had three or four combo boxes on that form that store some ID from other tables, then you would have to join in three or four tables into the query so you can display those additional columns in the report or form.
I'm simply pointing out that this is a longtime technique that works well in web based applications, but was also commonly used in client only applications.
Give the little sample I attached a try. Simply select a value on the combo box, and note in the form how the additional columns values are displayed for you.
About the ONLY possible detail you've not revealed here is perhaps your combo box is not bound to a column in the current form. The above solution and suggestion assumes the combo box is bound. If you do not have such a column in your current table the form is based on, then you can do one of the following:
1): simply add this additional column to the main table the form is based on, and it might be a case where you really do not need this column for later use and you never really did have to store it, but using as such allows use very easy grabbing of the additional column values.
2): Write some code that works like a dlookup as a data macro, and call that code from the form. This approach I would consider if you really do not need nor want the ID value from the listbox saved. This approach would also mean you do not need to base the form on a query you build. So, you can write code to pluck values from a table, and knowing how to do this is great to have part of your tool bag of tricks, but let's get the above approach working first before we try this second idea.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
Attached File(s)
Attached File  ColumCopy.zip ( 57.14K )Number of downloads: 26
 
Go to the top of the page
 
jakedrew
post May 23 2011, 12:43 AM
Post#5



Posts: 47
Joined: 16-May 09



This is great information! I have been doing some testing with your approach, and I think I may have stumbled across a Access Services Database bug??? At a minimum, the functionality we have been discussing appears to handle NULL join values differently, once the database has been published to SharePoint. Hopefully my screenshots will help explain.

1. I modified your database form just a tad to see how it behaved with selections from a list box, since this is similar to my situation. What I wanted to test was how the linked text boxes behave when a null value is selected (since this occurs often in my data). When the database was local, I was happy with the results. See below:

attachment=46426:listbox_1.png]
Attached File  listbox_2.png ( 80.12K )Number of downloads: 9

2. I published the database to office 365 and opened a copy of the published database on my desktop. When I selected a record with a null color, the form would no longer clear out the Column 1 and Column 2 text boxes. I am guessing this is a bug since the published web page version on SharePoint behaved as expected? Screenshots below:
Attached File  listbox_3.png ( 93.49K )Number of downloads: 8

Error Example:
Attached File  listbox_4.png ( 96.91K )Number of downloads: 12

I also uploaded your database with the extra form I created here:
Attached File  ColumCopy_Backup.zip ( 97.15K )Number of downloads: 20

Attached File(s)
Attached File  listbox_1.png ( 98.82K )Number of downloads: 10
 
Go to the top of the page
 
AlbertKallal
post May 23 2011, 02:41 AM
Post#6


UtterAccess VIP
Posts: 2,723
Joined: 12-April 07
From: Edmonton, Alberta Canada


I'm not really sure if it's a bug. You cannot expect to pull values from another table when the column we are using for the relational join is going to be all of a sudden a null value but other columns are not null?
On your test, it not a fair test, since the row of data exists, but the first column is null, and I simply did not make such an assumption.
If you use my original form as I have and simply blank out or erase the color selection (to a null value), then the related columns I have will also be erased and also then appear as blank/null. And in fact the copy code I have will actually still continue to function, and if that copy code was placed in the after update event, then it would actually copy the null values to the real local columns. It works fine in this case.
I'm extremely hard pressed to adopt some kind of design in which the first column you select from another related table is going to be somehow null or not used, and yet the other columns in that table have values as you are suggesting that need to be copied? This is hardly a workable approach even in regular access, let alone web based.
At the end of the day, the solution and approach here is rather simple. You want to tighten up your designs a bit, and simply ensure your designs have as the first column the primary key value from the other related table. You select a primary key value, and then copy the additional columns. In fact it doesn't matter if you're going to be using VBA, SQL joins, or whatever approach, you simply want to get this to resolve down to the primary key that identifies the row of data in the related table of which you're attempting to copy. In your case, if you have to set the first column be formatted as zero length to not display, then fine and the users will never see it. And MORE important is the rest of the columns if they are null or not does not matter in this case. So, you cannot use my suggesting if the first column used in the join is going to be null, but other collumns are not. As I pointed out, try eraseing the combo box to null, you will see the other boxes do go null, and they also do after being published.
HAs I stated, even in your case you might not want to even save the primary key value, but again in ALL cases you certainly want to use the primary key ID as the mechanism that allows you to connect and pull and identify out these values from other rows. This is much why we have a primary key to identify the row of data and this will remove any ambiguity that occurs here.
So the reason why your modifications are failing is because you using a value as the first column in the list box that does not uniquely identify the row of information in which you're attempting to copy values from. And of course my example works is because we ARE assuming we have a relational join to pull the data from the other table. As noted, the beauty of my design is this:
It is very simple (perhaps three lines of code here)
This design is scalable and practical and straightforward (you can for example add additional columns to the other related table over time, and then simply add some code to copy these additional colums by including them in the query - you not have to resort to a bunch of strange and bizarre delimited formatting and code to parse out the values (and you loose the data types, and this you REALLY REALLY do not want to do in web based applications - Access web servcies is MUCH more sentive to data type issues).

So your modification to the list box does not work as you have because you usiing a null value to identify the other row data in the other table, but the row still exists.
As I pointed out, you could call a data macro to return the additional values and then use code to stuff the returned values from that data macro into text boxes on your form. Once again however I am assuming at the very least like any relational database we would pass the primary Key ID to that data macro to then go out and fetch the information. This code thus then returns the additional columns of information and again we are free to put those values into text boxes on the form, but again we always use and assume a PK here.
If you take that list box in my example you've modified, and make the first column the pk ID of the table colors like I have, then the whole thing once again starts to work perfectly well.
So, to be clear, the issue here is not that a few of the columns are null (that's actually not the problem). However, if the first column in your selection process is not a primary key that we ARE going to use to IDENTIFY the unique particular piece of data that we are attempting to copy, then my suggesting will not work.
So, I thnk the best approach here is to simply identify the row of data you want a copy with a primary key, and you'll be just fine. As noted, you can use the sql join idea, or call a data macro to grab the values based on the PK value.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
jakedrew
post May 23 2011, 05:15 AM
Post#7



Posts: 47
Joined: 16-May 09



First of all, I definitely think this is a bug on Microsoft's part. I doubt they intended for the database to function differently in only 1 out of 3 instances (Access Local Database, Access Services Database, Access Wed Database). However, maybe Jeff Conrad could address this since I hear he was a tester for the Access Services Database platform? Don't get me wrong, for displaying data in cases when there is always a one-to-one match between tables, I think your suggestion is great! However, I will be using the alternate method described for adding new records where fields with Null values are a possibility. My current implementation of this form now uses both methods together (thank you very much, for your contribution btw!).
ext, regarding the following comment:
"I'm extremely hard pressed to adopt some kind of design in which the first column you select from another related table is going to be somehow null or not used, and yet the other columns in that table have values as you are suggesting that need to be copied? This is hardly a workable approach even in regular access, let alone web based."
Ototally agree, but I don't believe that is what is happening? Please correct me, if I am missing something here. In the database you sent, the query (you created) has a left outer join between the two tables. So, when that query is executed, all customers appear in the result set. Colors also appear, but only for the customers who have a "MyColor field" color selected. As you can see in the screenshot below, there is no case where the "first column you select from another related table is going to be somehow null or not used, and yet the other columns in that table have values" ?
Attached File  listbox_1B.PNG ( 50.12K )Number of downloads: 11

My point is that the "left over values" you see in the screen shot I previously posted are from the prior record's values, not a "null or not used key with yet other columns in that table that have values". This definitely appears to be an error on Microsoft's part.
The test I designed is also very valid test considering the requirements of my application. There are many cases where one might have a Herd that is not assigned to a Pasture or Cell. In those cases the ID field in the Herd Transaction table would be Null. If I implemented the suggested approach "as is", the bug I have described above could produce inaccurate results when adding new records to the table. I use the list box on my web form to allow the user to select a specific herd. When they do, I pull in all the specifics for that herd and make a copy for the historical record. In the situation where a Herd is not assigned to a Cell or Pasture, I can't have left over data sitting in those boxes from the prior record, and me writing a bunch of code to clear them out each time defeats the purpose.
For instance, consider the situation when you are adding a new color record when using the form I provided in your test database. If the user were to click on several people deciding which one to use, (as is the case with Herds), you could end up with a new record that has no value for MyColor and text values for Color and Descr in the new record. I don't think this is a flaw in your suggested approach either. I think this is bug that needs to be fixed by MS. I realize that the problem may sound strange when applied to your color database, but it becomes quite obvious when I click on a Herd in my own form and the CELL ID from the prior record is left sitting in the text box for a Herd that is not assigned to any Cell at all.
Thanks for all of your thoughts
Go to the top of the page
 
AlbertKallal
post May 23 2011, 09:41 AM
Post#8


UtterAccess VIP
Posts: 2,723
Joined: 12-April 07
From: Edmonton, Alberta Canada


Ah, ok, sure, but we are talking about the first column used in the combo box sql source also. In the above, each row has a PK id, but we NOT using that for the first column in the listbox anymore in your example.
So, if we are to apply my suggested rule to the above qryCust table, then we would have to use the ID column from table customersM in the above, and not tblcolors ID value as we are now using. If you do this, then tblColors ID could be blank, and what I suggest will however work (but, we now must left join in that query to the main form and NOT table tblColors as we have).
So I am saying that the first column of the listbox has to be the PK of the row of data we going to pull from via the left join. The color ID in the above is the PK of colors, but we not using that for the combo/listbox source anymore. However, if we use the pk ID from the row qryCust, then as noted we are free to pull color id from this row.
You switched the listbox from using tblColors as the data source to a query called qryCust for this listbox. This means for this to work we now would have to join in the query Cust to the qry the form is based on. And we would thus not left join in tblColors anymore. So, if you do this, then once again everything works fine again.
I do agree 100% agree that some kind of re-query is not occurring correctly when you do NOT follow the above. However, I am not too surprised that a form with a join between two tables does not update when you use a listbox based on a different table here. The Access query system simply cannot detect that something has changed here and one is going two query deep in this case. In fact, testing shows I see this issue crop up in client side – I did not even have to use web version for this problem. (and I am not really surprised as such).
So, just to assure you (and everyone else here). The issue you point out does exist. My spider sense tells me this is how this stuff just works as much as this is a bug. However, I not going to throw cold water here. This is inconsistant, and it is MOST fair to state this is a bug. I tapped out time wise for the next few days, but I could/should write this up as a bug.
So, I will limit my suggest to it working when following above rule.
Keep in mind that we also not considered the second suggestion to call a data macro. So, in place of the left join for the form, if I had 4 or 5 values, did not want to have to save the record before the copy of data (which we have to do now with above), then I would use the data macro idea. So which choice here will much depend on the needs here. This data macro idea also means we do not have to join in a query to the form.
The data macro idea is thus a great fallback position if above becomes un-workable.
So for everyone here, the poster is correct that Access should detect that the ID changed. And it should blank out the extra columns, but it simply does not. Perhaps some type of save record, refresh etc. could fix this, but I would not want to have to worry about the extra code. It also might be since we going more the one query deep here (we using a query that then again has to left join in other values from another table – as noted, even in client apps, this is problem).
HAs a final tip:
Note that a data macro can use a web query as its data source and this means you can use the queryCust in that data macro to return multiple columns of data when you call that data macro. So, you can use the same query for the listbox and the data macro, but then again, you have to be able to identify what row in the combo box selected here and again use a unique id in that listbox. So we really back to as a general rule always using a PK value to pull selected values. However, with a datamacro, that ID value would not be limited to the first row as per above for a left join idea.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
jakedrew
post Jun 1 2011, 10:28 PM
Post#9



Posts: 47
Joined: 16-May 09



One follow-up question. When I publish your database (unchanged) to the web, the textboxes stop populating with data on a new record. However, this is not the case when I am working with the database on my desktop. Unless you have other suggestions, it looks like I will be going back to my original approach for everything of that form :/
I'm a little disappointed with the differences I see between the display and behavior of forms on the desktop vs. the web. In many cases Google Chrome seems to do a better job making my forms look closer to their desktop counterparts than IE 8 or 9. I feel like the compatibility checker should be catching a lot of things the deeper I get into development. I hate that I cannot publish my entire project to the web at one time without SharePoint blowing up. I have to load table, queries, reports, then forms (only a few at a time) on both Office 365 and Accesshosting.com to eventually get a successful publish. I also see a plethora of little annoying "glitches" on the internet side in general, such as access displaying the value Stocker as ;#Stocker;# in a multi-value lookup field or half a button being cut off on a subform once it gets published as if the form is too big, yet displaying perfectly on the desktop.
Ochose the Access Web Database for this project, because I thought it would be faster to implement than my other alternative which was MySQL with PHP or ExpressionWeb, which also seemed like over-kill considering requirements. I'm having second thoughts about this decision now. If I were to build this exact database (non-web) in regular Access (with VBA etc), I also think I would have been done in a few days. I hope these little bugs get fixed soon. If this is truly the way Access Web Databases are supposed to work, I'm just not impressed.
Go to the top of the page
 
AlbertKallal
post Jun 2 2011, 05:28 AM
Post#10


UtterAccess VIP
Posts: 2,723
Joined: 12-April 07
From: Edmonton, Alberta Canada


True, but did you notice that hitting the copy button, the code works?
actually did not realize or even know the example I provided does not work when you tested this on a blank record in a web form.
However you have kindly provided everybody here with the information that when the record exists the code works. You've also provided everybody here with the information that when the record does not exist, the code fails.
So the above simple information suggests that a save record command would thus fix this issue. So just insert a saverecord command in the combo box after update event and what we have will work just fine.
When using Access with any kind of server system such as SQL server, you'll also find it rather common that you need to force a record save to generate the pk ID else the system will not work.
Well at least you have a simily on that statement. Before you consider that original (difficult) approach why not consider the second fall back position? I mean, you kindly provided the information to everyone here to allow the first example to work. However, if for some reason you do not want a save record or you do not like the first solution, then why not attempt to try the second suggestion I made? Taking this all or nothing solution approach to any software development process really backs you into a corner fast. You need to be flexible here. In fact I personally prefer the second suggestion of using a data macro, and I think it's a bit cleaner than the first suggestion. Do feel free to ask how such code for the data macro will look, as you might like this code approach better then the query idea.
So you can avoid (for some reason) the idea of a form based on a query if that your goal and use the 2nd idea.
There's no question that using access web services is less forgiving than the client side. However, in access we always had to learn what works well, and what does not.
Omean some users get away for using sendkeys in access applications for years. They then move the application to a slower or even a faster machine. Now some form loads faster or slower, and the focus is different and those keys get gobbled up by the incorrect form. The result is inconsistencies and lack of reliability in the application. So we over time learn to avoid sendkeys. There's just so many little things we learn to advoid. So the same goes for Access web. It is a mistake on anyone's part to assume that there's not going to be some new learning curve in adopting access web services.
I guess in my case, the tradeoff for me is learning a few things to avoid is far less work and time then having to learn a whole new development platform.
While there are many changes in adopting development process of using access web services, there is a rather large set of things from combo boxes, the update event model, sub forms, continuous forms, "where clause" etc. are all LONG TIME honored access development features. And these concepts and development paradigm transfer very well over to the web development system. In other words, there is a lot of things we can and do utilize here and do so without much of a learning curve.
However, I also admit that a lot of things that we've done over the years do not transfer well. So, I think that's where the difficult challenges exist. And a good number of these challenges change since we're actually using a different architecture. While the architecture is very different in access web services, it's still the correct design choice made for the product IHMO.
I also 100% agree that the compatibility checker, and even the debugging side of things is quite weak in this development system. And as you note, often there's a disconnect between the client side, and the server side. These disconnects do need to be tightened up, as this issue is what causes developers likely the most grief.
When I look at some of the hand coding HTML and scripting mess that people have used to cobble together some web based systems, we are in MUCH better shape IMHO.

Access web services in many ways has that brilliant Access ease of use. You can place a simple button on a form to launch a report with a filter and do so with about two lines of code. In other systems this type of simplicity and launching a report with a filter is really painful and takes far much more work and code then what we have with Access. And in fact on that other system, you cannot even be assured as to what kind of report writer system you're going to have to launch!
I find that when I stay within the constraints of the access web system, it's a really wonderful system.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@ms
Go to the top of the page
 
jakedrew
post Jun 2 2011, 10:35 AM
Post#11



Posts: 47
Joined: 16-May 09



I did notice that the Save, Refresh, and Requery commands all cause the new record to lookup the values. Unfortunately, since it is a new record, this also causes error messages on all my required fields as well i.e. "You must enter a value in field A". I imagine all my field and record level validation rules would fail as well in this situation.
The main reason I have been avoiding the data macro is that each time I write a data macro and place it in an On Load or After Update event, I suddenly get a new little message box that pops up each time the event fires which says something to the effect of "Please wait for server processing" ... Although the original approach seems complex, it is very fast and causes no visible delays. You mentioned the fact that I lose the data types as a concern, but the values are being placed right back into bound text boxes on the form. They are all being accepted when the record is saved with no issues (text, date, and numeric). I admit that Columns(1) would be a much easier way to get at that data. However, parsing the value out of a delimited string it not entirely too complex.
I guess it Have you experienced the "Please wait for server processing" delay on data macros? <
Go to the top of the page
 
AlbertKallal
post Jun 2 2011, 11:31 AM
Post#12


UtterAccess VIP
Posts: 2,723
Joined: 12-April 07
From: Edmonton, Alberta Canada


So, the reasons you give for not wanting to save the record is an excellent answer. I had anticipated this possible answer, and therefore that's why I included the second suggestion to call a named data macro.
should stress, I do not mean to call an table event macro, but a named table macro.
Ok, now armed with some good reasons as to why we do not want to save the reocrd, let's now adopt my suggested plan B.
And you should well note, that I also stated I somewhat prefer my second approach.
Oshould also point out that you will only see the processing delay the VERY first time the named macro is called since it is at that point in time it is compiled.
So, to make this work, we first switch the form data source back to the main table (we are NOT going to base the form on a query anymore).
Next step is to write a named table macro.
In the example we been working with, we want to pluck out from the table tblColors the two columns (color and description) based on the passed PK id.
The named table macro code will look like this:

Note in the above we set the macro to receive a parameter pID (this is going to be the PK id of the row from the table we want to look up). In the above simple macro, we simply set the two return values we want. In fact, I often setup this type of code for most tables (and I also tend to setup a delete routine based on the passed ID since then this allows me to by-pass the built in prompts when I want custom delete messages).
Ok, save the above. Now lets call that above macro from the combo box after update event. The code to pull the values will look like:

So, as I stated you can see that this second approach is quite clean. We in effect trade using a query for that of having to write some code. This type of trade off is quite reasonable in this case.
So we now have two reasonably workable solutions. While the first solution is not applicable in your scenario due to the save record issue, it still a great simple solution for the readers here and one that really doesn't require any knowledge of coding. (except for one save record command as we both found out).
However in those cases where the person is willing to write a little bit code that quite much works like a VBA "lookup" function, then the second scenario also works quite well. I should point out that between the query example, and calling the names table macro, I think the query example runs a little bit smoother and quicker in a browser form.
However, there is not much of a noticeable delay when calling the named table macro either. As as noted, once the code has compiled, then the delay "message" will not appear at all, and that includes subsequent form loads later on. It is only during testing debugging if you modify the code, then again the one Time Processing delay in compiling of code will occur.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
jakedrew
post Jun 2 2011, 08:52 PM
Post#13



Posts: 47
Joined: 16-May 09



Another frustrating difference between forms in web vs desktop access is the fact that forms bound to queries do not have the same editable fields once they are published to the web. It appears that all fields in the second table become read-only. This is not always the case in desktop access. Take the database you provided above, for example...
On desktop access, Column 1 (tblcolors.color) and Column 2(tblcolors.description) are editable. However, once published to the web, these fields are read only. Very disappointing. I have been able to macro and data macro around these issues in my own application, but I just expected a little more from my favorite little Microsoft database product, I guess...
Go to the top of the page
 
AlbertKallal
post Jun 2 2011, 10:46 PM
Post#14


UtterAccess VIP
Posts: 2,723
Joined: 12-April 07
From: Edmonton, Alberta Canada


There is no question when you start venturing into things like editable queries with a join, then some extra caution is required here.
However, in your case you were asking to copy the data, so at least this is not a issue for your question here.
Osuppose part of the issue here is that people are making the assumption that the queries built in the client system will work identical on the server side.
In fact, those left join columns ARE editable in the client even after publishing, but NOT web side.
You have to keep in mind you using the local data engine to edit that data client side, and THEN the changes are sent up to the server.
The problem is not what we expect from Access, but what are we to expect from the database system we are connecting to.
When you use access with oracle, Access does a pretty good translation and most queries will work, but some things will not.
When you use access with MySql, Access does a pretty good translation and most queries will work, but some things will not.
When you use Access with x, or y, or z, or SQL server, or FoxPro, or whatever, then most things will work, but some queries and things will work somewhat different because you are connecting to a different database system.
Same goes for using Access and SharePoint.
Now there may be reasons for not assuming the above, but we are NOW armed with the information that you shared here. So it quite much clear to all us developers here that we need to make the SAME assumptions that we had to make with every other database system that we could connect to in the near 20 year history of Access. Thus we simply need to be on the same guard that some differences will exist when we do this like we always had to.
However, at the end of the day, we explored some great possible ideas here. We found that the query approach was not very good for your case because of the save issue, but it still in our bag of tricks for use when it makes sense.
A great developer tip is to always must focus and zero in on a solution. When you encounter a mountain, you can choose to climb over it, tunnel through it, or perhaps just run around the side and avoid the mountain altogether. Or maybe you can charter an helicopter. We do not care about rocks, streams, or big bears. The goal is the goal, and everything else does not matter!
I guess I'm always looking towards a solution as the goal and everything else is to be side stepped and not hit head on. (so I am the kind of personal that will run around the side of the mountain to avoid it ).
And, we did eventually come up with the data macro "plan B" of which I assume worked quite well? (or did we get side tracked too much exploring the obstacles on the mountain?).
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
jakedrew
post Jun 3 2011, 12:03 AM
Post#15



Posts: 47
Joined: 16-May 09



I was / am looking for the most efficient way copy multiple columns from a listbox control on a web form. Now, I know three different ways to accomplish this task < The code under the copy button is not an optimal solution for my situation due to the fact that it requires use of the save record command in order to obtain the data. Unfortunately, this is problematic for a form that inputs new records into a table with required fields and validation rules.
I was VERY pleased to find that the "server processing" message goes away after the data macros compile! However, the data macro approach was a little problematic at first due to the fact that my listbox was unbound. It does not seem that the current value of a listbox cannot be passed to a named data macro unless the listbox control is bound. I even tried sending the unbound listbox value to a tempvar first and passing the tempvar to the named data macro with no success. When the listbox is bound to the table, the approach does work as described. I am not convinced that it is faster, and I think it requires a little more code than the approach I originally described. I also never have to move to a new record in the table (until I want to) with my original approach since the listbox can remain unbound.
I have learned a ton from this discussion. So far, it looks like I will be using a mixture of both approaches. I am using a named data macro to write updates to the 2nd table in my record source that becomes read-only on the web, and parsing a set of concatenated fields I delimited in my unbound listbox's recordsource on new record entries.
I should have a website up in a month or so with a read-only version of this database. I will send you a link, when I have something completed to share with you < Thanks for sharing all of your knowledge!
Go to the top of the page
 
AlbertKallal
post Jun 3 2011, 11:02 AM
Post#16


UtterAccess VIP
Posts: 2,723
Joined: 12-April 07
From: Edmonton, Alberta Canada


Actually, both combo box and list box when unbound work fine for me.
Keep in mind that if you UN bind a control, then if your ever watched lost in space
Danger Danger Will Robinson!
The solution here is to set the data type via code.
So, in the parameter being passed to the data macro, just use:
=cdbl([Name of listbox or comb box])
In fact, for a combo box you "could" use the format tab and set the data type as general number.
However, for a list box, you do not have the ability to set the data type by using format tab of the property sheet.
This issue is also very critical when it comes to dates.
So another dev tip of the day is if the control is unbound, then be on extra guard for setting data type.
When controls are bound, then the issue is much reduced because the control takes on the data type of the bound column, but when un-bound then the data type is not set.
This does mean that macros are RATHER sensitive to this data type issue where as VBA tends to "cast" the data type for you and not complain when you test a string against a number.
So the macro is failing when unbound due to data type issue. Cast the type, and it will work fine.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
jakedrew
post Jun 3 2011, 06:42 PM
Post#17



Posts: 47
Joined: 16-May 09



Thanks, that did the trick. I also noticed that, if there are no form controls bound to any fields from the form's recordset or if the form has no recordset assigned period, then all of the listbox and combobox values become locked. You can see the values display, but you cannot select them. On desktop access this is only true, if the form has no recorset assigned. However, once published, it seems that at least one control must be bound to the form recordset for the listboxes and comboboxes to unlock... Have you experienced this?
Go to the top of the page
 
AlbertKallal
post Jun 4 2011, 11:50 AM
Post#18


UtterAccess VIP
Posts: 2,723
Joined: 12-April 07
From: Edmonton, Alberta Canada


My unbound forms with a combo box or listbox work just fine for me, so no I have not seen or experienced that issue at all.
lbert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
jakedrew
post Jun 5 2011, 09:33 PM
Post#19



Posts: 47
Joined: 16-May 09



I figured out that the problem occurs with the removal of the vertical scroll bar. I suspect the error occurs when the drop down needs to go past the edge of the subform. I'm not sure how to fix this except to leave the vertical scrollbar on the form?
Screenshot with vertical scrollbar:
Attached File  scroll_1.png ( 20.51K )Number of downloads: 10

Screenshot without vertical scrollbar:
Attached File  no_scroll_1.png ( 14.13K )Number of downloads: 8
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th February 2019 - 03:13 AM