| Author |
Topic |
|
whill96205
Starting Member
46 Posts |
 Posted - 2004-06-21 : 16:55:44
|
Help - I'm ignorant!We have a SQL database with a classic Order-Entry configuration (you know, the many-to-many relationship between Orders and Parts), and the users create new Orders by way of a MS Access form. This form displays data values from an ORDER table, and also has a data grid that displays related data from an ORDER-DETAIL table. This grid just operates like a spreadsheet right now – users have to enter everything manually, including all the part data (we don’t have a PART table yet… but I'm working on it!)So, after I create a separate PART table, I want to modify the data grid so that users can open up a new ORDER record with the Access form, type a PART # into a row of the data grid, and then that row of the data grid will automatically populate itself with data from the PART table (description, etc.) How do I go about adding that kind of functionality to this data grid?Thanks to anyone who can steer this noob in the right direction!  Whill |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-06-21 : 19:48:19
|
| What sort of datagrid are you using? I assume it's the Access subform-type datagrids based on what you're saying. If so, the standard Access grid subforms are limited in their functionality - especially when you're trying to do something non-standard like you are. You would be better off using a MSFlexGrid on your form, as you would do in VB. You have infinitely more control over layout and content, but you will need to code the database reads and writes yourself. Basically what you can do is: Put some code on the cell change event to check if there is a value there. If so, populate the next x cells with the display contents.There are also a number of 3rd party grids that would be better suited to your purposes that the standard Access/VB ones. There are ways of doing what you want with Access's standard grids, but I think they're a bit dodgy and wouldn't recommend. |
 |
|
|
whill96205
Starting Member
46 Posts |
Posted - 2004-06-22 : 10:29:14
|
| Timmy,Yes, they are currently using an Access subform.Where might I obtain MSFlexGrid? Is it downloadable? Any good resources out there I could use? I haven't the slightest clue as to how to utilize VB with SQL. I could really use some obvious direction if it comes to that.Or is there someother way to do what I want to do in Access without using a "grid" at all?--Whill Willing to learn... |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-06-22 : 19:14:25
|
| First of all, you need to be running the Access Developers edition (available in the Office 2000 Developer) before you can include external ActiveX controls on your forms.I'd suggest you get yourself a good book on Access development using SQL. I can't recommend any off the top of my head, but the Wrox series of books are generally very good.In particular, you need to get familiar with the intricacies of ADO programming.As for doing without a "grid" - you could possibly use Access continuous forms, but this doesn't really offer much more than the grids anyway. All it does is give you a bit more control in the formatting. |
|
|
|
whill96205
Starting Member
46 Posts |
Posted - 2004-06-22 : 21:05:51
|
| Timmy,Thanks for the feedback.Related question: The datagrid currently in use on the Access form is a regular Access subform. It operates like a small spreadsheet, like a direct "window" to records that all exist within a single table (Order-Details). Can a datagrid like this also be used to display data columns that are from *different* tables?--Whill |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-06-22 : 21:31:30
|
| A subform can access data from one table or query. If you use a query, you can include columns from whatever table you want. The issue with your situation is getting the item details to update after the user enters the part number. Like I said earlier, this can be done but I would be worried about integrity issues. |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-06-22 : 21:38:58
|
| If you want to pursue it, here's a solution:Create a new query that looks something like this:SELECT O.OrderItemID, O.ItemID, I.ItemName, I.ItemDesc, O.Qty, O.PriceFROM OrderItems O LEFT JOIN Items I ON O.ItemID = I.ItemIDThen, create a new datasheet subform that uses this query as it's datasource (use the Autoform: Datasheet option in the New form dialog).Enter an item ID number in the ItemID column. Tab to the next column and the item name and description are displayed. You can then change the ItemID (the part number) field into a combo box that reads the part numbers from the Items table. HTH,Tim |
|
|
|
whill96205
Starting Member
46 Posts |
Posted - 2004-06-23 : 18:06:48
|
| Hey, thanks Timmy. I may give that a go. I did discover earlier that I could use a query as the datasource for the subform. (Microsoft's classic "Northwind" sample database does it that way!) So at least I don't have to have my hands tied when it comes to displaying the data I want.I'll check in again if I have any further issues going forwrad.--Whill |
|
|
|
|
Data grid on an Access form