Separate form for new records from updating records? 2

[barbola]

I have a sales order form. If it is a new order, then some fields are combo boxes and others are text boxes.

If it is an existing order, it is the reverse, and displays an order already in the database.

What is the recommended method for handling this? Is it better to have a separate form open for entering new order information, and upon saving, it opens the form for existing orders and displays the info that was entered plus allows entering other details.

This is how I had it, but changes to one form always had to be made on the other form. I am starting fresh, and have an option box for New or Existing. I suppose I could display/hide different fields depending on the status, but I get easily confused.

Are there any experienced developers with opinions on this? I am not one to sit and write hundreds of lines of code. I want it clean and simple.

This is why I avoid all the "if a user enters a record, and it doesn't exist, go through a gazillion lines of code to check and add the record, then display all the other fields for data entry rather than the fields from the table, and then save all the data entry to the table, etc etc" I always seem to have to look up every little thing I want it to do. I try it one way, and it works but the next time I try the same thing where it makes sense to use that code, it doesn't work.

thanks.

 

[TheAceMan1]

How are ya barbola . . .

With a button you simply toggle a forms [blue]Data Entry[/blue] property. To review the property put the cursor on the property line and hit F1.

BTW: Whats the [blue]Default View[/blue] property of the form ... Single Form, Continuous Form, Data Sheet?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[barbola]

The default property is a single form. The form has a tab control with subforms on some of the tabs.

Am I blind? I cannot see a Data Entry property on the field.
It is not on the Data tab, or Format, or Event or Other or All. This is Access 2007.

thanks.

 

[TheAceMan1]

barbola . . .

Its a form property not a control property ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[barbola]

OK thanks. I think I can work with that. I am having another issue, new thread

 

[barbola]

If I do this, then I still have to change my combo boxes to text boxes and vice versa.

I want the user to be able to select a customer for new orders but not for existing orders.

I want the user to be able to select the existing Order from a combo box, but use a textbox to enter a new Order number.

So I guess I do need to hide/display two fields for each record? Or is there a way to code this? I can't find it.

 

[TheAceMan1]

barbola . . .

You only need a [blue]bound[/blue] sales order form that shows [blue]existing![/blue] ... and if I have this right ... [blue]you want to disallow editing unless its a new record.[/blue]

What you do in the forms [blue]On Current[/blue] event is [blue]disallow editing[/blue] the fields if its not a new record, ... [blue]allow editing[/blue] otherwise. Example:

[blue]   Me.AllowEdits = (Me.NewRecord = True)[/blue]

[blue]Your Thoughts . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[barbola]

I don't understand but put that line of code with True where it's a new record as selected in the option group, and put it as false for existing records.

It was working okay, but now I am trying to use code to change the option from New to Existing if the user enters a Customer Order # that already exists.

User clicks New and the following code is run:

Private Sub optNewOrder_AfterUpdate()

    Select Case optNewOrder

        Case 1 'New
            Me.DataEntry = True
            Me.cboCustPO.Visible = False
            Me.txtCustPO.Visible = True
            Me.AllowEdits = (Me.NewRecord = True)
            DoCmd.GoToRecord , , acNewRec

        Case 2 'Existing
            Me.DataEntry = False
            Me.cboCustPO.Visible = True
            Me.txtCustPO.Visible = False
            Me.cboCustPO = ""
            Me.AllowEdits = (Me.NewRecord = False)
            Me.optSortView.Visible = True

    End Select

End Sub

Now the user enters an existing Order, and this code is run, but I get errors:

Private Sub txtCustPO_AfterUpdate()

    Dim nCount As Integer
    
    strCustPO = Me.txtCustPO
    
    nCount = DCount("[CustPO]", "SalesOrder", "[CustPO]='" & Me.txtCustPO & "'")
    
        If nCount > 0 Then
            'Cust PO Exists
            optNewOrder = 2
            Me.cboCustPO.Visible = True
            Me.cboCustPO.SetFocus
            Me.cboCustPO = strCustPO
            Me.txtCustPO = ""
            'Me.txtCustPO.Visible = False
            Me.optSortView.Visible = True
            
            Me.AllowEdits = False
            Me.AllowAdditions = True
            Me.AllowDeletions = False
            
            DoCmd.SearchForRecord , "", acFirst, "[CustPO] = '" & Me.cboCustPO & "'"
            
    End If
            
End Sub

I had Me.DataEntry = False and Me.AllowEdits = (Me.NewRecord = False) but that gave me errors about duplicate records going into the database. To me that meant it was still trying to save the new record. So I tried blanking out the text box and a few other things but it always stopped at the dataentry line.

Now I don't get errors but with the CustPO number in cboCustPO, the searchforrecord thing doesn't work. My form won't show me that Order information.

What am I missing?

 

[TheAceMan1]

[blue] ... If I do this, then [purple]I still have to change my combo boxes to text boxes[/purple] and vice versa.[/blue]

Need more detail on this!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[barbola]

Well, I use a text box that displays for entering a new Order. If they want to select an existing order, the combo box displays.

I don't know how to use just one box. If I have an unbound combo box, then how does a new entry get saved? If it is a bound combo box, then for some reason the record for the selected Order doesn't display. But it seems I have that problem now.

 

[TheAceMan1]

barbola . . .

Sorry to get back so late.

[blue]So I guess I do need to hide/display two fields for each record?[/blue]

You need bound textboxes and unbound comboxes. When a selection is made from a combo with a related textbox you update the hidden textbox. This way you can save as you normally do.

[blue]Your Thoughts? . . .[/blue]

BTW ... you could upload a scaled down model of your db at [link 4shared.com]4Shared[/url] (its free!) to speed things up here. Also, before uploading convert to Access 2000 if you can.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[barbola]

I ended up creating a new form for new orders. It works great, less code haha.

Thanks.

 

[TheAceMan1]

barbola . . .

Thats Resolution!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Christineeve]

Thanks for posting this question and the following answers. I think they will help me to resolve a problem I'm having. My problem is nearly identical. Again, thank you for posting the question and the suggestions!

Christine