Creating a registration table list based on an existing table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ChuckW - 13 Dec 2007 13:38 GMT

Hi,

I have an access database that contains transactional tables which I cannot
alter. There is a table called invoice. This has ClientId, ClientName and
ItemNumber. The ClientID is a ten digit number while the ClientName is a
business name. There are no extra fields that I can use in it. I want to
create a form that will enable the staff to keep track of registration. A
registration is initiated when an invoice on a client is created with the
itemnumber being College. Right now, people are keeping track of attendees
using a spreadsheet but I want to create a table that contains these names
and somehow links to the Invoice table. I can create a data entry screen
that enables the staff to add people to these College events. I want it to
read the invoice table for item=College and then be able to add names that go
to a registration table. What is the best way to set this up?

Thanks,

Signature

Chuck W

Reply to this Message

Mark A. Sam - 15 Dec 2007 20:26 GMT

Chuck,

I'm not understanting this. Try rewording this describing each step then
what you need at a particular point, like:

I add a registrant
I want to create an invoice for the registrant with his name in the
invoice...

God Bless,

Mark A. Sam

> Hi,
>
[quoted text clipped - 18 lines]
>
> Thanks,

Reply to this Message

ChuckW - 16 Dec 2007 00:44 GMT

Sam,

I have an access database that is fed from quickbooks data. In this
database is a table called Invoice. Whenever an invoice is created in
Quickbooks, it writes a record in this Invoice table. Whenever a business
wants to sign an employee up for a class of ours, we create an invoice and
use the item number of College and Customer which is usually a business name.
The quantity is usually one but sometimes two or three. After we do this,
the staff that keeps track of this then opens up an excel file and adds
attendees names which are the employees of the businesses along with other
information about the attendees such as their company name, and type of
machine they will bring to the training.

What I want to do is to create an data entry form in access that will
capture these attendees. I want to make it so that whenever the invoice
table has college in the itemnumber field, a record is automatically added to
this new registration table that I want to create. It would populate data
such as BusinessName and registration date. The staff can then look for these
new records and fill the rest of the information such as attendee name,
cellphone and MachineType.

Thanks,

Signature

Chuck W

> Chuck,
>
[quoted text clipped - 31 lines]
> >
> > Thanks,

Reply to this Message

Mark A. Sam - 16 Dec 2007 12:35 GMT

Chuck,

Since the Invoice is created in QuickBooks, you have no control fromt that
side. I am assuming that the QuickBook table is an Access table or other
ODBC type that you can attach to.

This is what I understand that you want. You want to populate a
registration table in an Access database whenver an invoice is created for a
new Company.

If this is the case then you need to scan the Invoice table using VBA to
address the table or use an Append Query or both.

It is rather involved and out of the scope of this forum to do the
development, but I will describe to you how I would possibly approach this.

I would create a query linking the [Invoice] and [Registration] Tables,
probably by [ItemNumber].

Next I would create an 'outerjoin' to identify the records which have
companies listed in the [Invoice] table but not listed in the [Registration]
Table.

I would next either convert the query into an append query and run it
manually or use VBA code to scroll through the query recordset and add the
records. You could place the code on a button, in the Open or Timing events
of a form or other events. I don't know which would be best for your
situation.

I don't know if this helps you, but I hope it gives you some direction.

God Bless,

Mark A. Sam

> Sam,
>
[quoted text clipped - 65 lines]
>> >
>> > Thanks,

Reply to this Message