I have a form in a project. When the form opens, all I display is a dropdown list of Customers. When the User selects a customer, they then have the option of creating a new invoice for this customer or viewing previous invoices, depending on which button they click. That's all the background... the problem is when they click to create a new invoice.
I am new to .adp (but an old hand at SQLserver), so I'm trying to figure out how I am supposed to populate the data in the form. What I want to do is:
1) display all the data entry fields (this already works)
2) be on a new record in my table
2) autopopulate SOME of the fields based on the customer that was selected (address, phone, etc) from a stored procedure that already exists
3) leave the rest of the fields empty for user entry (items to order)
4) have the record save in some automated way
There are lots of different ways of managing data in forms and don't know what is the best method. I've considered:
1) Me.Recordset - as far as I can tell, this is a temporary recordset that you can base the form on. It can be autopopulated from the storedprocedure but I don't know about then allowing user data entry? If I used this then at some point I'd have to create a new record in my SQLServer table based on the data in the recordset and it's not clear when is the best time to do this (since the user could do several different actions that might interrupt the data entry process)
2) DoCmd.GoToRecord acDataForm, "frmOrders", acNewRec - I'm not sure how this works. To use it I think I have to set the Form Recordsource to a table/view/sp in the Properties and when I do this I get other odd errors like fields on the form not being updatable, which means that my code that autopopulates certain fields does not work.
But to save the record I could run "DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord", correct? Does this really do the appropriate Insert/Update to the SQLServer table? It seems too magical for this old-time hand-coder ;)
I guess I'd like to use the DoCmd approach except that I have this problem with my fields not being updatable. Here is my code, with a comment showing what error I get when I run it:
Private Sub cmdNewInvoice_Click()
DoCmd.GoToRecord acDataForm, "frmOrders", acNewRec
subEnabled 'this makes the fields visible
Me!fld_iInvoiceNumber = iGetNextInvoiceNumber() 'ERROR: you can't assign a value to this object
'nothing after here is tested since I can't get past the previous error
Me!fld_dtInvoiceDate = dtGetDCDate()
Call EnterSoldShipped 'this enters the address info etc from a storedprocedure
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
' some alternate stuff I was playing with - again, I don't know which approach to use.
' and what's the diff between RecordSource and RecordSet and why does Access have
' so many different ways to do the same thing??
'Me.RecordSource = "Select Top 1 * from tblInvoices where fld_txtCustomerID='" & Me.cbxCustomerID & "'"
'DoCmd.GoToRecord , , acLast
If there are other procedures anyone wants to see that's fine. Also specific properties of my Form. The form Recordsource is originally blank, because I don't want to link the form to any particular invoice until the user either selects to view an existing invoice or create a new one.
Thanks for any help. I apologize if the question is confusing, I tried to make it as clear as possible. Lotsa points because I need both conceptual and coding assistance.