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!formcust omers!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!formcust omers!name and that for the [ID] control, =Forms!formpati ents!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.OpenAr gs, ",")

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!formcust omers!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.