passing variable from a subform to the main

[androomc]

i have searched through the forums but all of the examples seem to go the other way and im not quite good enough at access to adapt it to what i want.

basically i have a sales form. to choose a customer the user clicks 'choose' which pops-up a customer form that lets them either choose a customer already in the DB or to create a new one. when they click 'done' i want the customer form to close and the sales form to update with a summary of the customer record that was the last opened in the customer form. (if that makes any sense).

customers have a customer_ID (autonumber). each sale must have a customer_ID in it. how can i send the customer_ID from the customers form back to the sales form?

any ideas?

 

[Calvin]

In the close event of the Customer Form insert somthing like:

forms![SalesFormName].recordsource = "SELECT * FROM [SalesTable] WHERE [Customer_ID]=" & me![Customer_ID]
DoCmd.Close acForm, Me.Name, acSaveYes

You will have to change "SalesFormName" to the name of the form and "SalesTable" to the name of the table that you want to get the data from.

 

[androomc]

ok i applied that code to the 'on click' property of my 'done' button on my customer form. i get the following error...

Syntax Error (missing operator) in query expression '[Customer_ID=]'

how do i fix this? my tables and forms are named as follows...

tables
====
sales
customers


forms
====
sales
customers

both have a customer_ID field.

 

[Calvin]

What is the name of the field in the control source property of the [Customer_ID] field on the form?
what ever it is you need to change the [Customer_ID] in the "WHERE [Customer_ID]=" part of the statement to the name of the unique id field in the table.

 

[androomc]

i just had a look at my customer_ID textbox and noticed it was unbound. i must have changed it by accident. once i bound it to customer_ID on my customers table no error was thrown up, but the sales form doesnt update to show the customer_ID for the customer i just chose. it stays empty. i need this to update so that the combo boxes underneath it can update with a summary of the customer data.

 

[Calvin]

after the "forms![SalesFormName].recordsource = " line add a "forms![Sales].requery" and see if that reloads the data, if this doesn't work there are a couple of others.

 

[androomc]

unfortunately that didnt seem to make any difference at all. on the bright side no errors either. the customer_ID on the sales form remained at zero. i can feel its getting close though. with your genius im sure we can get it working.

 

[Calvin]

is the Sales form doing anything, is anything different data wise, does it have any data loaded after the previous actions?

 

[androomc]

its definately not doing anything. the only fields that are filled are a default Date() field a default employee name field. the customer_ID field which links a sale to a customer remains at '0'.

 

[Calvin]

I'm wondering if the me![Customer_ID] on the Customer form is still empty when the sql statment is built and passed to the Sales form.
Try this, Replace the me![Customer_ID] at the end with me.Customer_ID and try it again.

 

[androomc]

tried it. didnt work. no errors again. i really did think that would work too. ill keep tinkering.

 

[Calvin]

ok were not done yet, insert the following at the beginning of the close event an let me know if it is returning the correct customer id number or is it blank.

msgbox "first Customer ID: " & me![Customer_ID]
msgbox "second Customer ID: " & me.Customer_ID

 

[androomc]

both message boxes returned the value '15' which was the value of the customer_ID when i closed the form. so that bit seemes to be working correctly.

 

[Calvin]

ok good, then we know we are building the sql syntex correctly, as long as record 15 exists in the Sales table then there shouldn't be any problems. so why then...

ok delete the msgbox's and comment out the other two previous lines that I suggested and use the following instead

DoCmd.OpenForm "Sales", acNormal, , "[Customer_ID]=" & Me![Customer_ID]

 

[androomc]

ok tried that and the status of the sales form changed to 'filtered' but the customer_ID still remained empty.

one quick thing, you said as long as record 15 exists in sales table, but the sales table is empty right now because no sales have yet been made. i intend on letting the user choose a customer and a car and then to confirm a sale, and then the sale is made.

maybe im confusing things, but we do seem to be on right track anyway.

 

[Calvin]

BAMM!!!

THERE IS YOUR PROBLEM!!! you cannot open or set the Sales table equel to records that don't exist. Actually you can but as you've seen, since nothing matches the WHERE criteria no data will populate the form. How do you intend on opening up a Sales recored for a customer if the customer has no sales data?

delete the last line that reopens the form filtered and use the original line that I suggested. believe me it will work as soon as you have sales data matching the unique id of the customer.

 

[androomc]

ok that probably will work. but it doesnt let me do what i want to do. the fact is, the only reason i'd be in the sales form is to make a new sale. when you're making a new sale, you need a customer and so i figured the best way to do this would be a button that says 'choose customer' that opens a customer form, and when closed, fills some summary details on the sales page, like surname, firstname (not even necessarily customer_ID) then the user could go on to choose a car from stock and confirm and close the sale. have you any ideas on other ways to go about this? its important that the customer is really the right person and so just leaving it at typing in the surname and hoping access finds the right person isnt really acceptable (i know i can bind a combo box to a query).

 

[androomc]

actually, the way you tried to help me solve the problem highlights why i made this thread. all of the similar answers on this forum seem to go the other way i.e. if you already have sales data then you can get to it by customer. but i think in my case its easier to look at my problem from 'sales is main form and customer is subform' point of view.

 

[Calvin]

ok good, you know what you want at least, we can still make this work. give me a few minutes to write my idea out.

 

[Calvin]

Alright, now we're getting somewhere.

Since the Sales form is still the main form, tied to the Sales table, but after you create the new customer you cannot reload or set the the Sales form to the customer id, instead you need to:

1. Refresh the dropdown list of customers on the sales form.
2. Create a new sales record on the sales form
3. Set the Customer_ID field/combo box on the sales form equel to the newly created customer_id off of the Customer form.
4. Close the New Customer form (I call it New Customer form because there should be no point in using it if the customer already exists.)

does this make sense?