?
Solved

New record on open

Posted on 2012-09-06
9
Medium Priority
?
384 Views
Last Modified: 2012-09-07
I have a table for invoices.  The invoice form on load event has this code:

DoCmd.GoToRecord , , acNewRec

I have a combo box on the invoice form that shows Invoice Numbers that should pull a record based on the combo box.  But it doesn't work. Nothing happens.

How can I have a blank record load and then be able to use a combo box to find a record.

Thanks!
0
Comment
Question by:Sasha42
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 40

Expert Comment

by:als315
ID: 38375027
Can you upload sample DB with this form and some dummy data in tables?
0
 
LVL 85
ID: 38375494
Can you explain why you (a) open to a New Record and then (b) want to FIND a record? That doesn't really make much sense - generally, if you want to enter a New Record you'd move to a new, blank record and enter data.

If you're trying to fill in defaults based on a Combo choice, or something along those lines, then please let us know.
0
 
LVL 2

Expert Comment

by:BobOxford
ID: 38375911
A common issue here is that the underlying table has a field for the InvoiceID and you have Bound the ComboBox to that field.  That strategy sounds like to should work but it does not.  All it does is change the invoice Number of the current record.

What you need to do is to create an Unbound combobox on the form (you can place it anywhere but I would place it in the header of the form) have it get a unique list of invoices (usually from the Invoice table) for its RowSource.  Give it a name (cboFind will work).  In the AfterUpdate event of cboFind you would then need perform a find based on the invoice you chose in the Unbound combo box.

This can be done either using a macro object or writing some VBA code.  In order to give you an example, we would need to know the data type of the Invoice Number

I hope that gives you enough to go on.

Bob Oxford
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 20

Expert Comment

by:clarkscott
ID: 38375954
Open the form to view all data.  In the form header section, use the control wizard to create a combo box to "find record".  Follow the wizard...  

Scott C
0
 

Author Comment

by:Sasha42
ID: 38375960
I built it so that when the Invoice form opens, it opens to the first record.  The form has a New button that creates a new record and automatically populates several fields.  It also has a Find combo box to find a record based on an Invoice Number.  This works fine.

My client doesn't want to see any data in the fields when the form is opened.  So now I have it that when the form opens it creates a blank record.  If the user wants to enter a new record they still have to use the New button so that some of the fields automatically populate.   I have code that says if the form is dirty then automatically populate some fields.  If it is not dirty then create a blank record and populate.  This works fine.

When the form opens to a new record and is dirty the Find combox doesn't work and does not return an error.  Should I put the Find combo box on a subform?
0
 
LVL 2

Expert Comment

by:BobOxford
ID: 38376048
I am not quite clear about how you are doing this but let me give you an option here...The Form has a DataEntry property.  Go into Design view and set the dataEntry Property to true.  Every time the form is opened up from then on, it will be on a New Record.  No code or macro required.  (remove your code that goes to a New Record if you have it)

Now, create an ebedded macro in the afterUpdate event of the ComboBox if you like, that will search the form.  It should have 2 steps:

ShowAllrecords
SearchForRecords

The SearchForRecords action shold look for the First Record and the Where condition shold be something like:

="[InvoiceNumber] = " & "'" & [cboFind] & "'"

Do NOT use the [Screen].[ActiveControl] that the wizard creates.  For whatever reason that does not seem to work consistantly.  Using Active Control is risky since Access will sometime change the Active Control

Good Luck!

Bob Oxford
0
 
LVL 20

Accepted Solution

by:
clarkscott earned 2000 total points
ID: 38376317
You must force the new record to "not dirty" by saving the record.  In the event of your dropdown... try

docmd.runcommand accmdsaverecord.

This will save the record, release the buffer, and should allow you to move to any existing record.

Now, if you have any mandatory fields, etc. or the record is incomplete... (or any other unusual scenarios).. you may want to "encase" this command inside an "Ignore error" handler:

on error resume next
docmd.runcommand accmdsaverecord
on error [go back to your original error handler]

Scott C
0
 
LVL 85
ID: 38376418
When the form opens to a new record and is dirty the Find combox doesn't work and does not return an error.
A New Record generally will not be dirty unless the user enters data.

What do you mean by "does not work". What is it supposed to do? If you're at a New Record, what exactly are you "finding"?

If you could describe your process in more detail, we could very likely help you much better. Don't use vague terms - tell us exactly what you're doing.
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 38377075
Also, don't forget about this command.
Sometimes as we build apps... we create a record.. and then via buttons or such, move to another recordset that may relate to or depend on this original record.   Well.... this "accmdsaverecord" command makes sure your original record is created/saved prior to going anywhere else.

Yeah.. sometimes we build stuff and 'break rules'.  We learn as we go and try to improve with the next app we write.  In the meantime.....  as said.... don't forget this command.  It may come in handy again.

Good luck and have fun!

Scott C
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

850 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