Northwind as template

buratti

Registered User.
Local time
Today, 08:27
Joined
Jul 8, 2009
Messages
234
Ok, so I am using the Northwind database as a template for my database. I am basically just using the Customers, Orders and related forms/tables. I imported all the tables, forms, modules, queries, etc from Northwind into a new db, and modified as necessary. Everything is working GREAT, (VBA code old and new, formulas, queries), but Northwind is doing something that I cant seem to get mine to do and can't figure out why!
In the Northwind database, when in the Order Details form, and trying to enter a new order for a NON-EXISTING customer (by typing in a new customer name in the customer field), it tells/asks you that the name is not in the list and would I like to add it now. When you click yes, it opens the Customers form, and the name you just typed in the order details form already appears in the First Name and Last Name fields of that form.
In my database, if I enter a NON-EXISTING customer, it asks me the same thing, and if I click yes, it opens the Customers form, but it is just blank, not entering the name as northiwind did.
The ONLY difference in the databases (surrounding this specific problem at least) is that I renamed the Company Name field in Northwind to Account Number in my database. Other than that all the field names are the same.
What I need is when I start to enter a new order, I start by entering the customers account number, and if it is not in the list, open the customers form and enter the rest of the customer info, but have the account number already there. Just as the Northwind does but with the customer name instead.
On an additional note... the message box that asks if I want to add to the list seems like an Access system message box and not a user/Northwind created function/message box.
I'd be willing to just write some new code if that will be easier than trying to debug mine, but I wouldn't know what code to write or where to put it.
 
Check the underlying table in the form that you enter new customer info, maybe it still points to the old field name.
 
Thanks for the suggestion, but all the field names in the table haven't even been changed, except for the company name, which is now account number, all others are exactly the same. I basically went throught Northwinds event controls line by line to see if I could find any code that did this task, and maybe see if I possible forgot it, but I found nothing. I'm assuming that this is dont through Access's system code or something.
 
Ok I think I found out what was/is the problem. I found an article explaining the List Items Edit Form property of a lookup (which is apparently new to access 2007) field and how it works in conjunction with an embedded macro in the onOpen event of the form being opened (link here). I am using Access 2007, but my database is in Access 2002 format. I converted to 07 format, and everything worked the way it should. But I created it in 2002 format because other machines that will be accessing this database (when finished) only haave Access 2002 on them. Is there an equivalent code or macro that can be written for the 2002 format that will acomplish the same task so I dont have to upgrade the other machines?
 
Re: help with this code to automatically populate fields

I was thinking oh how to accomplish this in the Access 2002 format, and I came up with something. I don’t know if it would be the correct way to do it, but theoretically it should work, but I just need help with the code and the syntax. This code would actually automatically populate more than one field in the customer detail form, which is an added plus. This is what I figured:
In the AfterUpdate control of the Order Details form set the value of the Customer Name, Address, Phone Number, and Account Number fields each to a separate global variable. Then after the user enters an Account Number that is not in the list, Access will state that and ask if I want to add it... I click yes and it opens the Customer Detail form in add mode. An embedded macro in the onLoad even sets the form to open in to a new record (as explained in an earlier post). I would then add a line/action to the macro to run VBA code. I would write the code to assign the Name, Address, Phone Number and Account Number fields of the current, new record to the global variables set before.

Here's my problem though. I can only run a "global" function within a module from a macro, and don’t know how to set the values of a current open form from within a global function.

If I were to put the code in the forms module it would be something like:
me.FullName = golbalVariable1
me.Address = globalVariable2
me.PhoneNumber = globalVariable3
me.AccountNumber = globalVariable4

How would I convert that code into a global function so it can be accessed from a macro?

OR, a possible easier solution is to remove the macro and set the OnLoad control to run code only. I can figure how to set the fields to the global variables like stated above, but I would first need to write code to navigate to a new record, but only if those variables contain data. If they do not, then just open the form regularly. What code will navigate to a new record when the form is opened?
 

Users who are viewing this thread

Back
Top Bottom