By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,313 Members | 2,798 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,313 IT Pros & Developers. It's quick & easy.

Need help generating a customer number in a form.

100+
P: 256
I have set up a form to contain customer info. From that form, I have a button "Show Order" that links it to a Order Number form. That order number form contains a subform showing order details (qty, items). When I am on the customer info form and click SHOW ORDER it only shows me the orders associated with that particular customer number. (That is good.) But when I want to enter a new order, how can I get it to automatically fill in the customer number?

I have all my relationships defined. And I am a new user, so be gentle. :-)

Email me at [Email address removed per site rules] and I will send you screen shots if it helps. They were too large to attach. (All I know is print screen-->paste in Paint.) I think if you saw my screen shots it would clear up any confusion.
Oct 13 '09 #1

✓ answered by NeoPa

Try :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Show_Orders_Click()
  2. On Error GoTo Err_Show_Orders_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "HOTSTICK_ORDERS"
  8.  
  9.     stLinkCriteria = "[CUST_NUM]='" & Me![CUST_NUM] & "'"
  10.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  11.     Forms(stDocName).MainFormControl.DefaultValue = Me.[Customer Number]
  12.  
  13. Exit_Show_Orders_Click:
  14.     Exit Sub
  15.  
  16. Err_Show_Orders_Click:
  17.     MsgBox Err.DESCRIPTION
  18.     Resume Exit_Show_Orders_Click
  19.  
  20. End Sub
Change MainFormControl obviously ;)

Share this Question
Share on Google+
28 Replies


NeoPa
Expert Mod 15k+
P: 31,434
I think SubForms handle this well enough.

Otherwise, for separate forms, a technique I use is to set the .Default property for the control within the form.
Oct 13 '09 #2

100+
P: 256
Can you tell me how to arrange the Default property? You can just use MainFormName, MainFormField (what I want to copy FROM), SubFormName, SubFormField (what I want to copy to).
Thanks.
Oct 14 '09 #3

NeoPa
Expert Mod 15k+
P: 31,434
As there are no fields on a form (there are controls - it's a really good idea to understand the difference as it saves tons of confusion) I will use MainFormControl & SubFormControl respectively.

However, I need you to explain which form we are dealing with here. I thought you were entering an order to start with (that would require the MainFormControl.Default to be set). I expect when the order number has been set up in MainForm it will not be necessary to set the default in SubForm. The linking should sort that out for you.

I also need to know if MainForm is guaranteed to be open already when the order number value is entered into the original form, or whether MainForm is opened from scratch whenever the Command Button Show Order is clicked.
Oct 14 '09 #4

100+
P: 256
Command Button Form contains customer info, customer number, and the command button. :-)
MainForm contains an order number, ship date. This is where I want customer number to get to automatically.
SubForm contains the order qty and items.

I click Command Button and open up MainForm. The SubForm is where I input Qty and Item...no control for a customer number there. So you are right, when the order number has been set up in MainForm it will not be necessary to set the default in SubForm. (I guess SubForm is really irrelevant.) But I want to get the number in MainForm FROM CommandButtonForm. Make sense? (I know these things are hard to describe.) Also, it's not the order number I need, just that customer number. Thanks so much!!

MainForm is opened from scratch whenever the Command Button Show Order is clicked.
Oct 14 '09 #5

NeoPa
Expert Mod 15k+
P: 31,434
@DanicaDear
Quite right. My slip-up.
@DanicaDear
You should be able to add this code after the call to open the form.
Expand|Select|Wrap|Line Numbers
  1. Forms!MainForm.MainFormControl.DefaultValue = Me.[Customer Number]
NB. I got a little confused with MainForm. I wasn't sure whether it should be the form the subform is on (which makes sense) or the one that the CommandButton is on (which doesn't make much). I have used it to refer to the form that has a subform. The other one is referred to as Me in the code anyway.

Let me know how you get on with this.
Oct 14 '09 #6

100+
P: 256
Everything you say is correct and makes sense. Can you tell me where I am supposed to put this code? I tried it unsuccessfully in the control source and default value fields of my properties box. Here is my specific code:
Forms!HOTSTICK_ORDERS.CUST_NUM.Default = Me.[CUST_NUM]
I have both controls named CUST_NUM because they both refer to the same table field. Could this be a problem?

Thank you so much for helping me out. I don't see how you have time to help helpless programmers like myself. :-) (I'm a structural engineer...far from knowing anything about programming.) But I am REALLY appreciative of the generosity of your time.
Oct 14 '09 #7

codegecko
Expert 100+
P: 533
DanicaDear,

Put NeoPa's code in the "Code" window for the Command_Click action and it should work.

Hope this helps.

codegecko
Oct 14 '09 #8

100+
P: 256
I tried to put my code where codegecko suggested. I get this error:
"Object doesn't support this property or method."
I also tried changing the first . to a ! still with no luck. Any ideas on why this would not work? It seems so simple: copy a value from an open form into the newly opened form. Much thanks.
Oct 14 '09 #9

NeoPa
Expert Mod 15k+
P: 31,434
Post the code you currently have behind your Command Button, that includes the call to Open the other form. If all the info you've given us so far is accurate then we will be able to tell you quite easily where to put the new line.

If it's where you've already tried, then we'll look more closely at the error message.
Oct 15 '09 #10

100+
P: 256
Here is my current code behind the button:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Show_Orders_Click()
  2. On Error GoTo Err_Show_Orders_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "HOTSTICK_ORDERS"
  8.  
  9.     stLinkCriteria = "[CUST_NUM]=" & "'" & Me![CUST_NUM] & "'"
  10.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  11.  
  12. Exit_Show_Orders_Click:
  13.     Exit Sub
  14.  
  15. Err_Show_Orders_Click:
  16.     MsgBox Err.DESCRIPTION
  17.     Resume Exit_Show_Orders_Click
  18.  
  19. End Sub
I tried to put the code after stLinkCriteria. I also tried a new line right after private sub. Please excuse how much I DON'T know. Where else should I try to put it?
Thanks,Danica
Oct 15 '09 #11

NeoPa
Expert Mod 15k+
P: 31,434
Try :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Show_Orders_Click()
  2. On Error GoTo Err_Show_Orders_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "HOTSTICK_ORDERS"
  8.  
  9.     stLinkCriteria = "[CUST_NUM]='" & Me![CUST_NUM] & "'"
  10.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  11.     Forms(stDocName).MainFormControl.DefaultValue = Me.[Customer Number]
  12.  
  13. Exit_Show_Orders_Click:
  14.     Exit Sub
  15.  
  16. Err_Show_Orders_Click:
  17.     MsgBox Err.DESCRIPTION
  18.     Resume Exit_Show_Orders_Click
  19.  
  20. End Sub
Change MainFormControl obviously ;)
Oct 15 '09 #12

100+
P: 256
NeoPa,
Still no luck. It gives me an error message but the form second form still opens and shows records. It is not putting the new value in. My specific line of code is now
Expand|Select|Wrap|Line Numbers
  1. Forms(stDocName).[CUST_NUM].Default = Me.[CUST_NUM]
Could there be any trouble from both fields being defined as [CUST_NUM]?

Alternatively, is there a way to create new variable = customer number, and pass new variable into new form? More or less, copying and pasting. I don't know how it all works so I'm basically at your mercy.
Thanks,
Danica
Oct 15 '09 #13

NeoPa
Expert Mod 15k+
P: 31,434
@DanicaDear
I could really do with knowing what the error message says.
@DanicaDear
I wouldn't expect that. No.
@DanicaDear
Yes. There is a parameter that can be used in the call to open the form (DoCmd.OpenForm()) called OpenArgs. I can't imagine this is worth pursuing at this time though. If we have a problem then we are just as likely to find the same one after expending the effort to do it a different way.

Let's look at the error message and go from there.
Oct 15 '09 #14

100+
P: 256
I am posting my code with the correct form/control names, just so we can refer to things specifically.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Show_Orders_Click()
  2. On Error GoTo Err_Show_Orders_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "HOTSTICK_ORDERS"
  8.  
  9.     stLinkCriteria = "[CUST_NUM]='" & Me![CUST_NUM] & "'"
  10.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  11.     Forms(stDocName).[CUST_NUM].Default = Me.[CUST_NUM]
  12.  
  13. Exit_Show_Orders_Click:
  14.     Exit Sub
  15.  
  16. Err_Show_Orders_Click:
  17.     MsgBox Err.DESCRIPTION
  18.     Resume Exit_Show_Orders_Click
  19.  
  20. End Sub
Here is the error message (the Open Form Command Button DOES open the form--and supplies the error message with it).

"Object doesn't support this property or method."

Also, I don't work on Fridays so it may be Monday before I am back to try your next suggestion. I appreciate your continued support. You are a very nice person. Thanks, Danica
Oct 15 '09 #15

NeoPa
Expert Mod 15k+
P: 31,434
It would really help to know which line of the code produced this error message too. For now I will assume it is triggered by line #11. The code I suggested.

This would be a little surprising of course (Not that I can't be wrong but this seems pretty straightforward). What type of control is [CUST_NUM] on the form HOTSTICK_ORDERS? I've been working with the understanding it's a simple TextBox. Is it?

PS. Please note my post in one of your other threads about using the CODE tags. It's quite important and I can't go on forever without issuing a proper warning for it. Questions would be asked in The House ;)
Oct 15 '09 #16

100+
P: 256
The form was created by a wizard. I'm assuming it is a text box. I have been through the properties and don't find any references to anything but a text box. I know the reason your code isn't working is because there is something bad somewhere that I have done, and I'm not aware of it and hence can't tell you about it. Is it bad to offer to email you my database? It's less than 4MB. Is it bad to write my email address on here so you can email me and I can reply with the attachment? (Wouldn't that be easier than keep on trying to tell you about it when I don't half way understand it myself?) :-(

Thanks for your note on CODE tags. I am investigating that.
Oct 19 '09 #17

NeoPa
Expert Mod 15k+
P: 31,434
@DanicaDear
When you click on the control in Design View and look at the Properties Pane, the type of the control is displayed in the Title Bar of it, with the name of the control. EG. Text Box: CUST_NUM.
@DanicaDear
Often the way. Frustrating to be unable to help.
@DanicaDear
It's acceptable to attach the database to the thread. I will include instructions here in case it helps :
When attaching your work please follow the following steps first :
  1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
  2. If you've done anything in step 1 then make sure that the problem you're experiencing is still evident in the cut-down version.
  3. Compact the database.
  4. Compress the database into a ZIP file.
  5. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.
@DanicaDear
Yes. That would be against the rules.

It is possible (though generally the etiquette would be to wait to be offered) to swap email details with an expert privately in a PM. If your database is particularly private I will arrange this with you. I generally prefer to avoid this though, as this tips the amount of my involvement and time upwards while possibly saving some for the member requesting this higher level of help. I don't share details with just anyone as I value the privacy of myself & my family. As I said though, I would be reasonably comfortable to share these with you if it were necessary and you couldn't post the database normally for some reason.

As an expert myself, and also as an administrator where I try to look out for the interest of our experts, I would far rather see members learn to communicate in a clearer fashion generally. I'm not referring to your threads here Danica, but there are many that could be handled and answered quickly and simply, if only the OP (Originating Poster) would take the time to explain their problem clearly.
@DanicaDear
Excellent. If you need any help just ask. I'm always happy to help members learn the ropes here. It's in all of our best interests after all.
Oct 19 '09 #18

100+
P: 256
Okay, I verified it IS a text box. I didn't realize I could attach my database. I will attach it here. After compression it was just 1MB so I didn't zip (and also because I'm not sure if I have software or how to zip.) I notice you have replied to all 3 of my questions...I do appreciate it. This is the database that pertains to all 3 threads. I will upload it my post for Delerna as well. Thanks for the great tips!!!!
Oct 20 '09 #19

100+
P: 256
Nevermind...I see now I *must* zip. I'll work on that first thing in the morning and get that DB attached! :-)
Oct 20 '09 #20

100+
P: 256
I wish all my problems were this easy to figure out! ;-) File is now attached.
Attached Files
File Type: zip SHOPS1019.zip (131.4 KB, 49 views)
Oct 20 '09 #21

Delerna
Expert 100+
P: 1,134
Dancia
I've jumped in here because I had your database from your other post and saw the solution.

Put the code on the customers form back the way it was.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Show_Orders_Click()
  2.  
  3. On Error GoTo Err_Show_Orders_Click
  4.     Dim v As String
  5.     Dim stDocName As String
  6.     Dim stLinkCriteria As String
  7.  
  8.     stDocName = "HOTSTICK_ORDERS"
  9.  
  10.     stLinkCriteria = "[CUST_NUM]=" & "'" & Me![CUST_NUM] & "'"
  11.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  12. Exit_Show_Orders_Click:
  13.     Exit Sub
  14.  
  15. Err_Show_Orders_Click:
  16.     MsgBox Err.DESCRIPTION
  17.     Resume Exit_Show_Orders_Click
  18.  
  19. End Sub
  20.  

now go to the code page for the orders form and paste this in there

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Me.[CUST_NUM] = Forms(stDocName)![CUST_NUM]
  3. End Sub
  4.  
probably should put some error checking code in there.

I'll leave that as an exercise for you :)
Oct 20 '09 #22

100+
P: 256
YAY! This worked. Thank you so much Delerna and NeoPa.
I used your hints and code to do a couple other neat things as well. I am so happy this is working now. :-)
Oct 20 '09 #23

NeoPa
Expert Mod 15k+
P: 31,434
I'm sorry to say that I have to disagree with Delerna here. Now it seems I will have to explain in detail (I was hoping to avoid but as you already have found it to work I'd better).

First let me say that I have discovered the fault with my suggestion is that I referred to the .Default property instead of the .DefaultValue one. I'm confident this will work for you now.

Putting the code, as Delerna has, in the Form_Open() event procedure does work fine. The only differences between adding it there instead of where I suggested immediately after the call to open the form are :
  1. It means an event procedure must be created even if there is no other code to go there.
  2. There is a remote possibility that the form can be run without the calling form being open. In this case it will fail.
As there are no benefits to this approach I would recommend leaving it where it was after the open of the form. There are no drawbacks to this approach.

As far as simply setting the value of the control goes (as suggested by Delerna), this is not a very robust approach as it sets the buffer to dirty (it makes Access think there are changes entered on the form) even before the operator has done anything. This can lead to empty records being saved by mistake, or even just save prompts that confuse the operator. It really is a much better idea simply to set the .DefaultValue of the control. and let it be populated only when the operator has started to enter data into the form. It will still be saved properly when a record is added, it just won't cause problems on those very rare occasions when it's not.
Oct 20 '09 #24

Delerna
Expert 100+
P: 1,134
No need to be sorry NeoPa
Delerna is my alias not because I am a learner
but because thats my passion...
The day I stop learning is the day I don't want to be here.

Your reasons are noted for consideration and sound valid.
I must say,with my appoach, which I have taken many times in the past
I always use Form_Load do much more than just set a field to a default value
I never allow user's to open a form in any way except my way
And I use an unbound textbox as a filter for the query that drives a subform.

It has never actually occured to me that .DefaultValue would work as a solution in situations such as this. I tested on Dancia's database and it does indeed work.
Oct 20 '09 #25

NeoPa
Expert Mod 15k+
P: 31,434
That's just one of the many hints I picked up from other experts here :)

Before I started posting here I only ever used forms to contain Command Buttons. Never to maintain data.
Oct 20 '09 #26

Delerna
Expert 100+
P: 1,134
@NeoPa
Indeed.....In fact you may have just answered a problem I had in an access database many years ago which I never got around to fixing.
It's the only database (out of many many databases) I have ever written that had
The mysterious empty record...Where are they coming from???

DanciaDear
This all highlights, what I consider to be the most difficult thing in any form of software development.
Discovering the adverse effects (if any) of your solution decisions.
Oct 20 '09 #27

NeoPa
Expert Mod 15k+
P: 31,434
@Delerna
I'm not sure I understand when you are getting these empty records Delerna. I'd love to help if I can.

** Edit **
Sorry - it's late here :(
I see where you're going now. I hope that's helped you to appreciate what sometimes goes on behind the scenes. The worst thing is that these messages can be blocked - whereupon Access simply assumes you DO want to save anything in the buffer :( Never seemed like a very sensible assumption to me.
Oct 20 '09 #28

100+
P: 256
I want to thank you both for your time and advice. I got NeoPa's code to work for me too and in fact it solved another issue I was having. I owe you big time for this one......you are both so smart and generous. Thanks again.
Oct 21 '09 #29

Post your reply

Sign in to post your reply or Sign up for a free account.