Creating a datasheet or continuous form in VBA

[tonygg]

Hi. I have created a single form and populated the fields in the form using VBA taking fields from many tables in the DB (I cant create a view or even a crosstab to capture all the data). The vba takes in a product ID (on load) and returns the data into the single form view. It populates all the fields correctly. I am putting the data in the display fields such as Me.ProductID, Me.ProductPrice etc. I would like to turn it into either a datasheet or continuous form that displays the data all of the products in the DB. I am struggling to see how to manipulate a multi row form and put the data into each record.

There must be away to index the records in a datasheet form and create new rows? I am thinking I can create a recordset from the DB or all the products and step through this recordset retrieving the ProductIds and then loop through the onload vba in my single form to get back the data for each productID/row. The issue is I cant see how to create the rows in the form and dynamically populate them.

Please could someone kindly direct me to a tutorial on it or at least some basic code to populate & manipulate the continuous form/datasheet.



Many thanks in advance

Tony

[ssanfu]

You should work through these tutorials. They will help you with your table structure and forms.
http://www.rogersaccesslibrary.com/forum/forum46.html

[tonygg]

Steve

thank you for the link. I have taken a look. I was hoping for something a little more specific. I just need a bit of advice on how to populate a continuous or data sheet form in vba BUT not from a view or table but by row by row building the fields. As I explained I have the fully working code for 1 row but don't know how to wrap it into populating multiple rows on the form.

Again any help with the code example or framework would be appreciated?

Many thanks

tony

[ssanfu]

If you are using code to add data to a form, you must be using an unbound form.
Normally a form is set to continuous view and is bound to a table or query. All of the data in the record source is displayed in bound controls in the form.

With an unbound form, you are responsible (via code) to place the data into the controls and clear the controls - a lot more work, but better control. You would have to loop through the record set and put the data in the proper controls. You would need two loops: the outer loop for the vertical movement (rows) and the inner loop for the horizontal movement (the columns).

Would you post your db or code so I can see what you are trying to do?

[tonygg]

Hi Steve

As I was unsure how to create the datasheet I have created a new table on load of the form and populated it. The code is quite long (and slow). I have attached it for you. I am working with a 3rd party DB (SellerDeck - An online shopping cart) and if you add fields they get put away from the standard ones. The structure is:

on load of form:

1. create the new temp table
2. Get all records from the exist product tables

LOOP on all the product records:
3. Use the product Id to call a get data routine that brings together all the data into one set of fields
4. Write a row in the temp table with all the fields for that product

Display results.

I have a separate pop up form that shows a single rows data, validates any changes and writes any updated data for that row back to the original tables. I will not be using any bulk changes so I can control the integrity of the main tables.

What I wanted was rather than creating a table just to show the data in a datasheet and run conditional formatting on it to highlight problems with the data. I just need to have the code that puts the data I collected in the onload form into a datasheet rather than a table (of course any other suggestions would be most welcome)

I hope this makes sense.DB Load Form Backup VBA.txt

Thank you for your help

Tony

[ssanfu]

I just need to have the code that puts the data I collected in the onload form into a datasheet rather than a table

Just so we are talking apples to apples, it is important to understand that tables store data...period. Forms do not store data.
Forms have 3 main view modes: Single Form, Continuous Forms And Datasheet. There is also Pivottable, PivotChart and Split form (for A2007 and later).

You use forms to view the data in tables/queries (virtual tables).


You're right! That is a lot of code.

I am working with a 3rd party DB (SellerDeck - An online shopping cart)

I don't understand the process yet.
Start from the beginning - pretend I'm in line at the local Burger King and explain what you are doing. Where the data comes from and what you do with it?

[tonygg]

Steve

I understand that the table stores data. I don't need the data stored. I just want to collect all the product data that happens to be in many tables (not my doing) into one datasheet so we can see the fields together in an easy way and decide what to do to fix problems for each product. I will/do apply the changes to the underlying tables.

Hence I didn't want to create this temporary table. I just currently do not know how to just display the data.

Any ideas?

Many thanks

Tony

[ssanfu]

I don't know how else to say it.

OK, to display data (any data) in a form, you have 3 options:

1) Store the data in a table, even if it is a temp table. View the data using a bound form in datasheet view (or continuous forms view).
or
2) Open an UNBOUND form that LOOKS like a datasheet (text box controls arranged in a grid) and write the data to the controls using VBA. This involve a lot of code.
or
3) If the tables from the third party are related, create a query, adding all of the related tables. Add the fields of interest to the query grid and execute the query.


I'm sorry I don't understand. You keep telling me what you are doing or want to happen, but I don't understand the background. I know nothing about your dB or the third party dB.


You have a database from a third party (table structure?). Now what? Do you add a form to the third party dB with the code?

You say you don't want to store the data, but you are recreating a temp table "TonyProductTable" (every time you open a form) and putting data in it. To me, that is storing data.


Post some pictures, post your db, describe/explain the whole process, start to end.
I just don't have a frame of reference.

[tonygg]

Sorry I am not explaining very well. I will post some screenshots of what I have working now.

Just to clarify. Of your 3 options I would like to do option 2. I am only using a table to store the data as I don't know how to do option 2.

I am using VBA to go behind the third party product and directly manipulate the tables it uses to store its own data. It makes it easier to manage data on bulk. I do not have a separate DB. The table I added is in the same DB.

The first screenshot shows the datasheet created from the temporary table. It brings together all the product data and highlights errors.

The second shows the pop up form that I use to correct the data and then save it back to the original tables (as well as the temporary table so it refreshes the conditional formatting) showing where the errors are fixed.

I hope this helps. I guess if I cant get any code to populate the rows in the datasheet I will just stick with the temporary table.

Many thanks for your patience.

Tony

[tonygg]

Actually the screen shots are the other way around. The first actually shows the pop up. The second shows the datasheet I was trying to create without the use of a table (as it is full of un-normalised data)

[tonygg]

On reflection regarding the unbound form if I have understood correctly it would only simulate a data sheet and require manually populating field by field. This sounds very onerous and makes the temporary table look like the best way to go. Sorry it took me a while I just expected there would be some excel like functionality to manage data sheets. Thank you for clarifying.

may I bother you for a follow up question. As you can see from my sheet there are certain rows that have fields highlighted. These conditional formatting rules are simply column x <> column y. i would really like to be able to have a pop up to allow the user to select to see all rows or just rows that have errors (there are 3 different types of errors). All the data to filter the rows is contained in the sheet.

My my question is what would be the simplest way to achieve the filtering on load so I can ask the user which view he wants to see all rows, rows with error 1 in them, Rows with error 2 in them or rows with both errors in them?

hopefully this is a bit simpler to achieve. I was considering creating different views on the table but I will still need to create and populate the tonyproducttable on open as it gathers data from across the system.

Any suggestions would be gratefully received.

Many thanks.

Tony

[ssanfu]

Aha!!
You have a query in datasheet view. What is the source of the query? The table "TonyProductTable" or many tables from the third party dB?

One thing you have to do is forget most of what you know about Excel. An Access datasheet (query or form) is not anything like an Excel worksheet. No how, No time - they are completely different animals.


Post #9

I hope this helps. I guess if I cant get any code to populate the rows in the datasheet I will just stick with the temporary table.

The only way you can get "rows to populate the datasheet" is to add rows to the underlying query source (ie the tables that are used to create the query).
You *can* use code to add records to the table(s).

On reflection regarding the unbound form if I have understood correctly it would only simulate a data sheet and require manually populating field by field. This sounds very onerous and makes the temporary table look like the best way to go.

Exactly.

may I bother you for a follow up question. As you can see from my sheet there are certain rows that have fields highlighted. These conditional formatting rules are simply column x <> column y. i would really like to be able to have a pop up to allow the user to select to see all rows or just rows that have errors (there are 3 different types of errors). All the data to filter the rows is contained in the sheet.

My my question is what would be the simplest way to achieve the filtering on load so I can ask the user which view he wants to see all rows, rows with error 1 in them, Rows with error 2 in them or rows with both errors in them?

I created a dB with 1 form as a demo. It is meant to look like your query and popup form - I "rolled my own" datasheet...
There are 2 buttons - one to filter the errors and one to show all records per your request on how to filter records.
There are fields in the header that show the data in the current selected row in the detail section. This is like a (modified) split form.
Look at the query in design view. The query (query3) is the record source for the form. (Yes, I didn't use proper naming conventions )

Module 4 has the code that you posted in Post #5. There were some errors that I commented on.
My comments begin with '<<== ##". Look through the code.....


Don't have to use my suggestions..... I'm just saying....

[tonygg]

Steve. Thank you so much for doing all that work. It was going well beyond what I could expect. You are a star��

i have taken a quick look and will work through the coding changes today.. The filtering seems straightforward. I guess I can use column comparisons in the filter criteria.

thank you so much once again.

Tony

[ssanfu]

I guess I can use column comparisons in the filter criteria.

In the query, have a column that sums the errors (column comparisons):

TotalErrs: Abs(([Col1]<>[Col2]) + ([Col3]<> [Col4]) + ([Col5]<>[Col6]))

Comparison 1 = ([Col1]<>[Col2])
Comparison 2 = ([Col3]<> [Col4])
Comparison 3 = ([Col5]<>[Col6])

If any of the comparisons are true, a -1 is returned
Add the comparisons - a negative number will be returned.
Then convert to positive by using the ABS() function. (absolute)

Then you can set the filter to 1, 2 or 3.

[tonygg]

Steve

Sorry for late reply. I missed this reply in my email.

Thank you so much for your help.

I have filtering sorted now on the sheet.

Given I had got the product sheet working I tried to do the same for orders but this time I was able to use the existing tables. Mostly worked but I cant seem to get Me.Orderby to work on the views. I will post a separate question on this.

Many thanks once again for your help.

Tony