Expert Advice needed please :):)

[One2OneComputers]

Hi all, (first hello as im new here),

Ok, i have a few queries ive tried a few differnt ways but i must admit i am finding it hard switching my brain from excel to access, lol.

firstly, im using microsoft ACCESS (2007 PRO).

i have created an invoice form containing the usual fields, i.e.

Inv No, Date, Surname, Address, Post Code, Home Tel, Other Tel, etc etc.

my table is named Sales

my query being going through design view where would i put the code? i thought in default mask but when i look in form view still no number apppears... here is the code i have used.

=Nz(DMax("[Inv No]","Sales"),0)+1 (and is this code correct?)

also;

i am familiar more with using Vlookup in excel for when ive been using Sku codes with a database to fill in corresponding cells for example:

i have seperated fields in the following manner (the only way i know how)

amount1/sku1/item1/price1/less1/total1
amount2/sku2/item2/price2/less2/total2

and this carries on to accomodate the purchase of say 10 items

now im thinking along the lines (as i am a first timer at this access designing stage), of making yet another form which will up date a table named;

Price List

this of course will have x amount of sku codes in the table already and the rest would be updated via the new form.

so basically when someone for instance is looking for a product of course they will query that database, which will then produce the sku code, the sku code would then be put into the sku1/sku2/sku3 etc section of the invoice form which would cause the Item1/Price1 etc to be auto filled, like i said ive done this easily within excel, but am now looking at access as the best way of doing this.

my other query i believe would be quite a simple one for you guys and that is at the bottom of my form i have place a Finish button, i would like the document to forexample, save onto the table, print 3 copies and then close that form.

Any help with this would be greatly appreciated!!!

 

[RuralGuy]

Have you looked at the NorthWind example that comes with Access? It has a lot of good development tips in the way they put it together.

 

[One2OneComputers]

in reply

Have you looked at the NorthWind example that comes with Access? It has a lot of good development tips in the way they put it together.

Sorry been real busy!

Yeh got a few pointers from there, thanks for the info, but i must adnit sometimes these programs dont explain things, like why thats how you do that you know? "anyone can memorize a script, its acting the play thats hard", I'm self teaching at the moment so its all pretty new to me. I've worked with Delphi years n years ago, but apart from that, im from scratch.

My ultimate goal is to build a fully operational program that can cater for pretty much all industries whether it be small retail, large retail (more than one shop) stock in, stock out. invoices (and all aspects of), payments etc. also keepinh record analysis showing profit margins, costs, turnover, staff, the whole shabang so to speak, so i suppose quite an in depth system. Yes im biting a hell of a lot to start out with, but the harder it is, the quicker i learn, so any tips would be absolutely fantasticfor example, i work well with structure, for instance set ways of doing certain areas.

Justin

 

[tranchemontaigne]

VLOOKUP
Take a look at the MS Access DLOOKUP function. I think it offers similar functionality to the MS Excel VLOOKUP function. Personally, I often write a short little ADODB search routine to provide the same kind of functionality and tie it to a click event on a form to populate a form control with a look up value

QUERY FORMULAS
When writing queries in MS Access design view, embed the formula in the field name cell.

FINISH BUTTON
Often I create an unbound form (with unbound controls) that will perform an ADODB recordset query based upon an ONCURRENT or ONLOAD form event. The ADODB recordset will then have values called to populate data controls on the form. My equivalent to the FINISH button on the form is a SAVE CHANGES button that calls an update query (docmd.runsql) to overwrite values stored in the table with values read from the controls on the form. If you want you can have yoru form open with no values displayed, expect someone to complete the form, then click the FINISH command button to call an append query (docmd.runsql)

If you don't want to write the SQL manually, so that it could be called by the docmd.runsql method, you could always write a query in design view using the expression builder to browse to the form and data control on the form to use in the field cell of the query design view. Calling a named query would use the docmd.openquery method.

Hope this helps
________
TOYOTA WIKI HISTORY

 

[One2OneComputers]

reply

VLOOKUP
Take a look at the MS Access DLOOKUP function. I think it offers similar functionality to the MS Excel VLOOKUP function. Personally, I often write a short little ADODB search routine to provide the same kind of functionality and tie it to a click event on a form to populate a form control with a look up value

QUERY FORMULAS
When writing queries in MS Access design view, embed the formula in the field name cell.

FINISH BUTTON
Often I create an unbound form (with unbound controls) that will perform an ADODB recordset query based upon an ONCURRENT or ONLOAD form event. The ADODB recordset will then have values called to populate data controls on the form. My equivalent to the FINISH button on the form is a SAVE CHANGES button that calls an update query (docmd.runsql) to overwrite values stored in the table with values read from the controls on the form. If you want you can have yoru form open with no values displayed, expect someone to complete the form, then click the FINISH command button to call an append query (docmd.runsql)

If you don't want to write the SQL manually, so that it could be called by the docmd.runsql method, you could always write a query in design view using the expression builder to browse to the form and data control on the form to use in the field cell of the query design view. Calling a named query would use the docmd.openquery method.

Hope this helps

Thanks tranch, I'm just working on something else at the mo but ima have a good read of that and get straight back to you, (busy busy busy)

 

[sintacks]

Hello all,

I am also switching over form Excel VBA to Access, though slightly less enthusiastically as one2one. I am attempting to create a dynamic search form via ADO objects in the exact same manner as described by tranche. On the two forms I am attempting to do this I am stuck with two different questions.
1. Is there a limit to the maximum length of a SQL command? I am attempting to query ~25 fields spanning 7 tables with one giant SQL FROM...INNERJOIN...WHERE statement. I know there must be a work around but have yet to find any information online or in books.
2. What does it mean if I am returned an undefined recordset from a valid SQL statement? Is it that the query has returned zero results on a search? Both EOF and BOF return true and rec.RecordCount returns a value of -1.

Any help is greatly appreciated!!

Best,
E:<

 

[tranchemontaigne]

1. Is there a limit to the maximum length of a SQL command? I am attempting to query ~25 fields spanning 7 tables with one giant SQL FROM...INNERJOIN...WHERE statement. I know there must be a work around but have yet to find any information online or in books.
E:<

I've never quite figured this out, but can tell you that string lengths greater than 255 characters can be problematic. Sometimes building your SQL string so that it builds upon values already stored in memory lets you call an SQL string longer than 255 characters, but this will also fail at some point when your string gets too long. I don't know why, because you would think that a string variable would have a predefined amount of memory and that memory space would be static, but this is not the case. (Perhaps this has to do with passing values by reference and thereby bypassing th4e 255 character restriction because each memory register would contain less than 255 characters??)

I like using this latter approach because it makes the SQL statement more human readable.

strSQL = "SELECT "
strSQL = strSQL & "* "
strSQL = strSQL & "FROM "
strSQL = strSQL & "tblTABLE "
strSQL = strSQL & ";"

For complex queries you may want to build multiple recordsets, or iteratively redefine your recordset to evaluate lookup values, then pass those looked up values forward when defining a successive recordset. this should ultimately allow you the same output as if you had a long complex SQL statement. Surprisingly, this approach often improves performance dramatically when compared to defining a single recordset with a complex SQL statement.

2. What does it mean if I am returned an undefined recordset from a valid SQL statement? Is it that the query has returned zero results on a search? Both EOF and BOF return true and rec.RecordCount returns a value of -1.E:<

The best way that I have figured out to handle a null recordset is to create the recordset object, set a loopcounter variable, then loop through the recordset. By default, as soon as you create a recordset the recordset index points to the first record. In the event of a null recordset you will always pop an error if you try rst1.movefirst. Here is a sample code snippet you might find helpful in bypassing this error. Once you know that a recordset is not null, you can handle it as you normally would.

Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = CurrentProject.Connection
rst1.Open strSQL

'record count
lngRecordCount = 0
Do Until rst1.EOF
lngRecordCount = lngRecordCount + 1
rst1.MoveNext
Loop

'conditional handling based upon detection of a null recordset
If lngRecordCount < 1 Then
dblTodaysTime = 0
Else
dblTodaysTime = 0
rst1.MoveFirst

Do Until rst1.EOF
dblTodaysTime = dblTodaysTime + rst1![LogTime]
rst1.MoveNext
Loop

End If

________
Honda Cbr600F2 Specifications

 

[The_Doc_Man]

First, look up "Specifications" to see sizing limits. There are enough limits that the help files break that list up into several different pages.

Second, when switching from Excel to Access, the very first problem most people find staring them in the face is NORMALIZATION. If you have not read this topic before, NOW is the time to do so, before you commit yourself to one of the lower circles of Hell (probably worse than any Dante ever imagined...) I VERY STRONGLY advise that you do a Google / Yahoo / other favorite search on "Database Normalization" and read up on it. The Wikipedia (.org) site has a nice overview and some references. Your web search will turn up only about a gazillion hits, so limit your reading to some college / university sites you recognize and maybe a couple of vendor sites. There is the MS Access Help Files treatise on normalization, too.

Inv No, Date, Surname, Address, Post Code, Home Tel, Other Tel, etc etc.

This above little item just about guarantees you will need to address normalization (if you ever intend to sell stuff to the same customer on more than one occasion.

The forum also has lengthy discussions about normalization. But first do the web-based reading.

Now, let's talk BRIEFLY about the other issue you mentioned... the mind-set issues coming from Excel to Access. I will never criticize Excel users for having used Excel. But the moment you start to use Access as though it were Excel, you open yourself up for more messes than a room full of pre-school toddlers with dirty diapers.

Excel encourages what is called "flat-file" thinking, where the relationships between elements have little or no meaning. Access has the ability to generate a business model - but first you must organize yourself. Here is an "old programmer's rule" - Access will never tell you anything you didn't tell it first. Before you lay out too much code or queries or tables, look at your goals and determine how you would CAPTURE the data needed to reach those goals. Work backwards to determine what you must capture.

From the front-side, look at your business model to identify entities. Let me give you a couple of examples: (1) Customers - who have names, addresses, contact info; (2) Inventory items - which have SKU numbers, locations, supplier information; (3) suppliers - who have supplier IDs, names, addresses, contact info; (4) Invoices - which have lists of <quantity, SKU> as well as customer ID; (5) price lists - which have SKU and dollar values. Every one of those elements is an element of your business model. Do you see the kind of thinking required?

The "Design" section of this forum has some decent articles on decomposing problems in a way to allow for better design. At the least, the articles show examples of how the "entity" breakdown occurred for others - and our feedback on what they published.

I know it sounds like I'm tossing you a lump of coal, but this is a TIMELY lump. The longer you wait to develop an understanding of normalization and entity relationships, the harder you will have to work when finally implementing this project of yours. Trust me, we understand what a monolithic pile of work such a conversion can be. I've done a couple myself. It's no picnic, but if you do the right kind of analysis up front, your job will at least be liveable.

As to the bit about a command button, look up VBA because when you say "I want to do a few different things based on one button click" you are ALWAYS talking VBA. The good news is that VBA can do what you describe with a series of DoCmd.{methods} - but I respectfully suggest that the design of your data is far more important to get right first.

 

[One2OneComputers]

First, look up "Specifications" to see sizing limits. There are enough limits that the help files break that list up into several different pages.

Second, when switching from Excel to Access, the very first problem most people find staring them in the face is NORMALIZATION. If you have not read this topic before, NOW is the time to do so, before you commit yourself to one of the lower circles of Hell (probably worse than any Dante ever imagined...) I VERY STRONGLY advise that you do a Google / Yahoo / other favorite search on "Database Normalization" and read up on it. The Wikipedia (.org) site has a nice overview and some references. Your web search will turn up only about a gazillion hits, so limit your reading to some college / university sites you recognize and maybe a couple of vendor sites. There is the MS Access Help Files treatise on normalization, too.



This above little item just about guarantees you will need to address normalization (if you ever intend to sell stuff to the same customer on more than one occasion.

The forum also has lengthy discussions about normalization. But first do the web-based reading.

Now, let's talk BRIEFLY about the other issue you mentioned... the mind-set issues coming from Excel to Access. I will never criticize Excel users for having used Excel. But the moment you start to use Access as though it were Excel, you open yourself up for more messes than a room full of pre-school toddlers with dirty diapers.

Excel encourages what is called "flat-file" thinking, where the relationships between elements have little or no meaning. Access has the ability to generate a business model - but first you must organize yourself. Here is an "old programmer's rule" - Access will never tell you anything you didn't tell it first. Before you lay out too much code or queries or tables, look at your goals and determine how you would CAPTURE the data needed to reach those goals. Work backwards to determine what you must capture.

From the front-side, look at your business model to identify entities. Let me give you a couple of examples: (1) Customers - who have names, addresses, contact info; (2) Inventory items - which have SKU numbers, locations, supplier information; (3) suppliers - who have supplier IDs, names, addresses, contact info; (4) Invoices - which have lists of <quantity, SKU> as well as customer ID; (5) price lists - which have SKU and dollar values. Every one of those elements is an element of your business model. Do you see the kind of thinking required?

The "Design" section of this forum has some decent articles on decomposing problems in a way to allow for better design. At the least, the articles show examples of how the "entity" breakdown occurred for others - and our feedback on what they published.

I know it sounds like I'm tossing you a lump of coal, but this is a TIMELY lump. The longer you wait to develop an understanding of normalization and entity relationships, the harder you will have to work when finally implementing this project of yours. Trust me, we understand what a monolithic pile of work such a conversion can be. I've done a couple myself. It's no picnic, but if you do the right kind of analysis up front, your job will at least be liveable.

As to the bit about a command button, look up VBA because when you say "I want to do a few different things based on one button click" you are ALWAYS talking VBA. The good news is that VBA can do what you describe with a series of DoCmd.{methods} - but I respectfully suggest that the design of your data is far more important to get right first.

In all honesty, the best answer to my reply, when it comes to structure and linking different bits of information together etc, creating sku lists, i am familiar with the concept. This is how i make things understandable for my self. i am going to attach a file that i built, (excel) see what you make of it, its basically what i need in access, but with for example, running invoice numbers, stock allocation (in out) updating databases etc etc. no doubt you have the know how of what i am trying to achieve. i could also send you my build so far (no laffin mind) ^_^.

p.s, couldnt upload the build so far, to big to send on here apparently. nm, send ur email if you wanna take a ganda.

Cheers mate

Justin
One2One Computers

 

[sintacks]

Hello,

Thanks for the help all. Great advice about the normalization, and it makes me feel better about spending all that reading up on RDBMS modeling. I believe I have organized my data into roughly 3NF save a few transitive references (I can't bring myself to make one-field tables).

Tranche, that code snippet is a gem. I definitely have worked out most of the kinks with the recordset creation - or so I thought. I keep getting an error when attempting to set the forms Recordset property to the record set I created. Ok great, so I think I can work around it by giving the RecordSource property the SQL string. Both to no avail, especially RecordSource idea because I lock myself out of the table when creating the SQL string (purposely, just didn't think about it). Because of your code snippet I know the Recordset I am creating in VBA is working, yet are there specific properties that make all Recordset objects not the same? The help file is not so great. If feel like this should be a simple (and crucial) concept that is me and my coding abilities on unfriendly terms.

E:<

 

[ajetrumpet]

Thanks for the help all. Great advice about the normalization

I would advise changing your name to syntax, or was that already taken?

 

[sintacks]

taken like a week before i registered.

 

[The_Doc_Man]

Unfortunately, I cannot look at your ZIP file because my primary site blocks downloads from general sources. (I'm on a military network.)