Command Button for subform

[cubbyamber]

First let me say, I am sorry for all the questions lately. I thought once I got to the forms part of my database it would be smooth sailing boy was I wrong.

Anyways,

I have a form called Contractors and a Subform called ContractorsSubform. I need to add two sets of buttons one for the main form and one for the subform. Since I figured the subform is the hardest, I am working on that first. So here is my question:

I have an Edit Order Details button which works fine for the subform. I am trying to add a save Order Details Record button for just the subform (along with a message box asking if they are sure they want to save) however, I can't get it to work for the subform. I have tried various codes that I have found on this forum as well as in the help in Access and I keep getting errors. Can anybody help? I have attached the database to be looked at.

Tracy

P.S. I am not very good with VB but I am trying to learn.

 

[RuralGuy]

You *do* understand that if your MainForm and SubForm are bound that access will save any changes without asking, right?

 

[cubbyamber]

Yes. I am just doing this as an extra precaution so that the user has to actually think about whether they want to save it or not. The person that I am making this for I'm not going to tell him that access automatically saves the record for him. I will tell him he has to click the save record button.

I also just decided that I am going to make the same buttons for both forms instead of two rows of buttons that way it's a little bit less confusing for the user.

So I got the save button figured out but now I want to do an add record button and it brings up a new form. I haven't gotten a chance to search the forums for this before I got your post so can I ask you how do I get the add record to bring up a blank form and subform?

Tracy

 

[RuralGuy]

Hi Tracy,
By bring up a blank form do you mean just move to a new record on the MainForm?
Look into DoCmd.RunCommand acCmdRecordsGoToNew

 

[cubbyamber]

Yes. I want it to bring up a blank record on the main form and subform if possible. I tried what you suggested and it still didn't bring up a blank form. So I attached the database and see if I did something wrong.

Tracy

 

[RuralGuy]

I've seen this db before and you've changed things. Why don't you have the controls on the MainForm bound to the fields of the underlying query as you have done in the SubForm? I would strongly recommend you make the PrimaryKey of the Contractors table an autonumber rather than a text field. You can still have your text ContractorsNumber if you need it but the table does not depend on it. I'm sorry Tracy but it seems you are going backwards in your design. Are you just experimenting with different approaches?

 

[cubbyamber]

I have changed things. I'm not trying different approaches, I'm just trying to get it done now. At first I enjoyed it but now it is just a thorn in my side but I want to get it done.

The fields in the form are unbound because after I got the autofill with the combo box figured out, everytime that I opened the form and then closed it, it changed the contractor number and I didn't know how to fix it. I found the solution to that on one of the posts here and they said to change the control source so that's what I did.

I don't know I'm so confused making this now. I have asked so many questions on this forum that I don't want people to get annoyed with me. What would be the difference if I made the primary key an autonumber instead of the contractor number. I need to be able to sort all the information via the contractor number.

If you think I am doing something wrong please tell me. As long as you don't mind 50 questions afterwards. This is my first and I think last attempt at making a database. At least for a while.

Tracy

 

[RuralGuy]

Hi Tracy,
Give me a little time and I'll rework the db a bit and get you moving again.

What would be the difference if I made the primary key an autonumber instead of the contractor number.

The Primary Key would then be created correctly and automatically without user intervention, a big plus.

I need to be able to sort all the information via the contractor number.

You can still do that and you can still index on the Contractor Number to speed things up.

If you think I am doing something wrong please tell me. As long as you don't mind 50 questions afterwards. This is my first and I think last attempt at making a database. At least for a while.

Not to worry. It can be a little frustrating at times. Questions are not a problem; that's why most of us get on these forums in the first place. I for one enjoy watching people learn.

 

[cubbyamber]

Thank you. that would be greatly appreciated considering I have been working on this for three weeks now. Everytime I think I'm getting somewhere I hit another roadblock. Thanks again.

Tracy

 

[RuralGuy]

Hi Tracy,
I'm not real certain what your Contractors form is to accomplish but it looks like too much to me. See if I got this a little closer to what you were looking for.

 

[cubbyamber]

That is great. It is what I wanted. Here is what the contractors form is suppose to accomplish:

The guy I am making it for is a PI and he gets "orders" from clients of people he is suppose to find which he in turn sometimes sub-contracts out to other people so I was making a form to keep track of any and all information that the subcontractor has found on the "order" I've toyed with different ideas on how to do this but this is the best way that I came up with.

thank you again. I have to do this on the clients form as well so now I get to learn and study what you did.

Thanks again,

Tracy

 

[cubbyamber]

RG,

I am working on the other form that I need done called clients. I have gotten it so that it automatically selects the right record but I can't get it to do anything with my subform. I am attaching it so you can hopefully tell me what I am doing wrong. I also noticed that the autonumber (clientId) is working on the Clients table, the clients query but not on the order details query or table. I don't know how to fix this. I think that might be my problem.

Thanks

Tracy

P.S. I need the contractor number and client number on my tables and queries because I have numerous reports and queries tied to those numbers.

 

[RuralGuy]

Tracy,
You had it except the OrderDetails table didn't have the ClientID field populated yet. You have to start off by populating it by hand. You are goint to have problems with this design. How do you get the ContractorsID field populated when you are using the Client form and how about the ClientID field when you are using the Contractors form. You need to rethink the design.

 

[cubbyamber]

RG,

I'm not sure I am following you about using the contractor Id when using the client form and Client Id when using the contractor form.

The clients and contractors are two different things. They are not connected to one another.

The Client sends a list of People that they want my client to find. My client in turn gives some of the work to the Subcontractor. The only way that they will be connected is through order number.

So for example:

Client AB gives me John Doe to find (ie he quit paying his bills). I then come up with a Order number for John Doe. I then subcontract it out to Contractor Number C1 and he tries to locate John Doe. So therefore, the clients and subcontractors are not connected in any way.

I hope this is making sense.

While looking at my database again I think I understand what you are saying. The reason that I have my client numbers and my contractor numers in the same rows is because this is a dummy database and I didn't want to keep retyping different things for Contractor Numbers and Client numbers so I just used the same "dummy" names for each of them so that I can experiment each time I do something different.

Tracy

 

[RuralGuy]

Tracy,
You have a field for both ContractorsID and ClientID in the [Order Details] table. How do you plan to keep them populated properly with your current design? Access only populates it when you create a new record in a SubForm. By the way, you should stop creating names with embedded spaces. Use CamelFontNames for readability instead. Those spaces in names *will* give you grief some day. Here's some links you might want to bookmark and read.
List of reserved words in Access 2002 and Access 2003
List of Microsoft Jet 4.0 reserved words
Special characters that you must avoid when you work with Access databases

 

[cubbyamber]

Okay, I see what you are saying.

I made an Order details form so that when my client gets new orders he can place them in the order details form and assign each order to a specific subcontractor then it populates the table.

Tracy

 

[RuralGuy]

OK Tracy. Are you off and rolling again?

 

[cubbyamber]

RG,

I just sent you a PM let me know what you think.

Tracy