Button Macro Question

[scampbell70]

I would like to create a button that will open a new record to the currently selected record.

I am in customer 1's record and I want to add a new order. I would like to be able to click add new order and have the form pop up and the customer field already populated. I sure this is a simple problem but I am new to access and have no idea how to accomplish it.

 

[bob fitz]

You haven't told us about your table structure.
If you have a Customer table, an Orders table and an OrderDetail table then you could have a main form based on the Customer table with a subform for each of the other tables.

 

[scampbell70]

I have a Tbl with CustomerID, FirstName, LastName, Address, etc

I have another table

TblOrders

ProductID
OrderID
CustomerID
ShipmentStatus
ShipmentDate
ShipmentMethod

I created a one to many relationships between CustomerID in both tables

When I click open form it opens the orders form where I can view or edit orders. However when I create a button on the orders form to create new record it asks me to enter the customer name, I would like this to already be filled in if possible.

 

[bob fitz]

Where will you store details of quantity and how will you record orders of more than a single product

 

[scampbell70]

I am VERY new to access, I was using orders to store the information, is that a bad idea?

My thought was

Customer info table

Table with all my products

Everything else orders table

 

[bob fitz]

I suspect that you need to read up a little on Database design and table relationships.

IMHO you need at least four tables:

tblCustomers
CusID Primary Key
FName
LName
Address

tblOrders
OrdID Primary Key
CusID Foreign Key
OrdDate

tblOrdDet
OrdDetID
OrdID Foreign Key
ProdID
Qty
UnitPrice

tblProducts
ProdID
Product
UnitPrice

 

[scampbell70]

Okay, so once I set those tables up following that structure how to I create a button to open a new order to the current customer? And thank you for all the help.

 

[bob fitz]

If you can wait a short while I'll do a quick and very basic example and post it here for you.

 

[bob fitz]

As promised, I have attached a DB for you to take a look at. Very basic, as I said it would be but should be of some help to you in understanding db design and table relationships.
It automatically opens a form bound to the customer table.
I have entered three fictitious customers. The form opens at the first customer that I entered (Bob Fitz) and shows one order of three products.
At the top of the form is a button that will move to a new record when you need to enter a New customer. To the left of that button is a combo box which can be used to find any existing customer.

Ask away if you have any questions.
Hope this helps.

 

[scampbell70]

Thank you very much.

 

[scampbell70]

Bob,

If I upload a major project I am working on for my job would you look at the basic table structure and tell me if it is correct. I was using the customer database to teach myself what I need to know because customer examples seem to be all over the internet but I do not know how well it translates to what I am actually trying to do.

 

[bob fitz]

I would certainly look at it.

 

[scampbell70]

I think I did this all wrong and I was going to redo it before I uploaded it but I will just upload the one I been working on.

 

[bob fitz]

I think I did this all wrong and I was going to redo it before I uploaded it but I will just upload the one I been working on.

Nothing attached to your last post

 

[scampbell70]

I edited the post I did not realize the file was too big it is there now

 

[bob fitz]

Ok found your db. Now we're going to need some explanation of what what your business is and how and what the db is to be used for

 

[scampbell70]

I work for a mental health agency that provides mental health and substance abuse treatment to individuals who are incarcerated.

When someone comes in they are placed in various programs and each program can have several groups.

We need to be able to track patients by stay (IE. incarceration/booking number), by the judge, by county, and by the court.

We also need to know what program they were in during that stay, what groups they took, where they were referred to and if they followed through

This is my first attempt to build a database and I am self-taught by reading books, watching youtube videos and using forums as a resource so I am very appreciative of all your help.

 

[bob fitz]

I've had a quick look at the db you posted. I don't have a great understanding of your business model but even without that I would suggest again that you read up on table design and relationships.
I do not see the need for multiple Primary Key fields in your tables
IMHO when you have a one to many relationships between two tables, as I think you have between TblCounties (one) and Tbllncarcerations (many), you should be saving the Primary Key value from the one side in the many side table.
I would avoid using combo boxes in tables as you have in TblGroups for the field called GroupName.

Just a few thoughts for you to ponder on. Hope this hepls.

 

[scampbell70]

I figured I was all wrong about what I had done. I started working on it last night but did not want to post until I got your response. Here is a relationship table of what I have now is this getting better, or am I still all wrong. I have read three books and watched several videos on basic table design but most talks about customer databases and I am just confused and feel very stupid right now.

 

[bob fitz]

What you have now looks much better to me. Put all thoughts of feeling stupid right out of your mind. I still find this area difficult after many years of playing around with Access.
I would suggest that perhaps you start entering a few dummy records to see if/where your model fails.