form not updating table--changing the control source of a field

[angus macgyver]

hi,
i have a simple database that maintains customer info in one table and their orders in another.
when adding a new order for a customer i would like the customer name and id at the top of the new order form 'formorder' when the form is opened (it is opened with the button 'baddorder' on the form 'formcustomers')
u basically choose the customer from a drop down list on the splash screen and can view the customer info (address, telephone) with one button, add a new customer with another button, see their orders with another button, and add a new order with the last button.
----the fields for the new order form 'formorder' come from the table 'tablecustomers'
----here is the code of the add order (baddorder) button

Private Sub baddorder_Click()
On Error GoTo Err_baddorder_Click
 
    DoCmd.OpenForm "formorder", acNormal
    DoCmd.GoToRecord , , acNewRec
 
Exit_baddorder_Click:
    Exit Sub
 
Err_baddorder_Click:
    MsgBox Err.Description
    Resume Exit_baddorder_Click
 
End Sub
 

if i set the name and customer id to come from the previous form by editing the control source (for example setting the customer name to: =Forms!formcustomers!name) the table is not updated. but if i don't change the control source the table is updated. but i need the name and id prefilled when the form is opened.
many thanks for any help!

[ADezii]

• You can pass both the Customer Name and Customer ID Values to FormOrder via the OpenArgs Argument of the OpenForm() Method, as in:
  

  DoCmd.OpenForm "FormOrder", acNormal, , , acFormEdit, acWindowNormal, Me![txtCustomerName] & "," & Me![txtCustomerID]

• In the Open() Event of FormOrder, I have assigned these values to the Caption of the Form as well as two Text Boxes on FormOrder:
  

  Private Sub Form_Open(Cancel As Integer)
  Dim varArgs As Variant
   
  varArgs = Split(Me.OpenArgs, ",")
  Me.Caption = "Customer Name: " & varArgs(0) & " | Customer ID: " & varArgs(1)
   
  Me![Text1] = varArgs(0)     'Name
  Me![Text2] = varArgs(1)     'ID
  End Sub

[NeoPa]

This is a little confusing. How is Fred a signature for Angus Macgyver?

Anyway, as the Control Source is the property that tells Access which field to bind it to (where to store the data contained in it when the record is saved), setting it to a calculation will, by definition, mean that it writes away to no fields. I suggest you set the DefaultValue for the controls instead. See the Help page for DefaultValue. The DefaultValue for the [Name] control would be =Forms!formcustomers!name and that for the [ID] control, =Forms!formpatients!id.

I hope this helps.

[NeoPa]

By the way, that last post (of mine) was posted to the duplicate thread of this question you posted earlier (hence the confusion). Please don't double-post questions (I appreciate the confusion for visitors though, unless they read the page that comes up after the post is submitted).

[angus macgyver]

thanks ADezii
i got an error message: invalide use of null
debug highlighted the line
varArgs = Split(Me.OpenArgs, ",")

i use the form 'formorder' to view orders and add new orders it's the same form. so i am unsure if i should be using onopen code like this if i am merely viewing an order rather than entering a new one (not sure if it makes a difference just looking for your expert opinion.) thanks

[angus macgyver]

hi NeoPa,
thanks for the showing me how the default value works. i tried it and the name and id came up as it should. but sadly the form doesn't update the table still. if i leave all fields as is and reenter name and id the record is saved to the table. thanks.
p.s. yes i have many aliases....and macgyver was the best show on television! NeoPa seems very Matrixy by the way :)

[angus macgyver]

the duplicate post was an err on my part but i didn't know it would go thru as i was asked to register after posting and then i registered and didn't know if the first post was linked to my new account since i didn't see it there. but no more dupes for me. thanks and maybe this'll help another newbie.

[NeoPa]

Angus, there's two parts to my earliest post (#3 in here). One was about the DefaultValue certainly, but you also need to set the ControlSource if you want the data to be saved anywhere in the record.

About the DefaultValue, this certainly does not cause a new record to be created (in and of itself). That is generally the desired approach. Only when the operator enters anything in one of the form's controls does the form get what is termed a dirty buffer - or alternatively - appreciates that there is data there to be saved away.

BTW. NeoPa is Matrixy, but only indirectly. My son's gaming name was Neo (N30 actually) when he joined a clan some few years back, so I chose the name NeoPa (Not too imaginative I know - but what can you do). My son, at that time, was very into The Matrix and related films. He's a bit older now of course.

[angus macgyver]

NeoPa,
yes the control source is set to 'name' (as it should be and the default value is set to =Forms!formcustomers!name as prescribed by you. the field shows the proper name but no new record is saved on closing the form. it only seem sto work when i enter the name and id manually (i wanted this to be automatic--and using default value does this; and also work--but this is not happening) since our last post i have tried any code i could find that i thought might help but i haven't made any progress. i thought my design was simple (it's only 2 tables and 4 forms):
form 1: splash screen (choose a customer)
form 2: view customer orders (a list of orders by date and order id only--click on an order date to see more detail)
also on form 2: add a new order (it is nice to see the name and id prefilled in this form--no if only i could get it to save as a new record in the table!)
the remaining 2 forms are for viewing customer bio and viewing/adding an order.
thank you
p.s. i am trying to imagine what a clan in the uk is akin to here in new england.

[angus macgyver]

well i was trying to get my form working and discovered another issue that may be related: the form that shows the list of orders for a customer (where the 'add order' button is located) is missing a couple of fields ('event date' and 'event id'. the fields appear in design view but not in form view.
the button to view the orders for a customer is located on the 'formcustomers' form. i thought it would be easier to explain by pasting the code for the 'view orders' button below:

Private Sub bvieworders_Click()
On Error GoTo Err_bvieworders_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
If IsNull(Me!name) Then
        MsgBox ("You need to enter the Customer's Name first")
        GoTo Exit_bvieworders_Click
    End If
 
    stDocName = "formorderslist"
 
    stLinkCriteria = "[name]=" & "'" & Me![name] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_bvieworders_Click:
    Exit Sub
 
Err_bvieworders_Click:
    MsgBox Err.Description
    Resume Exit_bvieworders_Click
 
End Sub
 

[NeoPa]

Angus Macgyver:
p.s. i am trying to imagine what a clan in the uk is akin to here in new england.

Actually, gaming clans are very rarely that parochial (with the possible exception of some French ones, who like to stick together and talk in French mainly). Four or five of our number were Bostonians, no less, and were easily recognised by their forthright opinions, both in the game (Wolfenstein - Enemy Territory) and in clan discussions on TeamSpeak.

Anyway, I'm just going to read your last post (#10) to see if there's any more info I can use to determine what the problem may be...

[NeoPa]

Angus Macgyver:
the fields appear in design view but not in form view.

If the controls do not appear on the form when it is running then there are almost certainly some of their properties which are set incorrectly for your requirements.

If you cannot fix this by looking at and changing any of the properties, then I would consider checking over your database for you (Please check the properties first. I expect that will help you find and fix the problem yourself).

If you do find you need to attach a copy of your database then please read through these following instructions first :

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. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If the database includes any code, ensure that all modules are set to Option Explicit (See Require Variable Declaration).
5. If you've done anything in steps 1 to 4 then make sure that the problem you're experiencing is still evident in the updated version.
6. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
7. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
8. Compress the database into a ZIP file.
9. 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.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[angus macgyver]

Hi NeoPa,
Thanks for your suggestions. I tried unsuccessfully to compact and repair the database. An error pops up stating a form is misspelled or refers to a form that doesn't exist. it was a form i had borrowed to use as a template then renamed. i thought i removed all references to that template form. it is a small db (~275kb). i have added the db as an attachment. since it is so small it's not a problem if i have to start over from scratch.

seeing a list of orders in the view orders form and
adding a new order to the table from the new order form is all this little db needs to work (i think!)
thanks for your help.
p.s. i feel like i am being hazed by an access clan but it's all good!

[NeoPa]

In Tools / StartUp you will see a reference to a non-existent form called formpatients. This is the cause of your error message and possibly even the failure to compile. That's confirmed. It compiles fine now, as well as Compacting and Repairing.

[NeoPa]

Unfortunately that's where I come completely unstuck. I have no idea which forms you're referring to. These are not even similar to the names of any of the forms in the database, nor do any of these forms even have a label inside whereby I may be able to cross-refernce your terms with the forms' names. I'm going to have to wait for you to come back to me with the name(s) of the form(s) you are working on.

[angus macgyver]

NeoPa,
thank you for pointing out the startup error! so nice not to see that error message anymore.
so the database has 2 tables and 4 forms:
tables:
1. tblbio (customer bio--id, address, telephone, etc.)
2. tblorder (order info--order id, date, item, note)
forms:
1. formbio (for entering/viewing the customer bio info)
2. formorder (for entering/viewing order info)--entering a new order with this form does not save a new record to tblorder
3. formcustomers (splashscreen with customer name and id drop down box--pulls name and id from tblbio)
4. formorderslist (shows orders for a customer--only shows the order date(s) and order id(s) in a continuous form)

many thanks for any help
Mac

[NeoPa]

OK. I'm off for a day or so, but before I go let's just confirm what I should be looking at.

I can see the called form is [formorder] from the code in the OP, but which is the form that calls it (Which form is that posted code associated with)? That will be my start-point. A point from which the rest of the thread will have context in.

[angus macgyver]

Hi NeoPa,

formorder is called from the form formorderslist
formorderslist has 2 buttons which call formorder

• one button is called 'view order'
  the other button is called 'add order'

viewing the order seems to work fine.
adding an order is the big problem with this tiny db.

thanks a bunch!
Have a fun break!
It will be good to get away from this madness, i'm sure!

[NeoPa]

I can't even view orders (there is none in the table).

[qryOrders] (Record Source of [formorder]) is not an updatable recordset, so neither adding nor amending will work through that form. See Reasons for a Query to be Non-Updatable.

The following code will not work as line #4 refers to the current form, and not the newly opened one (I suspect. On the basis that the new form is not yet available). As both forms work on the same recordset though ([qryOrders]), this line always fails.

Private Sub baddorder_Click()
 
    DoCmd.OpenForm "formorder", acNormal
    DoCmd.GoToRecord , , acNewRec
End Sub

A better solution may be to pass a parameter to the newly opened form in OpenArgs and handle this within the new form itself's Form_Open() event procedure. Setting its Data Entry property to yes would be preferable to simply going to the new record of course. You may get away with doing this in your baddorder_Click() subroutine though :

    Forms!formorder.DataEntry = True

Of course, none of this will work properly while the Record Source is not updatable.

I have managed to see the empty form (all controls invisible), and I am confused by it, but at this stage I would suggest getting rid of the various known problems before trying to isolate this one, as there are just too many other issues to confuse the search at this time.

[angus macgyver]

NeoPa,
welcome back to the matrix or from the matrix. wherever u were or are is too deep for this board i am sure!
anyway thanks for the helpful advice! i have changed the record source from the query to the table and data entry property of the new order form to yes. the new order is now saved to the table! now i am working on being able to view the new order as listed in the view orders form. the new order date is shown in the continuous form but clicking on view order shows a blank order form not the order i am highlighting to view. i will keep trying to figure it out. take care

[NeoPa]

Angus Macgyver:
i will keep trying to figure it out.

That's a good approach Fred.

As for further help, I'd be happy to look over a future version if you find yourself stuck again. The last one is now too far behind your latest work to be useful. On that point, if ever you want a previous version deleted from the page just ask & I will do that for you.

[angus macgyver]

NeoPa,
thanks for the encouragement.
so i have new order entry working to the effect that a new order as entered in the form formorder saves to the table tblorder and the new order date and orderid shows in the continuous form formorderslist. as a test i have added two orders to this database for the customer named black, cat and these can be seen listed by order date and order id in the form formorderslist. now the problems i have been trying to solve are: 1. viewing an order using the view order button in the form formorderslist 2. listing the orders in the form formorderslist by order date. any ideas would be greatly appreciated as always! i think these 2 issues solved would result in an easy to use database don't you?
Yes, please delete that old database. i have attached the current one to this reply. thank you.
stay warm wherever u are. a cold front has come over my area and playing around with access doesn't warm like a good cup of joe (or something to that effect :)
signed:
fred/mac/angus/morpheusson

[NeoPa]

It seems that the original problem has indeed been resolved (from my testing - I saw it working fine as far as I tested it). Is that true?

As for new questions, they need to be progressed in their own thread. If you confirm this one is sorted then I can sort your post out and create a new thread with it which I will then reply to.

PS. Morpheusson did, indeed, give me a giggle :-D

[angus macgyver]

NeoPa,
Right the original problem seems solved. Not sure how to word the remaining problems. Something in the realm of choosing and viewing records displayed in a continuous form and sorting records displayed ina continuous form sound good to you? p.s. glad morpheusson gave u a giggle. laughs in the land of vba are like golden tickets. take care & thanks again

[NeoPa]

That clarifies that this thread is now done with. A thread with the new questions can now be found at Form Problems. I'll be looking into it as soon as I get some spare time.