Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-09-2019, 10:42 PM   #1
newbie1
Newly Registered User
 
Join Date: Oct 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
newbie1 is on a distinguished road
Forms not able to insert new record

Hi there,
It has been a while since I did programming and touching MS Access.

So recently I created a simple MS Access Form, everything is working well until I added unbound combo fields to the form and it stopped working. Since the addition of unbound combo fields, the insert fails to work.

I tested by removing the unbound combo fields, the insert works again...

so not too sure what is causing this problem?


newbie1 is offline   Reply With Quote
Old 10-09-2019, 11:43 PM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,476
Thanks: 0
Thanked 577 Times in 573 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Forms not able to insert new record

Presume you meant combobox controls, not fields. Unbound controls should not affect adding records.

If you want to provide db for analysis, follow instructions at bottom of my post.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 10-10-2019, 12:15 AM   #3
newbie1
Newly Registered User
 
Join Date: Oct 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
newbie1 is on a distinguished road
Re: Forms not able to insert new record

ok, uploaded db here.
Attached Files
File Type: zip test.zip (59.2 KB, 14 views)

newbie1 is offline   Reply With Quote
Old 10-10-2019, 01:28 AM   #4
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,476
Thanks: 0
Thanked 577 Times in 573 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Forms not able to insert new record

Multiple similar name fields indicates a non-normalized data structure - more like a spreadsheet. And now I see you are exporting data to Excel. Are you saying the export fails? Would have been helpful to provide workbook as well. However, I created a workbook with the name used in code and no problem running. Data is exported.

Those Cancel = True lines are meaningless in button click event.

You have more required fields than code validates and record cannot be saved until all are filled in. Because of error handler code, users don't get prompted for missing data. Shouldn't you make sure record is committed before exporting to Excel?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 10-10-2019 at 01:51 AM.
June7 is offline   Reply With Quote
Old 10-10-2019, 03:04 AM   #5
missinglinq
AWF VIP
 
missinglinq's Avatar
 
Join Date: Jun 2003
Location: Richmond (Virginia that is!)
Posts: 6,320
Thanks: 11
Thanked 721 Times in 671 Posts
missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light
Re: Forms not able to insert new record

When validating multiple fields, you have to Exit the Sub each time a validation fails, giving the user a chance to correct errant Control, before moving on to the next validation check...otherwise the code simply 'drops thru' to the next check!

Also, your Comboboxs are all Bound (have Control Sources) and when used like this, to populate Fields in the Form's underlying Table, should be Unbound. Delete the Control Sources for each cbo.

Now, try replacing the validation portion of your code like this...and see what happens (copy your current code and save it somewhere safe!!!)

Code:
Private Sub cmdSaveRecord_Click()


      
 If Len(Me.Customer & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter company name."
 
      Me.Customer.SetFocus
      Exit Sub
 
 ElseIf Len(Me.Duration & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter duration."
 
      Me.Duration.SetFocus
      Exit Sub

 ElseIf Len(Me.ContactPerson & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter contact person name."
 
      Me.ContactPerson.SetFocus
      Exit Sub
 
 ElseIf Len(Me.ContactNo & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter contact number."
 
      Me.ContactNo.SetFocus
      Exit Sub
      
 ElseIf Len(Me.Email & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter email address."
 
      Me.Email.SetFocus
      Exit Sub

ElseIf Len(Me.Item1 & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter item code."
 
      Me.Item1.SetFocus
      Exit Sub
      
 ElseIf Len(Me.Desc1 & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter description."
 
      Me.Desc1.SetFocus
      Exit Sub
      
 ElseIf Len(Me.Rate1 & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter rates."
 
      Me.Rate1.SetFocus
      Exit Sub

Else

DoCmd.RunCommand acCmdSaveRecord

..............


Notice that I've also saved the Record, as June7 suggested, and I'm not really sure what you're doing with the error handling you're attempting. You're using an Error Handler that is only set after all of the Sub has run (handlers should be set directly under the Sub's name) and you're using both an EH and On Error Resume Next. I'd comment out all of this code, for now.

Also, note that the user will have to click on the 'Save' button again, after correcting each errant Control.

Linq ;0)>
__________________
The Devil's in the Details!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


(All code solutions tested in Access 2003/2007, before posting, unless otherwise noted.)
missinglinq is offline   Reply With Quote
Old 10-10-2019, 03:29 AM   #6
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,476
Thanks: 0
Thanked 577 Times in 573 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Forms not able to insert new record

Wait, why should all comboboxes be UNBOUND? Only Category boxes need to be UNBOUND. Comboboxes are working fine.

Also, code worked without all those additional Exit Sub. When an If condition is met, that segment runs then execution drops out to End If. It's placement of GoToRecord and funky error handling that is issue.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 10-10-2019 at 04:01 AM.
June7 is offline   Reply With Quote
Old 10-10-2019, 04:48 PM   #7
newbie1
Newly Registered User
 
Join Date: Oct 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
newbie1 is on a distinguished road
Re: Forms not able to insert new record

Quote:
Originally Posted by June7 View Post
Wait, why should all comboboxes be UNBOUND? Only Category boxes need to be UNBOUND. Comboboxes are working fine.

Also, code worked without all those additional Exit Sub. When an If condition is met, that segment runs then execution drops out to End If. It's placement of GoToRecord and funky error handling that is issue.

Hi June7,

Thanks for your help.

so is this line that is the problem? It was working fine previously though...

Code:
Private Sub cmdSaveRecord_Click()
.

.

.

On Error GoTo NewRecord_Err
    
    On Error Resume Next
    DoCmd.GoToRecord , "", acNewRec
    
NewRecord_Exit:
    Exit Sub
    
NewRecord_Err:
    Beep
    MsgBox Error$
    Resume NewRecord_Exit
    
    Exit Sub
    
errHandler:
    
    MsgBox Err.Number & ": " & Err.Description

End Sub

newbie1 is offline   Reply With Quote
Old 10-10-2019, 04:50 PM   #8
newbie1
Newly Registered User
 
Join Date: Oct 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
newbie1 is on a distinguished road
Re: Forms not able to insert new record

Quote:
Originally Posted by missinglinq View Post
When validating multiple fields, you have to Exit the Sub each time a validation fails, giving the user a chance to correct errant Control, before moving on to the next validation check...otherwise the code simply 'drops thru' to the next check!

Also, your Comboboxs are all Bound (have Control Sources) and when used like this, to populate Fields in the Form's underlying Table, should be Unbound. Delete the Control Sources for each cbo.

Now, try replacing the validation portion of your code like this...and see what happens (copy your current code and save it somewhere safe!!!)

Code:
Private Sub cmdSaveRecord_Click()


      
 If Len(Me.Customer & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter company name."
 
      Me.Customer.SetFocus
      Exit Sub
 
 ElseIf Len(Me.Duration & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter duration."
 
      Me.Duration.SetFocus
      Exit Sub

 ElseIf Len(Me.ContactPerson & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter contact person name."
 
      Me.ContactPerson.SetFocus
      Exit Sub
 
 ElseIf Len(Me.ContactNo & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter contact number."
 
      Me.ContactNo.SetFocus
      Exit Sub
      
 ElseIf Len(Me.Email & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter email address."
 
      Me.Email.SetFocus
      Exit Sub

ElseIf Len(Me.Item1 & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter item code."
 
      Me.Item1.SetFocus
      Exit Sub
      
 ElseIf Len(Me.Desc1 & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter description."
 
      Me.Desc1.SetFocus
      Exit Sub
      
 ElseIf Len(Me.Rate1 & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter rates."
 
      Me.Rate1.SetFocus
      Exit Sub

Else

DoCmd.RunCommand acCmdSaveRecord

..............


Notice that I've also saved the Record, as June7 suggested, and I'm not really sure what you're doing with the error handling you're attempting. You're using an Error Handler that is only set after all of the Sub has run (handlers should be set directly under the Sub's name) and you're using both an EH and On Error Resume Next. I'd comment out all of this code, for now.

Also, note that the user will have to click on the 'Save' button again, after correcting each errant Control.

Linq ;0)>
Hi missinglinq,

Thanks for your help... I need all these comboboxes (except Category) to be bound as it will be data entry into the table... Except for category which is unbound as a selection to populate into the description field.

I will try out the error handling portion.
newbie1 is offline   Reply With Quote
Old 10-10-2019, 04:52 PM   #9
newbie1
Newly Registered User
 
Join Date: Oct 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
newbie1 is on a distinguished road
Re: Forms not able to insert new record

Quote:
Originally Posted by June7 View Post
Multiple similar name fields indicates a non-normalized data structure - more like a spreadsheet. And now I see you are exporting data to Excel. Are you saying the export fails? Would have been helpful to provide workbook as well. However, I created a workbook with the name used in code and no problem running. Data is exported.

Those Cancel = True lines are meaningless in button click event.

You have more required fields than code validates and record cannot be saved until all are filled in. Because of error handler code, users don't get prompted for missing data. Shouldn't you make sure record is committed before exporting to Excel?
Hi June7,

Yup the export to excel works just that the data entry to table fails.

I will remove those cancel=true.

I will go through those required fields (is under the form properties for that field right)?
newbie1 is offline   Reply With Quote
Old 10-10-2019, 05:16 PM   #10
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,476
Thanks: 0
Thanked 577 Times in 573 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Forms not able to insert new record

The fields are set as required in table. This will prevent saving record until those bound controls have data.

I have no problem with your original code as long as I fill in every control (except for all the Item and Category controls, only 1 set is required).

Still suggest should commit record before export to Excel.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 10-10-2019 at 05:24 PM.
June7 is offline   Reply With Quote
Old 10-10-2019, 05:18 PM   #11
newbie1
Newly Registered User
 
Join Date: Oct 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
newbie1 is on a distinguished road
Re: Forms not able to insert new record

Quote:
Originally Posted by June7 View Post
The fields are set as required in table. This will prevent saving record until those bound controls have data.
yup i just make this quick change, silly me cos earlier i remove some validation but forgotten about the table validation.

now its working again!

thank you so much

newbie1 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert first record into empty record set (based on query) Rx_ Forms 5 03-16-2010 06:27 PM
Using RecordSet to insert new data into a query based on two forms DRathbone General 14 02-27-2007 03:32 AM
about insert picture into a table from forms borislo Forms 4 12-30-2004 07:39 AM
Insert into tables, with active users & and from forms!! help! NoMAd Modules & VBA 0 11-10-2004 02:16 PM
insert SubDatasheet for forms smercer Forms 7 06-29-2004 07:58 AM




All times are GMT -8. The time now is 11:27 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World