Working with form records

[Technics]

Want to see if anyone has any ideas on this. I have an order form with a products record table (see photo). The Hourly Billing record is produced by another form that opens the order form (photo). My issue is when I go to type something in the notes section as I did in photo, the second record line auto fills with the "no product name" 1 $0.00 $0.00 record line. There are times when I will only bill for the hourly billing and as long as I don't need to add notes all is fine but I always have to add notes so I have to delete the null line. Does anyone know a way to stop it from adding the null record. I tried no to Add Additions in properties but that did not do anything to change it.

 

[MarkK]

Does anyone know a way to stop it from adding the null record.

Do you have code somewhere that adds that null record? If so, I would debug by setting a breakpoint in that code. Then cause the problem. When execution hits your breakpoint, pop open the Call Stack viewer and look at what routines call what other routines such typing in the notes field causes your insert code to execute.
From that information, determine what/where/when to perform logic to interrupt that chain of events.

 

[GaP42]

When typing in the notes section it appears you are adding that note to a current record (which has no product identified). Is that what you want to do? (I would expect some validation to prevent that occurring). The record should be saved when moving focus off that record (with validation). As suggested by @MarkK step thru the code - from where a note is added to a record.

 

[The_Doc_Man]

I see what appears to be a border inside the main form, enclosing the sub-form and the notes section. The question is, how do you get to the notes section? By mouse/cursor or by tabbing off the end of the top line? How is that note field related to the continuous form?

What you show us looks nice, but we cannot see the details or examine the properties of the form, and that is what we would need to properly diagnose this.

 

[Technics]

When typing in the notes section it appears you are adding that note to a current record (which has no product identified). Is that what you want to do? (I would expect some validation to prevent that occurring). The record should be saved when moving focus off that record (with validation). As suggested by @MarkK step thru the code - from where a note is added to a record.

No, this is not what I want to do. Before I type anything into the notes the only record is the hourly billing record which is all I want. Typing in the notes section is what creates the blank $0.00 record line. I used to work with Access in the late 90's but not since. Just now getting back into it due to having more time to mess with it but not sure exactly what MarkK is asking me to do although sounds interesting. The cursor is active in the totally blank second record so I've been told this is why once I start typing in notes the record is created. I guess I'm wondering if there is a way to keep the cursor from becoming active for the next continuous record and if I need to add another record I can click to put the cursor in the next record.

 

[Technics]

I see what appears to be a border inside the main form, enclosing the sub-form and the notes section. The question is, how do you get to the notes section? By mouse/cursor or by tabbing off the end of the top line? How is that note field related to the continuous form?

What you show us looks nice, but we cannot see the details or examine the properties of the form, and that is what we would need to properly diagnose this.

It is a subform within a form. I click the notes section to begin typing by use of the mouse. I've never tried tabbing over. Anything typed in the notes section is added to the invoice along with whatever product records are visible. This is why I have to delete the $0.00 record because it shows up on the invoice but means nothing.

 

[Technics]

OK, I've noticed that if I go ahead and add product to the second record and then type something into notes it does not auto populate the third record line. It's like the first record, hourly billing, is ignored because it is created by another form that opens the order form so I guess the second record line is kind of the first line when the form opens. Sorry guys, thinking out load.

 

[Gasman]

Probably easier to upload a copy of your DB?
If you need to obscure the data, you can use this

 

[Technics]

Here is a copy of the database. Sequence to get to issue. MainMenu, click on Customer Form, then in Customer form click on Timesheet. Create starttime and endtime then click on Create Invoice. Once you are on Order Form you'll see the Hourly Billing record for whatever time you created. Go to first Notes box, type something and you will see the "no product name" 1 $0.00 $0.00 record popup.

 

[MarkK]

The notes field is bound to the same RecordSource as that which drives the continuous form. If the record selected in the continuous form is on a new record, and you start typing in the notes field, this creates a row.

One option to prevent this is as follows...

Private Sub Form_Current()
    Me.Notes.Enabled = Not Me.NewRecord
End Sub

This code disables the notes field if the current record does not exist yet, effectively preventing the user from ever using the notes field to create a row.

Also, I think this is very tidy database. Nice work. I've seen a lot of crap on here, and this is not that.

And if you wanted to get fancy, you could do...

Private Const FMT_Enabled = "@;""Notes: (Notes will show with order)"""
Private Const FMT_Disabled = "@;""Notes: (No current record)"""

Private Sub Form_Current()
    Me.Notes.Enabled = Not Me.NewRecord
    If Me.Notes.Enabled Then
        Me.Notes.Format = FMT_Enabled
    Else
        Me.Notes.Format = FMT_Disabled
    End If
End Sub

 

[xavier.batlle]

@Mark has explained what the problem is. A simple way to avoid going to a new record is changing this line on your create Invoice button:

Richard Rost would be happy to see this database.

 

[Technics]

The notes field is bound to the same RecordSource as that which drives the continuous form. If the record selected in the continuous form is on a new record, and you start typing in the notes field, this creates a row.

One option to prevent this is as follows...

Private Sub Form_Current()
    Me.Notes.Enabled = Not Me.NewRecord
End Sub

This code disables the notes field if the current record does not exist yet, effectively preventing the user from ever using the notes field to create a row.

Also, I think this is very tidy database. Nice work. I've seen a lot of crap on here, and this is not that.

And if you wanted to get fancy, you could do...

Private Const FMT_Enabled = "@;""Notes: (Notes will show with order)"""
Private Const FMT_Disabled = "@;""Notes: (No current record)"""

Private Sub Form_Current()
    Me.Notes.Enabled = Not Me.NewRecord
    If Me.Notes.Enabled Then
        Me.Notes.Format = FMT_Enabled
    Else
        Me.Notes.Format = FMT_Disabled
    End If
End Sub

Thanks Mark, nice reply. The only thing about the notes is sometimes the only thing I want to bill for is the Hourly Billing but I need to add notes about the hourly billing. This is why I go straight to notes when the order form opens. Not sure if you went further but if you click on Invoice you will see whatever notes your type in will show up on the final invoice. This is why I am trying to figure it out because unless I delete it, the $0.00 record also shows on the invoice which will make no sense to the customer.

 

[Technics]

@Mark has explained what the problem is. A simple way to avoid going to a new record is changing this line on your create Invoice button:
View attachment 121814

Richard Rost would be happy to see this database.

Yes, I did start with his template. I actually ran this by him and he said this was kind of the way of Access. He gave me a way to force that the next record had to be filled before notes could be accessed but as I replied to Mark there will be times that the only charge will be the Hourly Billing. Thanks for the vba update. I'm going to try it.

 

[Gasman]

Here is a copy of the database. Sequence to get to issue. MainMenu, click on Customer Form, then in Customer form click on Timesheet. Create starttime and endtime then click on Create Invoice. Once you are on Order Form you'll see the Hourly Billing record for whatever time you created. Go to first Notes box, type something and you will see the "no product name" 1 $0.00 $0.00 record popup.

Nice explanation of issue. Most people just chuck up a database and expect us to find the issue.

 

[Gasman]

Thanks Mark, nice reply. The only thing about the notes is sometimes the only thing I want to bill for is the Hourly Billing but I need to add notes about the hourly billing. This is why I go straight to notes when the order form opens. Not sure if you went further but if you click on Invoice you will see whatever notes your type in will show up on the final invoice. This is why I am trying to figure it out because unless I delete it, the $0.00 record also shows on the invoice which will make no sense to the customer.

When you create your invoice (hopefully a report?), you could exclude any records of $0 ?
Best to fix it in the first place though.

 

[Technics]

@Mark has explained what the problem is. A simple way to avoid going to a new record is changing this line on your create Invoice button:
View attachment 121814

Richard Rost would be happy to see this database.

One simple line of code! I've received complete overhaul of vba from some. It is to my exact image of what I was looking for. Notes about the billing go on the invoice under hours, minutes, starttime and endtime and if I do add a product notes for that go under the product. You are the Master. Thank you so much.

 

[Technics]

Nice explanation of issue. Most people just chuck up a database and expect us to find the issue.

Thanks Gasman. Not sure if you saw post from xavier.batlle but his one line of code did the trick in every way. Put it in and see the results you get on the invoice, yes, report!

 

[Technics]

I want to thank everyone for your input on this one. When I can't figure out or find the answer this is where I come. AI couldn't even do it. This forum rules!

 

[Gasman]

AI could probably do it, but you would have to be very specific and that is why I asked for a db sample.