Solved

ADP data-entry form

Posted on 2004-04-30
27
1,194 Views
Last Modified: 2013-12-05
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 & "'"
    'Me.Requery
    'DoCmd.GoToRecord , , acLast

End Sub


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.
0
Comment
Question by:rsoble
  • 15
  • 12
27 Comments
 
LVL 1

Author Comment

by:rsoble
Comment Utility
I did some more testing, commented out the lines that caused that "can't assign value to object" error and then it did seem to load a new record.  But I'm not sure because all the entry fields contain #Name? and if you try to type in them the error "control can't be edited, bound to unknown field fld_dtOrderedDate"  (or whatever field you click on).  So I clearly have some major conceptual thing wrong, not just code tweaks.  Thanks.
0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
I did some more testing, commented out the lines that caused that "can't assign value to object" error and then it did seem to load a new record.  But I'm not sure because all the entry fields contain #Name? and if you try to type in them the error "control can't be edited, bound to unknown field fld_dtOrderedDate"  (or whatever field you click on).  So I clearly have some major conceptual thing wrong, not just code tweaks.  Thanks.
0
 
LVL 5

Expert Comment

by:Krys_Wilson
Comment Utility
Hello!

I use an ADP for work and it is a great tool as a front end.  Plus you get all the great features of using SQL server!

Just as a side note, a great book for using ADP projects is Access 2000 Developers Handbook Volume 2: Enterprise edition.  It gives a lot of information on ADP's and how they connect to SQL server.

Now on to the problem with a little background on Access and ADP's

Access ADP projects need a primary key before you can insert any new records into the underlying table.

To save the record, you can test to see if it needs saving first.  Access has a property called Dirty.  If a form is dirty, then it needs to be saved.

So:

If Me.Dirty Then
     Me.Dirty = False ' Saves the record
End If

I really like Access ADP's ability to use a Stored Procedure as its recordsource.  At the bottom of the form properties there is a property called "Input Parameters" which is used specifically for stored procedures.  I have a form that uses a stored procedure like below and then gets the CustID from a custom search form I have.  If GetCustID is blank, the form is blank.
*******************************
Create Procedure dbo.GetRecordsource
      (
            @CustID int
      )
As
      SELECT *
      FROM Data_Cust
      WHERE CustID =  @DECustID
*******************************

Then my Input Parameter looks like:

@DECustID=[forms]![data_entry]![getcustid]

This works great and because its a stored procedure, its really fast.


First I need to know how your forms are setup.  IE when the user selects add new invoice, is it supposed to add a new record in the same table as your customer?  Or is it a new form (subform or linked form) that is linked to an ID field in your customer table?

I am assuming that iGetNextInvoiceNumber() is a custom module that generates a new invoice number for your table.

Once we get your form setup so that it will accept data entry, then we can get it to accept the existing customers information.

So, in order to do that.  Lets take it step by step.

Goto the tables tab in your project, and open tblInvoices.  Goto the last record and try to put in a new record.  If you can, we are on the right track.  If we can't there needs to be a primary key or an identity field in tblInvoices.

Once you can put new records into the table itself, create a Stored Procdure like mine above.  Manually input the @CustID for now and make sure you can pull back records.

Then, we can base the form off of the stored procedure.  I have probably given you too much to chew on so if you have any questions, Ill keep checking back.  Sorry to inundate you with information!

Chris
0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
Inundate away!  :)    I'll get started with this info ASAP.  For information's sake, the tables are designed like this:

tblCustomers stores generic customer info (name, address, phone)

tblInvoices stores high-level invoice info l(Invoice #, OrderDate, PaymentDate). Linked to the customer by the customerID.

tblInvoiceLineItems stores specific items on the invoice (item, quantity, price).  Linked to the Invoice by the Invoice #.  

The form (frmOrders) will contain fields from both tblCustomers and tblInvoices.  There is a subform (subfrmOrders) that will have all the InvoiceLineItems, but I was avoiding that tangle until I got the main form stuff straight.   Eventually the form will have to write to both tblInvoices and tblInvoiceLineItems.  

FWIW I tend to do all my views and stored procedures from the SQLServer enterprise manager, not thru Access.  But I'll try your way just to get a hang of the connection.
0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
Also, great heads-up on the PK thing.  I inherited the DB from a previous sloppy programmer and have been having to add PKs to tables.  Indeed my tblInvoices didn't have one but I got so befuddled by all the different form stuff that I forgot to check that.  It has one now, maybe that will make stuff easier! ;)

The previous version used a .mdb with linked tables to SQLServer (kinda wasting the power there) so I guess the PK issue resolved itself differently then.

0
 
LVL 5

Expert Comment

by:Krys_Wilson
Comment Utility
Great!

Stored Procedures are probably easier to create in Enterprise manager.  The great thing about an ADP is that is directly connected to the SQL server by OLE DB which means that all you need to do is press F5 in your ADP after you have created it in Enterprise manager and it shows in your ADP.

Let me know if you can add the records through the table, and then create the new Stored Procedure.

Chris
0
 
LVL 5

Expert Comment

by:Krys_Wilson
Comment Utility
Also, I'm heading to lunch in a couple of minutes so it might take me a little while to get back to your next questions.

Chris
0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
Thats ok.  Access just crashed on me and my backup is from several hours ago so I've lost a lot of work on other stuff.  Ugh.  It's gonna be a while.  No hurry.  
0
 
LVL 5

Expert Comment

by:Krys_Wilson
Comment Utility
Wow, I've never actually had an ADP crash on me.  MDB's all the time though, is everything ok?

Let me know how the first couple parts go!

Chris

0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
All is fine.  Even though I couldn't open forms etc in the original adp (and couldnt open VBA) I could still copy the forms out to my backup version.  So I didn't lose most of the work after all.  

Still, I know it was playing around with the order form that made it crash so I think I'm gonna delete it and start from scratch (I was trying to use the version from the .mdb and fiddle with things).  This might or might not happen over the weekend, so don't bother checking back until Monday.  I appreciate your patience :)  (I'm East Coast BTW, so it's nearing the end of my day...)
0
 
LVL 5

Expert Comment

by:Krys_Wilson
Comment Utility
Ok, then this is after you have gone home then.  I'm in Seattle so I have at least 3 hours before I head home.

Ill check back over the weekend and on monday see what happened.

Chris
0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
Okay, starting again Monday morning with a fresh and rested brain...
I started with a brand new form with no code in it at all.  I rebuilt the basic parts of the original form and created the following procedure which does work  (I think the PK helped a lot ;)  

After the user selects a Customer from the combobox cbxCustomerID they can click the "New Invoice" button which creates a new record in tblInvoices with these preset values set, and it does save it properly.
----------------------------------------
Private Sub cmdNewInvoice_Click()

    DoCmd.GoToRecord acDataForm, "frmOrders", acNewRec
    'populate the known info for the invoice
    Me.txtCustomerID = Me.cbxCustomerID.Column(0)
    Me.txtInvoiceNumber = GetNextInvoiceNumber()
    Me.txtInvoiceDate = dtGetDCDate()
    ' populate the known info from the customer table (address, payment terms, etc)
    EnterCustomerInfo
    ' show all the fields
    EnableForm
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

End Sub
----------------------------------------

All my procedures (GetNextInvoiceNumber, EnterCustomerInfo, dtGetDCDate) run from Stored Procedures.

So is this an okay way to do this?  Is it efficent or inefficient?  Is there a better way?
I still have a long way to go - adding in the subform for the InvoiceLineItems, etc.  But I want to be sure I'm using the right approach from the start.  How does using this DoCmd approach affect other users in a multi-user environment and if there is another way, what is it?  

I must admit I didn't quite follow your example using the InputParameters etc because
(1) I didn't want to do it when the form loaded but rather after the user chose a customer and (2) I am under the impression that if I base my form on a stored procedure rather than a table then the fields will not be updatable since they're based on a calculation rather than a data field.  

Let me know what you think.  Thank you!!
0
 
LVL 5

Expert Comment

by:Krys_Wilson
Comment Utility
The approach seems fine to me.  There are more than likely better ways to do it but that is always the case.  As long as there are no significant slowdowns this should be fine (You are using SP's to do most of the work so this should be very quick)

This approach could possibly affect multiple users if they can work on the same customer at the same time.  Depending on how your work flow goes, this might not be an issue.  Such as if the user will only be working on the customer if they have the customers file.  Then it will be very rare that two users will be in the same record.

Because ADP's have to use optomistic locking, if two users do enter information at the same time, then it will pick the first person to save the records information and put that in the table.  Then when the second user tries to save it will give them an message saying someone else has changed the record since they began working in it.  Would you like to save your changes (Overwrites the first users changes), or cancel your changes(keeps the first users changes).

There are a number of ways to get around this such as "checking out" a record whenever a user begins updating a record.  (In the before update event).  I place the users SPID and the CustID into a table with a primary key of the CustID.  Then if another user tries to update the record it fires the before update and tries to place the CustID into the table a second time generating an error and then cancels the update.  So they can't update the record until the first user is done.  The record gets checked back in(CustID deleted from reservation table) during the After Update event of the form and other users can then update the record.  This approach has served me pretty well.  If you want more info on this method, I will show you the place where I got it from.

Don't worry about following my examples to the letter.  I'm only offering suggestions.  They can be modified in any way you can see to make them better.  Your way is most likely better since you have to pick a customer before you goto the new record.  The only drawback is that there will be two records in the form(Found customer record and the new record) and they might update the wrong record by mistake.  Users can do some strange things.

I'm not quite sure what you mean by based on a calculation.  Stored Procedures that have input parameters are just like queries that have a parameter in them.  Select queries used in a stored procedure are definately updateable and the input parameter just filters the select query.  It never calculates anything.

HTH

Chris
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:rsoble
Comment Utility
Here's an example of what I mean about the fields not being editable... it's happening now so hopefully you have an idea how to fix/work around it.  As I said yesterday I got the form to work when creating a new invoice record.  Now, I'm trying to let the user edit an existing invoice:  I display a list of existing records in a separate modal dialog, having the user select one, and then it populates the form with the data of the existing invoice.  This works almost great and it looks like this:

--------------------------------------------------------------------
Public Sub EditInvoice(InvNum As Integer, CustID As String)

    Set Me.Recordset = myGetRecordset("spGetInvoice", InvNum, "InvNum")
    EnterCustomerInfo (CustID)
    EnableForm
 
End Sub
---------------------------------------------------------------------

The myGetRecordset is a public function I created that runs the specified stored procedure with the parameter and returns a recordset, in this case just one record.  I know this works because I use it other places, and with a Watch I can see the value in Me.Recordset is correct.   So the data displays fine but when I try to edit a record, the following error appears in the status bar:

"Field <whichever fieldname> is based on an expression and can't be edited."

The stored procedure itself just does (for now) "select * from tblInvoices where fld_iInvoiceNumber = @InvNum", essentially returning one record.

Since the form is linked to tblInvoices, I considered using a filter on the form to limit to that one invoice, but then figured that won't work that since I never load ALL the records into the form (there are a lot and what's the point when you just want one).  Also I get errors when trying to use the filter -  I don't remember the exact text but it was about trying to create a new record with a duplicate PK (on the InvoiceNumber) - as if I were trying to create a new record instead of just load an existing one.  

So as you can see there are still some gaps in my understanding of how the forms handle data.  I've been experimenting looking back and forth between form view and datasheet view and that's helped a little.  If you can explain how I get my data that loads from a stored procedure be editable, that would be a huge help.

BTW thanks for all the information on the Optimistic Locking, etc.
0
 
LVL 5

Expert Comment

by:Krys_Wilson
Comment Utility
Ok, lets try something and see if this works.  One of the problems maybe that you are using RecordSet instead of RecordSource.  I'm not 100% positive on this but a form's recordsource is where it gets its information from.  Its recordset is what is returned from that recordsource.

I'm not sure whats on the form before the user selects the customer from the modal dialog but it really shouldn't matter.  As long as the form is open this should work.

What I want it to do is this:

* User selects customer - This should give you the InvNum.  
* Change recordsource to StoredProcedure
* Change Input Parameter to InvNum from Modal Dialog
* Run the rest of your procedure (EnterCustomerInfo and EnableForm)

So something like this, I'm not sure how the InvNum is gotten from the Modal Dialog though

***************
Public Sub EditInvoice(InvNum As Integer, CustID As String)

   Dim ModalInvNum as Int
   Set ModalInvNum = Me.ModalInvNum ' Taken from the Modal Dialog box.

    Set Me.RecordSource = MyStoredProcedure ' Not sure what the SPs name is.
    Set Me.InputParameters = "@InvNum = " & ModalInvNum
    EnterCustomerInfo (CustID)
    EnableForm
 
End Sub
***************

If you want to see if this works outside of the code, go into your form and change the recordsource to your stored procedure.  It will show in the drop down list.  Then in the input parameter property at the bottom of form properties place:

@InvNum = 12345

Just make sure the number is from an actual client and that should bring up an editable form with just Customer 12345's information.

Let me know how that works.

Chris
0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
Okay :)  Here's what I got...

--------------------------------------------------------------------
Public Sub EditInvoice(InvNum As Integer, CustID As String)
    Me.RecordSource = "spGetInvoice"
    Me.InputParameters = "@InvNum = " & InvNum
    Me.Requery
    EnterInvoiceInfo InvNum, dtGetDCDate()
    EnterCustomerInfo (CustID)
    EnableForm
End Sub
---------------------------------------------------------------------

The InvNum gets passed in because the modal dialog that lists the invoices (and contains the invoice number) calls this procedure before it closes (hence this procedure being public).    

I now understand better what the RecordSource property is for - I was confused before, not understanding the difference between it and RecordSet.   In order to get this to work I had to also do the opposite when the New Invoice button is clicked:  

---------------------------------------------------------------------
Private Sub cmdNewInvoice_Click()
    Me.RecordSource = "tblInvoices"
    Me.InputParameters = ""
    Me.Requery
    DoCmd.GoToRecord acDataForm, "frmOrders", acNewRec
    'etc...
End Sub
---------------------------------------------------------------------

So now when the user selects an invoice to edit, it will load the record just fine, but there are two followup problems:

1) after loading the record the user can't load any more records, either by Edit Invoice or New Invoice.  When either of these is chosen, the run-time error 2107 is thrown, which says "the value you entered doesn't meet the validation rule defined for the field or control".  This error is being thrown on the line that resets the RecordSource.  (Either Me.RecordSource = "tblInvoices" or Me.RecordSource = "spGetInvoice" depending on which the user chose.)  
[BTW I did try using the "Set" syntax you had, and that gave a different, more obvious error.  I also checked the docs and the examples there do no use Set when setting the recordsource.]

It doesn't even work to close the form and reopen (after hitting the runtime error) because then InputParameters is set to a value when it shouldn't be and I have to go into Form Design/Properties to clear it out.   I thought resetting the RecordSource and InputParameters on Form Close would help but it didn't.

2) The record saving with a Stored Procedure recordsource does not seem to work the same as with a table RecordSource.   For example, I have an existing record open to edit and I add a test note in the Notes field.  If this was a new Invoice (RecordSource is tblInvoices), I could go look at the record in SQLServer and the new note would be there right away. With the stored procedure it isn't.  So if you have some good idea about how to make sure users save records, that would be helpful too :)

Thanks so much.
Rebecca
0
 
LVL 5

Accepted Solution

by:
Krys_Wilson earned 500 total points
Comment Utility
Rebecca,

Sorry I didn't get back to you yesterday, a lot of other things popped up here at work.  I used Set because you had that in your original one so I assumed it was working for you.  I always use Me.Property when I reference something.

The best way to make sure users save a record is to check for it every time it should be saved.  IE when they push the button for a New Invoice, the previous record should be saved.  This bit of code will check to see if the form is saved and then save it if it isn't.

If Me.Dirty Then
    Me.Dirty = False
End If

What is the recordsource of your form based on normally?  I seem to have a setup very similar to yours. (form with recordsource of stored procedure and users select a customer from a search form)  The only difference I have is that instead of getting my CustID (your InvNum) directly from the search I place the number in a txtbox on my main form and my Input Parameter always stays to @DECustID=[forms]![data_entry]![getcustid]

Here is the way my data_entry form is setup.
****************************************************
Stored Procedure:
--------------------------------------------
Alter Procedure dbo.GetRecordsource
      (
            @DECustID int
      )
As
      SELECT *
      FROM Data_Cust
      WHERE CustID =  @DECustID
--------------------------------------------
Form:
Recordsource: dbo.GetRecordsource
Allow Edits: Yes
Allow Deletions: No
Allow Additions: Yes
Data Entry: No
Recordset Type: Updatable Snapshot
Input Parameters: @DECustID=[forms]![data_entry]![getcustid]

Table:
CustID: Primary Key AND Identity 1
****************************************************

With this setup, when I open my form, there is a blank record waiting to be data entered.  When I select something from my search form and press the button to move it over to my main form it looks like this.

DoCmd.OpenForm "Data_Entry"
Forms!Data_entry!GetCustID = Me.CustID ' Me.CustID is from the search form
Forms!Data_entry.Refresh ' Requerys the main form to get the exisiting customer.

So what does this all mean for you.

* Check to make sure your form properties allow edits and additions.  Not sure if your users can delete, mine can't.
Since this way isn't working perfectly and your "frmOrders" form is open, make a txtBox called txtInvNum.  Then instead of using @InvNum = InvNum put the InvNum from your Modal Dialog box into txtInvNum (Me.txtInvNum = InvNum) and in the properties of the form (Not from code) set your Input Parameter to equal @InvNum = Forms!frmOrders!txtInvNum

* Change the recordsource property in design view of frmOrders to spGetInvoice

* Change your Public function to this:
--------------------------------------------------------------------
Public Sub EditInvoice(InvNum As Integer, CustID As String)
    Me.txtInvNum = InvNum
    Me.Requery
    EnterInvoiceInfo InvNum, dtGetDCDate()
    EnterCustomerInfo (CustID)
    EnableForm
End Sub
---------------------------------------------------------------------

* Change your NewInvoice procedure to:
---------------------------------------------------------------------
Private Sub cmdNewInvoice_Click()
    DoCmd.GoToRecord acDataForm, "frmOrders", acNewRec
    'etc...
End Sub
---------------------------------------------------------------------

Let me know if that works.

Sorry this is taking so long to get you up and running!

Chris
0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
Chris,  thanks for the info.  I got sidetracked on some other issues but I will get back to this soon enough.  I won't forget I promise :) -R.
0
 
LVL 5

Expert Comment

by:Krys_Wilson
Comment Utility
No problem and go home, you are there late tonight!

Chris
0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
Haha.  I was working late last night, but since I do it all from home these days, it's a short commute :)
Okay, here's what I've got now.

In the Form Properties:
RecordSource is spGetInvoice ("select * from tblInvoices where fld_iInvoiceNumber = @InvNum"), and the InputParameter is @InvNum = Forms!frmOrders!txtInvoiceNumber (that textbox already existed).  
Other Form Properties:  Allow Edits, Additions, Deletes, DataEntry all set to Yes.  (Yes I plan to implement deletes.)

I've updated both NewInvoice_Click and EditInvoices as you suggested.  It looks like the NewInvoice stuff is working fine.
EditInvoices is not working in the following specific way:

A run-time error is thrown on the Me.Requery line.  This error says "Violation of primary key constraint PK_tblInvoices.  Cannot insert duplicate key in object tblInvoices".  Okay this makes sense, I wouldn't want to create a duplicate key.  So the problem appears to be that the form is thinking I want to create a new record (using this same InvoiceNum hence the PK violation) rather than just open an existing one.  Why would this be??

Here is the code again:
--------------------------------------------------------------------
Public Sub EditInvoice(InvNum As Integer, CustID As String)
    Me.txtInvoiceNumber = InvNum
    Me.Requery                                  'this is the line that throws the error
    EnterInvoiceInfo dtGetDCDate()      'now that I set the InvNum above I took it out of the proc, changed params
    EnterCustomerInfo (CustID)
    EnableForm
    Me.cbxCustomerID = ""                 ' this is a new addition, it just clears the customer select box
End Sub
--------------------------------------------------------------------

As I said, NewInvoice_Click seems to be working fine - nice that it still works even when the RecordSource is the SP rather than the table itself.  Thanks!
0
 
LVL 5

Expert Comment

by:Krys_Wilson
Comment Utility
The problem is most likely that txtInvoiceNumber is a bound field.  IE its an actual field (The Primary Key field) in the underlying table.  If you try to change that to another customer, then it will throw the error you are saying.

The reason NewInvoice_Click works is you are assigning something new to that field.

The txtbox I have is not bound to anything (I have a CustID txtbox that is bound to my CustID field and a GetCustID txtbox that is unbound and is used as my input parameter).  Since my Primary Key CustID is also an identity field, I don't ever need to assign it anything, the sql server always increments the number itself.  Since you assign something to your primary key, yours can't be exactly like mine.

So, leave your NewInvoice_Click alone.  That should work just fine.

Add a new txtbox to your form.  You can make the visible property to no after you have tested it that way its seemless to the users.  Name it what you want, something like txtGetInvoice.

Then change the line in your code:
Me.txtInvoiceNumber = InvNum

to

Me.txtGetInvoice = InvNum.

Then change your input parameter to the new txt box and that should work for you.

Chris
0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
Okay, more progress but still a problem.

Just for the sake of experimentation I went ahead and made my PK field (InvoiceNumber) an Identity.  I don't want to do this long term but for now it's fine, and I could live with it if I had to.  But it helped to see the underlying problem better...

Every time I select an invoice to edit, it not only loads the data for that invoice into the form, but it ALSO creates a BRAND NEW record.  This is why I was getting that PK violation.  I removed that problem by using the Unbound textbox and then I got an error about inserting NULLS into a PK field.  That's when I decided to make the InvoiceNumber in the table an Identity, and that's when I saw that new records were being created.  So, here's the complete code for this edit process:

From the frmOrders, user clicks on the find invoice button:
------------------------------------------
Private Sub cmdFindInvoice_Click()
    DoCmd.OpenForm "frmInvoices"
End Sub
------------------------------------------

frmInvoices has the RecordSelectors Property set to Yes, so the user clicks an invoice from the list and then clicks Select:
------------------------------------------
Private Sub cmdSelect_Click()
    Call Forms("frmOrders").EditInvoice(Me.fld_iInvoiceNumber, Me.fld_txtCustomerID, Me.fld_dtInvoiceDate)
    DoCmd.Close acForm, "frmInvoices"
End Sub
------------------------------------------
This calls EditInvoice back in the original form, and then closes.  Note that I added a third param because my dates were wrong.  That's not related to the problem.  And I've confirmed with the debugger that the correct values are being passed.

So now we're back to our good old EditInvoice, which now looks like this:
------------------------------------------
Public Sub EditInvoice(InvNum As Integer, CustID As String, InvDate As Date)
    Me.txtHiddenInvNum = InvNum
    Me.Requery
    ' note NOT setting the visible txtInvoiceNumber - letting the Identity do its magic.
    ' when i was setting it (before I had the Identity) it would tell me that the field couldn't be NULL.
    ' And now I see that was because it was creating this new blank record.
    Me.txtCustomerID = Me.cbxCustomerID.Column(0)
    Me.txtInvoiceDate = InvDate
    EnterCustomerInfo CustID
    EnableForm
    Me.cbxCustomerID = ""
End Sub
------------------------------------------
So this works fine (no runtime errors) except that it's making these extra empty records (with the PK InvoiceNumber incrementing as expected).  Any ideas?

Sorry this is taking so long.  But it will be very useful in the long run, for more than just this form.  Thanks for your time.
0
 
LVL 5

Expert Comment

by:Krys_Wilson
Comment Utility
Shoot, I didn't see this earlier but you have the property Data_Entry set to yes.  You need to set this to no.

I believe that would be the reason why its creating those empty records.

Let me know if that fixes it.

And don't worry about the time, I am sorta bored here at work and I love figuring stuff like this out!

Chris
0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
Well that seemed to work!  I had to make one other small fix (I was manually setting a bound field by accident) and now it looks all correct!  Thanks for all your help.  This was supposed to be just one small piece of all the work that I have to do with this form, so I hope the rest goes more smoothly.  

BTW I was also able to reset the InvoiceNumber field to NOT be Identity and use my SP instead to assign it, and that didn't break anything thankfully :)  

I'm not sure which of your very helpful answers to set as the correct one, but I guess it doesn't matter.  Thanks a bunch.  I'm sure I'll have more questions as time goes on so if you're still bored keep an eye out...  

0
 
LVL 5

Expert Comment

by:Krys_Wilson
Comment Utility
Great!  I'm glad it worked for you.

I'm still subscribed to this thread so if you post here I get an email.  Ill be glad to help you out the next time and so will all the other experts I'm sure.

Thanks for the points!

Chris
0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
Hey Chris :)  Just thought I'd let you know I have an open question 500 pts that nobody's felt like helping me with.  it doesn't seem so complicated, so if you are bored at work on Monday, give it a look!  
http://www.experts-exchange.com/Databases/MS_Access/Q_21004932.html
0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
Er.. I mean Tuesday.  I forget about these holiday things sometimes. ;o)
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now