passing variable from a subform to the main

[androomc]

yeah that seems about right.

ill post some screenshots to give you an idea of what it looks like... (its looking rather untidy so dont laugh too hard)

a few things to note about the above pics. on the sales form, the customer summary (surname etc.) is locked. i intend for the vehicle side to be the same when done (its essentially the same problem as this again).

also, on the customers subform, i intend for fields like surname to be searchable.

p.s. the ID fields for vehicle and customer are also missing (i was doing some last minute editing)

 

[Calvin]

Actually your design looks very functional and well thought out, you would be surprised by the interfaces that I see people design.

First peice of advice, although theoretically you could use the same form for searching and selecting a record as you do creating and editing, I recommend against it, I think you will find it much easier to design a custom lookup/search form for selecting a customer and use a separete form for editing and creating new customers.
The two forms will be simpler to design than having one and having to account for various conditions and it will get code intensive pretty quick.

How is your relationships structured between the tables? You might need to design an updateable query to use on your sales form instead of tieing it directly to the table if you want to display customers info on the sales form.

Have you considered using subforms on the sales form for customer and vehicle. This may be simpler than writing a bunch of complicated queries.

Is there just the one vehicle/item per sale or can there multiple items?

 

[Calvin]

My brother just called, and I need to go. I will be around tommorrow agian, if you have more questions.

 

[androomc]

i've taken your advice and will use the customers form simply for adding a new customer. i will add another form elsewhere in my switchboard for reviewing old customers. i've updated the picture to reflect this.

i'll post a photo of my relationships. i've hidden a few mundane tables but the main ones are visible.

yes there is only one item per sale. and i have thought about subforms but i think my method will be simpler to the end user, and usability is extremely important for me, since i can charge more for a DB thats easier to use also, this way helps to leave the sales form free of clutter.

you've been an immense help for me tonite. helping me get clear in my own head exactly what i want. thanks a lot.

 

[androomc]

HEY! i finally got something working. i made a public variable in a new module and on the close event of my customers form i made the customer_ID on my customer form become equal to it. then i made the customer_ID on my sales form equal my public varaible. this passes the customer_ID of the person i just created back into the sales page. the only problem now is that i have a few other fields like 'surname' and 'firstname' that i'd like to show too. i dont want to have to pass them as variables. is there some way i can make them update based on the customer_ID that is now on the same form?

 

[androomc]

hey me again. so i did some more tinkering and through the use of a subform i managed to get access to fill in the summary details that i told you about earlier.

this means that the whole thing does pretty much what i wanted at the beginning. there is only one problem. when i close the sales page i get the following error...

The Microsoft Jet database engine cannot find a record in the table 'CUSTOMERS' with key matching field(s) 'customer_ID'.

is this because i havent yet implemented the 'choose a vehicle' side of the sales form, or is it something more sinister?

as always your help appreciated.
andy.

 

[androomc]

...me

...again

well, i managed to fix that error that i was getting. turns out i wasnt saving the record before i was doing all the other stuff, so i cloned a save buttons VB code and put it in my customers form on the OK button. that sorted that. i even managed to implement the other side of the sales form. so the whole thing works now. yay.

one final slight problem. if customer details have been entered, and they need to be changed or 're-chosen' for some reason, i'd like the customers form to open at the last entered info. otherwise i'd like it to open at a blank page. is this possible? right now it's opening as a blank page. obviously i'd need some kind of condition but im unsure.

so far i have...

Private Sub Form_Load()
If Forms![SALES].[customer_ID] = "0" Then
DoCmd.GoToRecord , , acNewRec
Else
DoCmd.GoToRecord , , acGoTo, Me.customer_ID = pubcusID 'Buggy
End If

 

[Calvin]

The line that you called buggy doesn't work the way I think you think it works

DoCmd.GoToRecord , , acGoTo, Me.customer_ID = pubcusID 'Buggy

the last part Me.customer_ID=pubcusID isn't valid

to get the record you want use, set the form property rowsource = to a SELECT sql statement like we were using earlier in this thread.

another possibility is docmd.ApplyFilter look it up in the help file it's pretty strait forward. you will only need a Where Condition, don't worry about the Filtername.

 docmd.ApplyFilter , me![customer_ID]=pubcusID