Solved

code for new record and refresh query and form in MS Access

Posted on 2007-03-30
15
275 Views
Last Modified: 2008-02-01
What code do I use to being a new record and refresh the query (and thus the form where the command button resides)?

I tried

DoCmd.GoToRecord , , acNewRec
Me.Refresh

and no luck

Thanks.
0
Comment
Question by:mamadouthiam
  • 9
  • 6
15 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 18825392
Hi mamadouthiam,
from your description, I can't tell what it is you are trying to accomplish and what is not working in relation to what you are looking for.  Thanks.

PDB  
0
 

Author Comment

by:mamadouthiam
ID: 18825422
Sorry,

I have a form for data entry where the user will click a command to start a new record.  The form is based on query and the field that I want o populate when they click (and start a new record) is a combination of an Autonum field and a text string.

Thus, when they click "start new request", the "tracking field should populate with "OurCompany - 1". If they click again, it shoudl start a new record that says "OurCompany - 2".

Does this help?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 18825509
Try this:
DoCmd.GoToRecord , , acNewRec
Me.Dirty = False

or this:
DoCmd.GoToRecord , , acNewRec
Me!TrackingField.Recalc
Me.Dirty = False
0
 

Author Comment

by:mamadouthiam
ID: 18825640
puppydogbuddy,

No good.

DoCmd.GoToRecord , , acNewRec ---> Nothing
Me.Dirty = False

or this:

DoCmd.GoToRecord , , acNewRec --> Error, object doesn't support...
Me!TrackingField.Recalc
Me.Dirty = False

I think I know what should happen.  

New Record
Requery (qrymyqery)
Refresh

I just don't know the syntax
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 18825762
Did you open form before issuing goto new record?
Try it this way:
   DoCmd.OpenForm "yourForm", , , , acFormAdd
   Me!TrackingField.Recalc
   Me.Dirty = False      

or
DoCmd.OpenForm "yourForm", , , , acFormAdd
Me.Requery    
0
 

Author Comment

by:mamadouthiam
ID: 18825864
puppydogbuddy,


The form is already open.  User navigate to this form via the main switchboard.  Once there, there is a command to initiate a new record.  I would not want to automatically start a new record on open, b/c users also will view and edit on-going records on this form.

mama
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 18825934
If form already open and this command is giving you error:
      DoCmd.GoToRecord , , acNewRec --> Error, object doesn't support
then you need to check form properties:
            Allow additons should be set to yes
            Allow edits should be set to yes
            Data Entry should be set to No.
If the above is not the answer, please post more of your code, starting with opening of form.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 500 total points
ID: 18825988
mamdouthiam,
you can do it like this:
Me.AllowAdditions= True
Me.AllowEdits = True
Me.DataEntry = False
DoCmd.GoToRecord , , acNewRec
0
 

Author Comment

by:mamadouthiam
ID: 18826055
puppydogbuddy,

Still nothing.  All that happens is that the text in the tracking field (when it opens to the current record) disappears whereas the next number in the sequence should populate.

Can you supply the VB to requery the query behind this form?

mama
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 18826094
You no longer get the error you were getting???
In response to your question>>>>>>>>>>>>Me.Requery
0
 

Author Comment

by:mamadouthiam
ID: 18826155
No, no error.

Does it have to do with the fact that the Tracking field was built?
Tracking: "Ourcompany-  " & tblFOIA!Number

This seems like a very simple thing and I think I have done this before without any trouble...

mama
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 18826300
Not necessarily...does your menu form close when add form opens?
if menu form close, try Me.Requery; Me!Tracking.Requery......or ......Me.Recalc, or Me!Tracking.Recalc...... or
                    Me.Refresh......
If menu form stays open,
try fully qualified reference>>>>>>>>>>>Forms!YourMainForm.Requery
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 18826372
Mama,
Just realized>>>>Tracking: "Ourcompany-  " & tblFOIA!Number
is not a numeric & can't be calculated.  Think you meant the following:
Tracking: = [Ourcompany] - [tblFOIA!Number]
 
0
 

Author Comment

by:mamadouthiam
ID: 18826486
I am such an a..

The problem was that I was starting a new record but I was,t entering any information and thus the record was not changing (and I guess no reason to add another new record when I clicked again)!

I now did this:

DoCmd.GoToRecord , , acNewRec
Me.Date_received.SetFocus
Me.Date_received = Now()

Automatically dropping the date in changes the record and everything is fine.  

Sorry I wasted your time.  Thanks so much for the help though!
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 18826552
Glad you got it resolved.  That is why it helps to provide as much info as possible.  Thanks for the points and grade.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now