adding records to recordset

Topic

New Reply

in vb6

I have a database that has a table (customers) with five attributes (custID, name, street, city, and amtPurchases). I am trying to use vb to manipulate the data in the database (part of a class I am taking). I have four text boxes that are to be used on an order entry form, one each for custID, name, street, and city. I can set up the code that will allow the user to enter a custID and vb will fill in the rest of the text boxes automatically. The problem I am running into, is there is a checkbox for new customers. When a new customer calls, the user should check the checkbox and vb should give the next available custID in the the custID textbox, then the user can enter the new customers name, street and city. The record would be updated on the lostfocus event of the city textbox.

My problem is I can’t figure out how to have vb place the next available custID into the custID textbox after checking the checkbox.

Any ideas or thoughts.

Reply | Quote

Replies

Is the CustID something the program creates or is it an autonumber? Is the form bound to the data or unbound? It makes a big difference in the answer.

Reply | Quote

the custID field in the source database is a number (integer) field. As for your second question, I would say the form (in vba) is unbound as the textboxes are not directly tied to the datasource.

Reply | Quote

So if the CustID is an integer, are you using something like SQL Server as a back end rather than a Jet mdb, which would generally use an AutoNumber (long), or are you using a text file? Are you creating CustID in code by calculating the max number and adding an increment or is it being created automatically by the database engine?

I’m not sure if you’re asking how to populate the CustID textbox with a calculated value versus just looking up an existing value and filling the field, or if I’ve misunderstood your question. Can you expand a bit on your question and say what kind of database you’re using? Are you actually opening an ADO recordset or populating an array first and then filling in the controls from that? It would help if you posted the code you’re using to populate the textbox now.

You mentioned VB 6 in your original post but VBA in your last one. It helps keep things clearer if you explain which one we’re actually dealing with, because the answer may be different depending on which application engine is being used, VB or some other application that supports VBA.

Reply | Quote

Hopefully this will make sense.

I am taking a vb class and we are learning about manipulating databases with vb code. The exercise we are supposed to do is create an order entry form that incorporates elements of three tables from an Access database. The Access database has a Customers table, an Inventory table, and an Orders table.

My problem is as follows: The project requires that the ‘user’ enter the custID, which will in turn fill in the custName, custStreet, and custCity fields. This part of the code I can handle. The issue I am facing is that the form has a checkbox for NewCustomer. By checking the NewCustomer box, the code should automatically populate the custID field with the next available number, then allow the ‘user’ to enter the custName, custStreet, and custCity information, then save this new record to the Customers table in the Access database.

The instructor had told us to “assume” that the user was “intelligent” and would only enter a valid custID, or click the newCustomer button, thereby saving us from having to build in all the code for making sure that only valid ‘data’ was entered into the custID field. I was trying to be slicker than I am, by going ahead and trying to code for all possibilities, but kept getting into trouble.

I am at work so I don’t have a copy of the code to attach, but I was using the LostFocus event on the custID field and the NewCustomer checkbox, and kept going around in circles.

I finished the project without the extra code (assuming that the user was intelligent).

Reply | Quote

Steve,

Obviously your instructor has no concept of the real world! Assuming an intelligent user, indeed!

I don’t know if such an animal even exists

I guess it’s those slight simplifications which enable them to give you an assignment you can actually finish on time.

Reply | Quote

Now, be nice, Geoff. After all, some of those users are intelligent enough to pay my salary.

And yes, the instructors do have to simplify in order to get the students through the assignment, but it would be helpful if they explained that you don’t ever rely on users doing the expected, intelligent thing in the real world because there’s always at least one user who won’t.

Reply | Quote

I am attaching a zip file with the project I came up with, using the “dumbed” down version (assuming the user will enter only correct information).

Here were the requirements of the project:

1. Use a check box to indicate if the customer is a new customer. If the customer is new then the record should be added to the CUSTOMERS Table and assign the next number as the custID. If the customer is an existing customer, then all that should have to be entered is the custID and the rest of the information should be filled in automatically.
2. Once the order is taken then the ORDERS Table should be updated.
3. If the customer orders an item that is not in the INVENTORY Table then a message box should indicate that you do not carry that item.
4. A command button should be used to print all the orders for a single day. Use the debug.print rather than printer.print to have the output displayed in the immediate window. The output should include the customer name and what the customer ordered.
5. After the order is taken then the program should also update the INVENTORY Table. If the quantity gets to 0, and a customer orders another item then a message box should indicate that the quantity is 0.
6. Hide the data control
7. An Exit command button to exit the program

I kept getting stuck on #1 above, kinda like a circular reference. going ’round and ’round.

Reply | Quote

You’re using datacontrols, so your form is really bound rather than unbound. The difference is that it’s possible to open a recordset without a datacontrol and then use code to populate unbound controls rather than binding them to fields through the datacontrol. It’s also possible to use a datacontrol but still use unbound controls and populate them through code. There are reasons for the different techniques, but they don’t concern us now.

First, you need to test in your checkbox click event whether the box is checked or not, because it’s also possible to click a box to UNcheck it, and that shouldn’t cause a new customer to be created.

Then I would remove the command to reset the checkbox value. That’s what’s currently throwing you into a loop because it’s causing the routine to recurse. With the conditional test, it will only recurse once, but it isn’t necessary unless you MUST clear the checkbox immediately, which isn’t how I read your assignment.

Put the line reseting the checkbox value into the cmdClear_Click procedure instead. That way, the checkbox will stay clicked until you hit the submit button, at which point it will be cleared. Keeping it checked until then is a visual reminder that this is a new customer.

Private Sub chkNewCust_Click()
    'Procedure for entering a new customer
    Dim intLastCustID As Integer
    
    If chkNewCust.Value = True Then
      datCustomer.Recordset.MoveLast
      intLastCustID = FormatNumber(datCustomer.Recordset!custID)
      mintNewCustID = intLastCustID + 1
      datCustomer.Recordset.AddNew
      datCustomer.Recordset!custID = mintNewCustID
      txtCustID.Text = datCustomer.Recordset!custID
      datCustomer.Recordset.Update
      datCustomer.Refresh
      txtCustName.SetFocus
'>>   chkNewCust.Value = 0 'take this out and put it in your
                           'cmdClear_Click event instead
    End If
End Sub

Reply | Quote

Charlotte – thanks for your help. I am attaching the revisions I made. The only thing I couldn’t figure out how to work was if after checking the new customer box and entering the rest of the new customers information, the new customer decided to not place an order. In this situation the new customer should have been deleted from the table. But, oh well. That wasn’t within the scope of the original project, so I am going without it.

Again, thanks

Reply | Quote

Well, I assume your assignment required you to use bound controls. Otherwise, you could have used unbound controls for everything except possibly the CustID and only added the record when they hit the submit button. Until then, you wouldn’t have a CustID, just the input from the user. When they hit submit, you would add the record (which would immediately populate the CustID control) and populate the recordset fields from the unbound fields’ values.

That’s one way, but there are others. One would be to use a messagebox to verify that the user really wanted to save the new record and if they didn’t, to use code to invoke the delete method of the recordset.

Since your form doesn’t appear to offer a method for deleting a record though, I assume that cancelling a new record was outside the scope of the assignment.

Reply | Quote

Well, then I assume you aren’t using an AutoNumber to generate the CustID field, although that would have saved you a lot of work. That means you have to generate a new CustID in code. Is that what you need help on?

You need to test in your lostfocus event for NewCustomer.Checked to tell you not to do the rest of what you would do for an existing customer. You can use the Click event of the checkbox to hold the code to generate your CustID if IsNull(CustID) or if NewCustomer is checked. Wait, is NewCustomer a checkbox or a button, or do you have more than one way to create a new customer (i.e., menu item)?

Reply | Quote