Re: Insert Into and automatic record entry

Jeff,

Here is something that might help you understand this stuff a bit better... A date is a date. The format is totally irrelevant. It only affects the way the data is displayed, i.e. it is cosmetic only, and has no bearing at all on the values you will work with in queries, code, etc.

The problem I immediately see with the code is the #s which should not be there. Also, there should be a space before the word "Values"... looks like your code runs ok without it, which surprises me. And also, I am not sure why you did not follow the suggested structure I gave you before, regarding the DataVenc=>DatePlus=>NewDate.

Also, can I ask again... is this code being run from an event on the [add Aluno 2] form, or the [fmAlunosCurso input] subform, or somewhere else? You will have less trouble with your code if you simplify it by avoiding the logn references to Forms!blabla.

So, I still can't be totally clear, but I would do it more like this...

 Dim i As Integer
 Dim PagNo As Integer
 Dim SQL As String
 Dim Count As Integer
 Dim NewDate As Date
 Dim DatePlus As Date

 ' Set Variables
 PagNo = 1
 Count = 0
 i = Me.InglesImprest
 DatePlus = CLng(Me.DataVenc)

 For PagNo = 1 To i   'Begin the loop

' set the date variables
 NewDate = CLng(Dateadd("m", Count, DatePlus))

'Create the sql statement to insert the records
SQL = "INSERT INTO tbAlunosPag ( AlunoRef, [CursoRef Ing], [CursoRef Esp], DataVenc, Quanto, AnodoCurso, SemestredoCurso, Prestação )" & _
" VALUES ( Formulários![add Aluno 2]![tbAlunosPag subformulárioinaddAluno].Form!AlunoRef, Formulários![add Aluno 2]![fmAlunosCurso input].Form!InglesRef, Formulários![add Aluno 2]![fmAlunosCurso input].Form!EspanholRef, " & NewDate & ", Formulários![add Aluno 2]![tbAlunosPag subformulárioinaddAluno].Form!Quanto, Formulários![add Aluno 2]![tbAlunosPag subformulárioinaddAluno].Form!AnodoCurso, Formulários![add Aluno 2]![tbAlunosPag subformulárioinaddAluno].Form!SemestredoCurso ," & PagNo & " );"

'Run the SQL statement
 CurrentDb.Execute SQL, dbFailOnError
 Count = Count + 1

Next PagNo

--
Steve Schapel, Microsoft Access MVP


Jeff wrote: 
Steve, Help Me!

I have a code to add a record but increasing the date by one month.

Problem:

date in formfield = 01/jan/2006
Result: 01/jan/2006    02/jan/2006    03/jan/2006

date in formfield = 27/jan/2006
Result: 27/jan/2006    27/feb/2006    27/mar/2006

The field in the table is set as datatype date with no formating
The field in the form is format dd mmmm yyyy
Field in subform is format dd mmmm yyyy

I've tried setting fields in table and forms to dd mmm yyyy and then tried mmm dd yyyy and then tried no format. Same problem!

The global settings are for Brazil and I have this problem.
When the global settings are for Engish (United States) the whole thing works perfectly.

Question: how do I get it to work using the Portuguese (Brazil) global settings?

The Code:


Dim i As Integer
Dim PagNo As Integer
Dim SQL As String
Dim Count As Integer
Dim NewDate As Date
Dim DatePlus As Date



' Set Variables
PagNo = 1
Count = 0
i = Me.InglesImprest



For PagNo = 1 To i   'Begin the loop

 If PagNo > 1 Then       'Turn off warning after first execution
 DoCmd.SetWarnings False
 Else
 DoCmd.SetWarnings True
 End If

' set the date variables

DatePlus = CLng(Dateadd("m", Count, Me.DataVenc))

'Create the sql statement to insert the records
SQL = "INSERT INTO tbAlunosPag ( AlunoRef, [CursoRef Ing], [CursoRef Esp], DataVenc, Quanto, AnodoCurso, SemestredoCurso, Prestação )" & _
"Values (Formulários![add Aluno 2]![tbAlunosPag subformulárioinaddAluno].Form!AlunoRef, Formulários![add Aluno 2]![fmAlunosCurso input].Form!InglesRef, Formulários![add Aluno 2]![fmAlunosCurso input].Form!EspanholRef, " & "#" & DatePlus & "#" & ", Formulários![add Aluno 2]![tbAlunosPag subformulárioinaddAluno].Form!Quanto, Formulários![add Aluno 2]![tbAlunosPag subformulárioinaddAluno].Form!AnodoCurso, Formulários![add Aluno 2]![tbAlunosPag subformulárioinaddAluno].Form!SemestredoCurso ," & PagNo & ");"

'Run the SQL statement
DoCmd.RunSQL SQL
DoCmd.SetWarnings True   'Restore warnings
Count = Count + 1

Next

At least I've found out what the problem is - I think I have, anyway. Every time I think I have, I find out it doesn't work! Frustrating.

Jeff 
.

Relevant Pages

  • Re: Setting field properties in code
    ... format that the user has defined in the Windows Control Panel, ... >> Sub StandardProperties(strTableName As String) ... >> Dim tdf As DAO.TableDef 'Table nominated in argument. ... >> Dim ind As DAO.Index ...
    (comp.databases.ms-access)
  • Re: Conditional format problem
    ... an Excel 5.0/95 format file, ... Anyone know how to save in a later Excel format from Access? ... Dim oXL As Object ... Dim strCondition1 As String ...
    (microsoft.public.excel.programming)
  • Re: Mail Merge HTML Format Word 2002 XP SP3
    ... HTML format as you have no control over how the recipient reads them. ... 'Creates a new e-mail item and modifies its properties. ... Dim olApp As Outlook.Application ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Generating a column based on import file name
    ... is d/m/yy as opposed to the format shown. ... Dim lReturn As Long ... .TextFilePlatform = xlWindows ... importing. ...
    (microsoft.public.excel.programming)
  • Re: tab in userform locks program
    ... > I am a VBA newbie and have not been using DIM in my code so it is ... declare a variable at the top of a standard module using Private (module ... You may notice that I used Format$ instead of Format. ... but Format$ returns a String whereas ...
    (microsoft.public.excel.programming)