GoToRecord acNewRec not working in 2010

Greetings. I have a form that automatically creates a new record each day, the first time the form is opened that day. Code in the Form_Load event checks the date value in the last record of the table, and if it is less than the current date, it creates the new record. Otherwise, it stays on the last record. Here's the code:


	Code:
	Private Sub Form_Load()
    DoCmd.GoToRecord , , acLast
    If PODDate.Value < Date And _
       Weekday(Date) > 1 And Weekday(Date) < 7 Then ' No record yet today, and not a weekend
            DoCmd.GoToRecord , , acNewRec
            PODDate.Value = Date
    End If
End Sub

This has been working fine in Access 2003, but in 2010 the acNewRec command doesn't work. Stepping through the debugger, it correctly goes to the last record after the acLast command, but after the acNewRec, it still points to the last record (PODDate still contains yesterday's date), even though no error seems to have occurred. It then updates yesterday's record with today's date, which forces me to have to go into the table every day and fix it. When I step through the 2003 version, PODDate contains null after the acNewRec command, as it should.

We have mixed (2003/2010) users accessing the database, and either one could be the first to open the form. Any help would be appreciated.


Post your answer or comment

comments powered by Disqus
I created a db in Access 2007. I have a form that opens when the database opens. Then a button that opens another form. There is no data link - just a new form. In Access 2010 the first form opens fine. The second form opens but none of the controls are visible. I have checked the properties and all controls are set to visible. Does anyone know why this is happening?

Another issue with the same database. The first form is set to open with all controls locked to prevent accidental data changes. There is a button linked to a macro to unlock the controls for editing. The button works fine in 2007 but does not work in 2010. Any ideas?

My application links to an Excel spreadsheet which is from a bank download. Everything works perfectly in Access 2010 including a button calling acCmdLinkedTableManager. For my end users this spreadsheet resides in a variety of locations, hence the need to re-attach in the runtime environment. The button, however, produces no response in runtime and no error indication; just a slight flicker suggesting it is NOT disabled in any way.

Is Linked Table Manager functionality not available in Access 2010 runtime? It certainly was in earlier versions.

Any suggestions or ideas would be welcome.

Hi
I've a database that I am trying to replicate, the problem is that the macros continue to work in the master, however they do not work in the replicas.
Is this a recognised problem or am i missing something.
Your help will be of great value.

Hi,

I have below EXPORT command in Access VBA code, it does not work in some PC'S (Access 2007).

COMMAND:
.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExporter", strFileName, True, "Data"

While generating report, it will appear loading box then it will disappear.
It will not generate excel sheet.

Any suggestions please.

Kind Regards,
Sam.

What would cause the VBA to not work in an MDE even though it works perfectly as the MDB it is created from? Running in fully updated A2007.

No errors with the MDE. VBA just doesn't run. It is in a Trusted Location and the earlier versions of the same database worked perfectly until some recent changes. Couple of new linked tables and a listbox added to a form. Some fairly minor code revisions.

Tried all the usual fixes. Compact & Repair. Decompile. Import everything to a new database. Rebooted.

All suggestions, no matter how wild, much appreciated.

A while back I created an Excel Macro that converts a plain spreadsheet we commonly get into a format that makes it easy to track items and removes all the columns I dont use. Well recently people want that macro from other organizations, and since I cannot figure out how to explain to them how to set it up I created an Access database that would import the document, and export it with all my nifty modifications. Only problem is, my VBA code from Excel is not working right in Access. I have discovered a way around it for most lines of code and I am going line by line fixing it, but one line will not work no matter what I try. It is a simple Replace function, the dates from the original document are stored as text and have "-" in them; I simply want to replace the "-" with a space " " but the same code that works great for this in Excel is not working in Access. I am using Access 2007, here is my code:

	Code:
	Private Sub Export_EPR_Tracker_Click()
Dim FreshFile As String
FreshFile = FileOpenDialog
Dim dAt As String
dAt = " " & Format(Now(), "dd-mmm-yy")
Dim pA As String
pA = "C:Usersnathan.mitson.AVIANODocumentsMS Practice StuffEPR TrackersEPR Tracker" & dAt & ".xlsx"

DoCmd.TransferSpreadsheet acExport, 10, "Alpha Roster Import", pA, True
Dim xlApp, xlBook As Object
Dim xlSheet As Object
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.workbooks.Add(pA)
Set xlSheet = xlApp.workbooks.Open(pA).sheets(1)
xlApp.Visible = True

    Dim EPRDue1 As Variant
    Dim EPRDue2 As Variant
    Dim Val1 As Variant
    Dim Val2 As Variant
    Dim Val3 As Variant
    Dim x As Variant
    Dim LResult As String
    
With xlApp
.Application.sheets("Alpha_Roster_Import").Select
    a = xlApp.Application.WorksheetFunction.CountA(xlApp.Range("B:B"))
    x = 2
    .Columns("C:C").Select
    .Selection.Cut
    .Columns("A:A").Select
    .Selection.Insert Shift:=xlToRight
    .Range("C:E, G:W, Y:AA, AC:AF, AH:AI, AK:AM, AO:BH").Delete
    .Columns("G:G").Select
    .Selection.Cut
    .Columns("E:E").Select
    .Selection.Insert Shift:=xlToRight
    .Columns("H:H").Select
    .Selection.Cut
    .Columns("D:D").Select
    .Selection.Insert Shift:=xlToRight
    .Columns("G:G").Select
    .Selection.Insert Shift:=xlToRight, _
        CopyOrigin:=xlFormatFromLeftOrAbove
    .Columns("G:G").Select
    .Selection.Insert Shift:=xlToRight, _
        CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("D:D, F:F, I:J").Select
**.Selection.Replace What:="-", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    .Range("G1").Value = "DUE TO FLT"
    .Range("H1").Value = "DUE TO SQ"
    .Range("H2:H" & a).Formula = "=RC[1]-30"
    .Range("G2:G" & a).Formula = "=RC[2]-45"
    'Do
        'Val1 = xlApp.Range("D" & x)
        'Val2 = xlApp.Range("J" & x)
        'Val3 = xlApp.Range("F" & x)
        'EPRDue1 = Val1 - Val2
        'EPRDue2 = Val1 - Val3
        'If EPRDue1 > 120 And EPRDue1 < 364 And EPRDue2 > 120 Then
        '.Range("I" & x).Value = xlApp.Range("D" & x) - 30
        'End If
    
        'x = x + 1
    'Loop While x

I am currently running Access 2000 and would lke some help. I don't understand why one pice of code will work one place but not another.
The below piece of code will work in the older legacy databases I have, but if I try and use it in a new database I've just created. I get type mismatch, and when I debug the
Set rst = db.OpenRecordset("calldata")
is highlighted.
Is the below an old way of doing things, so by default does not work in a newly created Access 2000 database, and if so what's the correct way of carrying out the below or if not any ideas why it's not working.
Hope you can help, its driving me mad

Dim db As Database
Set db = CurrentDb
Dim message As String
Dim email As String
Dim rst As Recordset
Set rst = db.OpenRecordset("calldata")

email = rst!PA
rst.MoveNext
Do While Not rst.EOF
email = email & ";" & rst!PA
rst.MoveNext
Loop
rst.Close

For some reason, I cannot get Shift-Click to work in Access 2010.

Originally, I put in a shift-click disable module to prevent tampering with the database, and had a secret button to click to disable the module, so I could still work in the database.

Well, that secret button decided it didn't want to work in 2010, so I opened the file back in 2003, deleted the secret button, and deleted the shift-click disable module.

When I open the database back in 2010, shift-click still won't open the database up. I've confirmed that it works in 2003; I just need it in 2010 now.

The database is attached if anyone has an idea.


Thanks,

I'm trying to produce a calculated field in my table that will calculate the next month (same day) as another field in my table and cannot find the function to produce this result... the dateadd function doesn't seem to work in 2010 access and I'm stuck!!! Help please??

Oh, and in the event that the original date was, for instance, 1/31, I know that obviously the result couldn't be 2/31 so it would just need to show 2/28... I've been working on this for weeks and cannot figure this one field out...

Hi all,

I have a form where I want to show three numbers:
- Competitors who are member (they have Memnr) =Textfield 1
- The ones without Memnr =Textfield 2
- Total of competitors =Textfield 3

In Access2003 this os working fine. in 2007 the second field is displying
"Name?"

Textfield 1 has property :
Name = Teller
=(Aantal([MEMNR]=Onwaar)) Translated: = Number([MEMNR]=FALSE))

Textfield 2:
Name = Tekst68
=[RecCnt]-[Teller]

Textfield 3:
Name = TotaalTeller
=Abs(Aantal([MEMNR] Is Not Null Or ""))

Who can explain why this is not working in 2007? And help me get it working.. ;-)
Thanks

I have a standard combobox filter that filters a forms records based on the name that is selected in it.

The form can be opened in Add, Edit and View mode.

The combobox filter does not work in View mode though - is there any way to fix this?

Can someone tell me why my access database will not work in .Net 2003
yet in vb6 it works fine. In .net i took some of the fields out and then it would work. But if i put them back in it won't work.

Dear Friends

Can somebody varify the following code for me? It works in MS Access 2003, but not in 2010.
It ask me to debug at line
Set qryDef = CurrentDb.QueryDefs(strQueryName)


Thanks


	Code:
	Option Compare Database
Private Sub Command2_Click()
Dim strCrit As String
Dim strCritNO As String     'Criteria for new old
Dim QueryName As String
Dim QueryName2 As String
Dim qryDef As DAO.QueryDef
Dim qryDef2 As DAO.QueryDef
Dim strSelect As String
Dim strSelect2 As String
 
strCrit = Me.cobCrit
strCritNO = Me.FirstDate
 
QueryName = "000MainService"
QueryName2 = "111OtherOR"
strNewT = newTable
strQueryName = QueryName
strQueryName2 = QueryName2
 
strSelect = "SELECT " & strCrit & " AS Period,"
strSelect = strSelect & strCritNO & " AS 1stDate,"
 
strSelect = strSelect & "[0000MainService].Code, [0000MainService].CaseMx, [0000MainService].Sex, [0000MainService].Age,
[0000MainService].Risk1, [0000MainService].Risk2, [0000MainService].ProjSite, [0000MainService].Project,
[0000MainService].Staff, [0000MainService].DorO, [0000MainService].Diagnosis, [0000MainService].DrugSex,
[0000MainService].NDist, [0000MainService].SDist, [0000MainService].NSReturn, [0000MainService].DWater,
[0000MainService].Condom, [0000MainService].DCSL, [0000MainService].FUCSL, [0000MainService].PreCSL,
[0000MainService].Testing, [0000MainService].PostCSL, [0000MainService].FCSL, [0000MainService].PSC, [0000MainService].YCSL,
[0000MainService].GCSL, [0000MainService].Referral, [0000MainService].To, [0000MainService].Fr, [0000MainService].For,
[0000MainService].Meal, [0000MainService].HE, [0000MainService].Remark, [0000MainService].BHCID,[0000MainService].Dx1  from
0000MainService  "
strSelect2 = "SELECT " & strCrit & " AS Period,"
strSelect2 = strSelect2 & "OtherOR.ORID, OtherOR.ProjSite, OtherOR.Project, OtherOR.ClientType, OtherOR.Male, OtherOR.Female,
OtherOR.Condom, OtherOR.HE, OtherOR.Meal, OtherOR.Referral, OtherOR.To, OtherOR.For, OtherOR.Staff, OtherOR.Remark FROM
OtherOR;"
 
Set qryDef = CurrentDb.QueryDefs(strQueryName)
Set qryDef2 = CurrentDb.QueryDefs(strQueryName2)
qryDef.SQL = strSelect
qryDef2.SQL = strSelect2
qryDef.Close
qryDef2.Close
'DoCmd.OpenQuery QueryName, acNormal, acEdit
'DoCmd.OpenQuery QueryName2, acNormal, acEdit
End Sub



I have an automated workbook creation in an xls document. It automatically creates and saves a workbook to a specified name and path.

Works fine in Excel 2007 on XP but will not save the in 2010 on Windows7. No error message. It simply doesn't save.

Ultimately the user is presented with a Save dialog when they go to close the workbook which we leave open after the attempted SaveAs.

The document that runs the macro is digitally signed and the is a Trusted Publisher.

We do have an Administrative Template in Group Policy to set the default save location. We didn't have this on 2007.

However I would have thought the SaveAs method would override the default location anyway so I doubt it is the issue.

All suggestions welcome.

Hi

After much hussle I got my custom ribbon working in 2007 version. Now that I upgrade to 2010 my call back functions are not seen. The office help isn't helpful. Can anybody tell me why?
Engr. Aliyu

Hi all.

Having a problem with a database I'm trying to use in Access 2010. I have forms that reference a SharePoint list.
It works well in A2007, of course, but when I use it in A2010, it locks up solid when I attempt to write to the SharePoint (hourglass, 'Not Responding', never recovers). This will occur in Datasheet view, or when I click the pencil in a form. All I need to do is edit a field, any field, in the record.
The SharePoint list (SP 2010 server) utilizes lookup fields that reference smaller SP lists in the same site, and simpler Choice fields. I also reference the User Information list in setting ownership of assets that the records reference.
I have tried to delete and re-link the SP lists thinking it was a difference in handling.

Q: Is there a setting, parameter, or something in Acc2010 that can be flipped to allow this file to work?

Any ideas in this area would be helpful. I don't wish to rewrite the accdb!

Background: DB created in access 2003, my work upgraded to Access 2010. Now when I open the DB in 2010 and try to filter by form the "Filter" button stays inactive.

Basically on my form i select "filter by form" then add a number into the field I want to filter. The apply filter button stays "inactive" it's not clickable. I've read a few places that says it's an issue when upgrading to MS Access 2010. Does anybody have a solution? Much appreciated, thanks.

I have several computers that are being upgraded to 2010.

I have a query that updates 2 fields; tblMain.WorkingDate and tblMain.Time. I know Time is a reserved word but that is not the problem. I update the field "WorkingDate" to Date() and "Time" to Time(). They are Short Date and Short Time respectively.

This query works in 2003, 2007, and 2010. However, there is one computer that was just upgraded to 2010 and it will not work on that computer. I get the error message:

Run-time error '3085':

Undefined function 'Date' in expression.

Does anyone know why this is happening on that computer?

I have a report that I designed in Access 2007 and it worked fine. Now that we're using 2010, it is all messed up. The query gets the correct data, but the report does not display / print the correct data. Sometimes when I print the report for a specific student twice, I'll get different data on each.

Can anyone please help me?!!

Thanks,
Frustrated

Hi everyone. Thank you in advance for any ideas ... I have an access db that links to a Pervasive database. I administer the database in 2010 version but one of my user is still on 2000 (he wont change so thats not an option). I had to change the ODBC for a small group of tables. The links are working on my end but not his - he gets an ODBC error.

Any ideas on why this is happening and how to resolve it? Thanks!

Hey guys. I've got some code here that seems to present a bit of an issue. It basically joins two tables together, but ONLY includes records that are unique to either side (so basically joining two tables and not including records where the employee number shows up in both tables.) It works fine in 2010, but in 2007, I only get the data from the first table in the resulting report, not the second. Can anyone looking at this see something obvious that would present an issue in 2007? I'm guessing it has to do with the UNION statement, because everything after that has to do with merging the second table, which is what isn't showing up.

The two tables being merged are 'ShiftChangeA' and 'ShiftChangeB.' I'm thinking now I should have put this in a different forum section. I originally put it here because this SQL code is in the Record Source of my Report.

Quote: SELECT ShiftChangeA.[Employee Number], ShiftChangeA.Category, ShiftChangeA.[Shift Description], ShiftChangeA.Date, ShiftChangeA.[Start Time], ShiftChangeA.[End Time], ShiftChangeA.Duration, ShiftChangeA.[Day Of Week], ShiftChangeA.[Employee First Name], ShiftChangeA.[Employee Last Name], ShiftChangeA.Route, ShiftChangeA.Bus FROM ShiftChangeA LEFT JOIN ShiftChangeB ON ShiftChangeA.[Employee Number] = ShiftChangeB.[Employee Number] WHERE (((ShiftChangeB.[Employee Number]) Is Null) AND ((ShiftChangeA.[Shift Description]) Not Like "*20AT*" And (ShiftChangeA.[Shift Description]) Not Like "*60AT*" And (ShiftChangeA.[Shift Description]) Not Like "*90AT*" And (ShiftChangeA.[Shift Description]) Not Like "*AT2*" And (ShiftChangeA.[Shift Description]) Not Like "*AT3*" And (ShiftChangeA.[Shift Description]) Not Like "*AT4*")); UNION SELECT ShiftChangeB.[Employee Number], ShiftChangeB.Category, ShiftChangeB.[Shift Description], ShiftChangeB.Date, ShiftChangeB.[Start Time], ShiftChangeB.[End Time], ShiftChangeB.Duration, ShiftChangeB.[Day Of Week], ShiftChangeB.[Employee First Name], ShiftChangeB.[Employee Last Name], ShiftChangeB.Route, ShiftChangeB.Bus FROM ShiftChangeB LEFT JOIN ShiftChangeA ON ShiftChangeB.[Employee Number] = ShiftChangeA.[Employee Number] WHERE (((ShiftChangeA.[Employee Number]) Is Null) AND ((ShiftChangeB.[Shift Description]) Not Like "*50B*" And (ShiftChangeB.[Shift Description]) Not Like "*52B*"));

I created a database in Access 2010, but saved it in 2002-2003 format because the people that I created the database for could not open it. So, I then imported all the objects into a new db and saved it in the 2002-2003 format mentioned above.

All of the buttons work for me. I sent it to them as a zip file. They can open it, but the buttons do not work. I think this happened to me before and it had to do with database being read only when the user opens it and they had to change the "allow editing" or someting like that.

The users have 2007, what setting do they need to change to make this work? I think this is the issue noted above. Everything is working fine for me.

Help? I will not get paid until I fix this. I have seen this before, but it is driving me crazy.

Donna

I have a strange problem. I am trying to add a new record to a subform but i keep getting the "Error 2105 - You can't go to the specified record".

I have to main forms (frmFeedOrder and frmMineralOrder), each form has a subform & a products listbox. When a product is selected in the list box (double click) it should add a new record to the subform and place the order details into it.

Strange thing is it works fine on the frmFeedOrder which i setup first but not the frmMineralOrder. The latter form i copy & pasted and changed the necessary fields.

Could anyone explain why i might be getting this error?

Sample db attached. (Use test farm as the active farm, then load Mineral Order from menu, select a product from listbox. Then try to select another product but it will not add the new record using the docmd.gotorecord , , acnewrec command).

Thanks in advance.

Hi Everyone,

I am new in access, maybe this has a very simple answer. I had to migrate this function previously in access 2000 to access 2010 while working with adp's all works fine but when I convert my adp's into adp's the very simple steps shown below dont work anymore and I get the error


Error #28557

* The expression may not result in the name of a macro,
the name of a user-defined function, or [Event Procedure].
* There may have
been an error evaluating the function, event, or macro.


Private Sub cmdPrint_Click()
Dim strDryCleaningParameters As String
DoCmd.Echo False

strDryCleaningParameters = gobjTicket.gstrLaundryNumber

SetDryCleaningReportParameters "rptWwLaundryDryCleaningPrintTickets", strDryCleaningParameters
DoCmd.OpenReport "rptWwLaundryDryCleaningPrintItemTickets", 2

DoCmd.PrintOut

DoCmd.Close acReport, "rptWwLaundryDryCleaningPrintItemTickets", acSaveYes

' Set echo as it was
DoCmd.Echo True
End Sub

What am I getting wrong? I tried also without Print out and although no error is triggered no print is coming out =( when pressing the button I receive a quick screen saying printing but then nothing is printed. Any idea?

Private Sub cmdPrint_Click()
Dim strDryCleaningParameters As String

strDryCleaningParameters = gobjTicket.gstrLaundryNumber

SetDryCleaningReportParameters "rptWwLaundryDryCleaningPrintTickets", strDryCleaningParameters
DoCmd.OpenReport "rptWwLaundryDryCleaningPrintItemTickets", acViewNormal

End Sub


Not finding an answer? Try a Google search.