Copy Customer_ID from Current Record to Another Form/Table

[CDF]

Hi,

I'm having trouble working out the following.

I've got a 2 tables (tbl_Customer & tbl_Vehicle) linked by a
Customer_ID field with a 1:Many relationship (1 customer (Customer_ID
(PK)) can have many vehicles (Customer_ID (Fk))).

On the Customer Form the Customer_ID field is filled by Autonumber.

After filling in the customer form you have the option to add a
vehicle to this customer by clicking an 'Add Vehicle' button which
saves the current record and pops up a modal form with Vehicle
information.

This button has the following vb code in the onclick property:

--------------------------------------------------
Private Sub AddNewVehicle_Click()
On Error GoTo Err_AddNewVehicle_Click

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
stDocName = "Vehicle Installation Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec


Exit_AddNewVehicle_Click:
Exit Sub

Err_AddNewVehicle_Click:
MsgBox Err.Description
Resume Exit_AddNewVehicle_Click

End Sub
--------------------------------------------------

My problem is that I can't work out what code to 'write' to
automatically take the Customer_ID (which was automatically generated
by autonumber) from the Current Record of the Customer form and Paste
it into the Vehicle form so that you don't have to manually type it
in. I assume the code will go AFTER the 'go to new record command',
but I'm not a programmer so am not familiar with 'VB syntax' but
understand it when told the correct code. My VB 'experience' is
trolling these newsgroups and finding the code I need, but I cannot
find an answer to this question which I'm sure is common place in
relational databases.

I appreciate any help.

 

[Al Campagna]

CDF,
Your having this problem because your using a separate pop up form to
collect Vehicle data.
You would save yourself a great deal of of problems by using a subform
on the Customer form to enter associated multiple vehicle info.
Given...
Relationship CustID to CustID (1 to many)
Main form based on tblCustomer, and a subform based on tblVehicles
Subform LinkChildField = CustID
Subform LinkMasterField = CustID
Every record you enter into the Vehicle subform will have it's
CustomerID filled in automatically, because it is forced to do so by the
Parent/Child Link.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Ken Sheridan]

As Al says, the simplest solution is to use a subform, but if you prefer to
open a separate form then the best solution is the set the DefaultValue
property of the Vehicle Installation Form's Customer_ID control in the
form's Open event procedure with code like this:

Const FORMNOTOPEN = 2450
Dim frm As Form

On Error Resume Next
'attempt to return a reference to customer form
Set frm = Forms("Customer Form")
Select Case Err.Number
Case 0
' no error so set default value
Me.Customer_ID.DefaultValue = """" & frm.Customer_ID & """"
Case FORMNOTOPEN
' do nothing
Case Else
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End Select

By setting the DefaultValue property rather than assigning a value to the
control the Vehicle Installation Form is not 'Dirtied'. This means that a
user can back out of entering a new record simply by closing the form before
inserting any other data. If a value is assigned to the control on the other
hand the user would have to undo the record with the Esc key to abort the new
record. Note that the DefaultValue property is always a string expression
regardless of the data type of the field in question, so should always be
wrapped in quotes characters as above. This may not always be necessary, but
can be essential in some surprising situations, dates being a case in point,
so its best to always include the quotes characters in the expression.

BTW I'd recommend tidying up the wizard generated code a little like so:

Private Sub AddNewVehicle_Click()

On Error GoTo Err_AddNewVehicle_Click

Const DOCNAME = "Vehicle Installation Form"

' ensure current customer record is saved
Me.Dirty = False
' open vehicle form at a new record in dialogue mode
DoCmd.OpenForm DOCNAME , _
DataMode:=acFormAdd, _
WindowMode:=acDialog

Exit_AddNewVehicle_Click:
Exit Sub

Err_AddNewVehicle_Click:
MsgBox Err.Description
Resume Exit_AddNewVehicle_Click

End Sub

By way of explanation:

1. As the form name, "Vehicle Installation Form" is a constant there is no
point declaring a variable and then assigning a value to it. It can be done
in one line by declaring a constant.

2. The code to save the current record which the wizard generates is these
days rather out of date. Setting the form's Dirty property to False saves
the record (an alternative would be RunCommand acCmdSaveRecord).

2. The vehicle form is not being filtered to existing records, so the
stLinkCriteria variable is unnecessary.

3. Opening the vehicle form in dialogue mode means it must be closed (or
more strictly speaking hidden at least) before returning to the customers
form.

4. The vehicles form can be opened at a new record by use of the acFormAdd
setting for the DataMode argument of the OpenForm method, which removes the
need to go to a new record when the form opens.

5. The DataMode and WindowMode arguments of the OpenForm method are
explicitly named and the line of code is split over three lines, using the
underscore continuation character, to make it more readable, rather than just
using commas with empty spaces between them to indicate unused arguments.
Note that when an argument is named like this the assignment operator is :=
(colon + equals sign) rather than just = (equals sign). Comment lines,
beginning with a ' character, are put in by way of explanation as to what the
code is doing.

Ken Sheridan
Stafford, England

 

[CDF]

Thanks Al and Ken,

I appreciate your help.

What I didn't mention was that I do have a subform in the customer
form which shows the vehicle information in datasheet view. My client
wants to see each vehicle for the customer in this way (ie one vehicle
under the other), but doesn't want to enter vehicle data in datasheet
view. As there are approx 40 fields on the vehicle form, it's not
possible to fit the form as a subform in form view (without
scrolling).

Therefore I need to go Kens route.

Ken, what I've done with the vehicle form that pops up is put 3
buttons, 'Add Another', 'Cancel' and 'Reset'. These 3 buttons should
help with/cover your explanation of 'making the Vehicle form 'not
dirty''.

When you finish entering the vehicle details and close the form, the
subform on the client form (which was not closed down) refreshes and
the new vehicle/s is/are listed in the subform.

Have I gone about this the right way?

I will play with your code and let you know of the outcome.

Again thank you both for your quick responses.

 

[Al Campagna]

CDF,
That's why it's aleays best to Copy & Paste your code into your post.
You're always assured of getting and "exact" copy.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[CDF]

    'attempt to return a reference to customer form
    Set frm = Forms("Customer Form")

Ken,

As I have 2 forms (Customer Form and Customer Form - Amend) pointing
to the form that has this in it's onopen event property, is there
anyway of referencing both forms with some sort of OR statement? I
tried Set frm = Forms("Customer Form" or "Customer Form - Amend") but
I don't know the correct syntax.

Or do I need to use some sort of IF statement? Eg If <coming from
Customer Form> THEN Set frm = Forms("Customer Form") Else If <coming
from Customer Form - Amend> THEN Set frm = Forms("Customer Form -
Amend").

If all else fails I will just have to create a duplicate form and put
one of each statement in the Onopen event I suppose???

 

[CDF]

Is it possible to somehow reference the customers name also, not just
the id? The customers name is nowhere in the vehicles table just the
ID, but is it possible to send the customers name from the other table
the same way we are sending the Customers ID?

Thanks

 

[John W. Vinson]

Is it possible to somehow reference the customers name also, not just
the id? The customers name is nowhere in the vehicles table just the
ID, but is it possible to send the customers name from the other table
the same way we are sending the Customers ID?

Thanks

First off - DON'T use table datasheets to interact with your data. They are of
VERY limited utility, and they're not designed for that purpose.

Secondly, DO use a Form based on your table. On this form you can bind the
CustomerID field to a Combo Box based on the customer table, displaying the
name but storing the ID.

To generate a report or an export file containing the customer name, use a
Query joining the vehicles table to the customer table; select the vehicle
info from that table, and the customer info from its table.

 

[CDF]

Thanks John,

I was a bit confused with your explanation, but from what I think I
understood I was using the Vehicle Table as the record source for my
form. So I instead created a query with all vehicle info and Customer
ID from vehicle table and Customer Name from customer table. I then
put a text box on the form with the Customer Name as the record
source. Now when the Customer ID is 'carried over' from the
instructions in this thread, the name also shows up. There was no need
for a combo box.

Thanks for your help.

 

[John W. Vinson]

I was a bit confused with your explanation, but from what I think I
understood I was using the Vehicle Table as the record source for my
form. So I instead created a query with all vehicle info and Customer
ID from vehicle table and Customer Name from customer table. I then
put a text box on the form with the Customer Name as the record
source. Now when the Customer ID is 'carried over' from the
instructions in this thread, the name also shows up. There was no need
for a combo box.

Well... basing your Form on a two (or multi) table query *can* work, but it
can also have unintended and unwanted consequences. Not all fields may be
updateable, and depending on the one to many relationships, you may see
multiple copies of each record, or missing records, unlesss you're really
knowledgable and careful about the form's recordsource!

What is your aversion to combo boxes? They make it much EASIER (not harder) to
insert and edit data!

 

[CDF]

What is your aversion to combo boxes? They make it much EASIER (not harder) to
insert and edit data!

Hi John,

I have no aversion to combo boxes. I just didn't understand what you
meant and couldn't see how a combo box would help my situation as the
id field and username field in question are not active (ie their
properties are set to Enabled: No, Locked: Yes). They're just there
so the person knows which customer (at a glance) they're changing
vehicle information for. Before I did this there was just a customer
ID number showing (not useful at all).

On a side note, are you saying that I shouldn't use a query for a
forms record source? The way I had it with the table as the record
source was better? If so, then how do I get the customer name to show
without the need for a combo box, ie how do I bring over the customer
Name along with the customer ID as per Kens suggestions/instructions
above?

 

[John W. Vinson]

On a side note, are you saying that I shouldn't use a query for a
forms record source? The way I had it with the table as the record
source was better? If so, then how do I get the customer name to show
without the need for a combo box, ie how do I bring over the customer
Name along with the customer ID as per Kens suggestions/instructions
above?

A Query is perfectly reasonable - but (usually) it would be a one-table query,
to present records and fields selectively so Access doesn't need to pull in
the entire table. This is particularly important for split databases.

In certain circumstances it can also be useful to include a lookup table...
just be sure you understand how this can lead to some fields not being
updateable, or (worse) how you can find yourself updating the wrong table!

 

[CDF]

...just be sure you understand how this can lead to some fields not being
updateable, or (worse) how you can find yourself updating the wrong table!

The only field I used from the '2nd' table (Customer Table) was the
Customer_Name. Every other field in the query (including the
Customer_ID) is from the Vehicle Table. The Customer_Name is not
accessible on the form so can't be updated.

Have I 'understood' correctly???

 

[John W. Vinson]

The only field I used from the '2nd' table (Customer Table) was the
Customer_Name. Every other field in the query (including the
Customer_ID) is from the Vehicle Table. The Customer_Name is not
accessible on the form so can't be updated.

Have I 'understood' correctly???

If it's working for you, fine. It sounds reasonable.

I'd still have used a combo box, myself; it lets you conceal the customer ID
but still allow you to select a different customer. If that's not relevant in
your case, you should be good to go.