Linking a Key field from one form to another

[donkey89]

Possibly a misleading title, I'm not sure but please help if you can !

Okay so I have two tables both with appropriate forms: Customers and Orders, both are linked by a unique field being CustomerID.

I have added a button that allows me to go to Orders form, from the Customers form however I can't seem to figure out two things, firstly.. How to make it so that when the button is pressed it always goes to create a new entry into the orders form, and also how to make it "autofill" in the CustomerID, as its unique and has to be entered in order to continue, manually remembering this number and entering it each time could turn out to be a nuisance so I'd like to automate it as much as possible.

Is there a simple solution or is this a little advanced for a beginner like myself?

~Markus

 

[missinglinq]

Not really advanced, but it'll require just a little VBA code in the forms' code module.
In the Customer form

Private Sub Go2OrderForm_Click()
If Not IsNull(Me.CustomerID) Then
  DoCmd.OpenForm "OrderForm", , , , , , Me.CustomerID
 Else
  MsgBox "A Customer ID Must Be Entered First!"
 End If
End Sub

In the Order form

Private Sub Form_Load()

If Not IsNull(Me.OpenArgs) Then
  DoCmd.GoToRecord , , acNewRec
  Me.CustomerID = Me.OpenArgs
End If

End Sub

Just replace the various object/field names with your actual names.

Linq ;0)>

 

[donkey89]

Thankyou for your reply missinglinq,

With a bit of patience your code did the job aside from one problem that I am now facing.. Perhaps a design floor on my behalf.

It won't allow me to "copy" the ID so to speak into the order form. The error message I receive is "You can't assign a value to this object" I can only guess the reason for this is because the field "ID" which relates to a unique autonumber field, used as a Customer reference, is indeed a autonumber.

I'm unsure of a solution for this besides changing the ID field to a number which is manually ended, however I feel this could lead to inconsistant data rather fast?

If anybody has a solution or advice it would be greatly appreciated!

~Markus

 

[missinglinq]

The CustomerID on the Customer Form should be the Primary Key, and apparently is an Autonumber, which is fine.

On the Order Form, the CustomerID would be a Foreign Key, and needs to be defined, at the Table level, as a Number, Long Integer. Go into Table Design View for your Order Table and change its Datatype to Number. This should fix your problem.

Linq ;0)>

 

[donkey89]

Thankyou once more Missinglinq!

It appears to working as intended, but I'm once again having a slight problem which I think is down to my design.

The problem is if I open up my "main" form which has buttons linking it to the orders form and customers form, then go through to customers form, add a new record and then go to the button we have discussed which takes me to a new order with the customers ID filled in, it works the first time I do it but then if I go back through the now opened forms and repeat the above process, adding a new customer and then going to create a new order based on ID, it simply takes me to the order we just added, rather than opening a new record and filling the ID in.

I have noticed if I close all the forms open, and re-open the main menu form and go from there on it works for one time again. It may be worth noting that I do have a save button on both my order and customer forms, and I have been saving the new data each time I have added something.

My only guess could be that because I'm not closing the forms after each use it isn't updating perhaps? so without changing everything to perhaps pop-ups I may be stuck, hopefully that isn't the case though!

I apoligise in advance for the rather large post, I couldn't seem to describe it enough!

Thankyou in advance,

~Markus