Automatic creation of records

[Rafik]

I seem to have solved the problem of pop up reminder but still need some help with automatically creating records once a tick box is clicked.

My form is as follows
CODE Key
PROPERTY Address
INSPECTION DATE Date
INSPECTION DONE Yes/No tick box
INSPECTION BY Lookup
COMMENTS Memo

Inspections are done quarterley
Once an inspection is done i need a new record to be added on with all the details as in the current record but the date to change to 3 months into the future.

Think need to do this using VB but have very little knowledge of VB. How would i go about it and what is the code.

Any help will be very much appreciated.
Rafik

[ChipR]

Run some code like this when you want add the new record:

Dim strSQL as String
strSQL = "INSERT INTO destinationTable _
(field1, field2, etc...) _
VALUES (" & value1 & ", " & value2 & ", " & [I]etc... [/I]", #" & [Inspection Date] + 90 & "#, " & [I]probably more values[/I] & ")"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL, 0
DoCmd.SetWarnings True

This just adds 90 days to the date, which may or may not be the same as 3 months, depending on how you are thinking about it.

[DonRayner]

If you want to add three months you need to change

[Inspection Date] + 90

to

DateAdd("n",3,[Inspection Date])

[NeoPa]

DateAdd("n",3,[Inspection Date])

Or :

DateAdd("m",3,[Inspection Date])

for months instead of minutes :D Sorry Don. Couldn't resist (I'm sure it was just a typo really).

It really ought to be :

... ", #" & Format(DateAdd("m",3,Me.[Inspection Date]),"m/d/yyyy") & "#," ...

Just displaying the date in the standard local format is not reliable for various potential reasons (See Literal DateTimes and Their Delimiters (#) for details).

[NeoPa]

@Chip,
Some nice code, but remember the continuation characters (_) at the end of a line cannot be inside strings.
Wrong :

str = "ABC _
       DEF"

Right :

str = "ABC" & _
      "DEF"

[DonRayner]

Thanks for catching that NeoPa. It should have been "m" for months, not "n" for minutes.

[ChipR]

Thank you both for helping with my code. I can only say it was toward the end of the day.

[NeoPa]

No excuses required Chip. The amount of work you're doing is a real help, and if like us, you manage to learn a few extra tricks while you're at it, then all the better :)

[Rafik]

Thanks for all that, but it seems to complicated for me as i have no knowledge of VB.
Will have to learn VB to solve this problem.
Also may i ask u if it is possible to run a macro to solve the above problem?
And if so how would one go about it
Thanks
Rafik

[NeoPa]

It may be possible with a macro but I wouldn't know. That way appears easier, but I find it more trouble in the long run. I doubt you could without the macro being fairly complicated anyway. That would rather be missing the point I feel.

What you can do is try applying the code as already worked out for you, and asking any extra questions you need to about how to get it in your particular database. I don't think you'll need to learn too much about VBA if you don't want to now, as the code is already prepared for you. A simple copy/paste should do.

Does that sound like it may be a workable solution.

[Rafik]

Thanks Neo
I will try using the code, but please tell me where to stick the code in, is it in access ? On the form ? Through the code builder ?
Also do i copy the code exactly as given?
Sorry to be such a pain but as i am very new to this code business all help will be very much appreciated.
Rafik

[ChipR]

This depends on how you want the running of the code to be initiated. One common option is when a command button on a form is clicked. If you make a button and go to the Properties Event tab, and select the Code Builder for the On Click event, it will create a function for you and you can put the code there.
You can copy and paste the code, but then you'll need to fix it as pointed out. You can put the strSQL = statement all on one line or you can break it up like

strSQL = "INSERT " _
  & "INTO " _
  & "destinationTable " _
  ...

Just replace the table, field, and value names with yours, and change my date calculation as NeoPa suggested last.

[Rafik]

Thanks for that Chip
I want the running of the code to be initiated when the Y/N box is ticked.

My form is as follows
CODE Key
PROPERTY Address
INSPECTION DATE Date
INSPECTION DONE Yes/No tick box
INSPECTION BY Lookup
COMMENTS Memo

Once an inspection has been done say on 01/04/2009 the field
INSPECTION DONE will be ticked and at that point i want a new record to be created with all the same information with the exception of the date which will be ()+3m.

Thanks again
Rafik

[ChipR]

In that case, you can put the code in the AfterUpdate event of the "Inspection Done" check box, but you have to consider whether you allow users to uncheck and/or recheck the box.

[Rafik]

Chip,Neo
I am struggling to make this work,can you please guide me step by step as to how to put the code in and if the code is correct.

My form looks like this in datasheet view

PropertyID PropertyAddress InspectionDate InspectionBy InspectionDon
1 Gedding Road 73 08/04/2009 Ash 0
2 King Edward Road 25 06/04/2009 Salim 0
3 Trevino Drive 61 10/04/2009 Ash 0


The code i have typed in is as follows

Private Sub InspectionDone_AfterUpdate()
Dim strSQL As String
strSQL = "INSERT INTO destinationTable _
(field1, field2, field3,  field4, field 5, field 6,) _
VALUES (" & value1 &", " & value2 & "Format(DateAdd("m",3,[Inspection Date]),"dd/mm/yyy" " & value4 &", " & value5 &", " & value6 &" _
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL, 0
DoCmd.SetWarnings True
End Sub

The first line comes in yellow
And lines 3,4,5 and 6 come up in red.
And a box comes up with syntax error when i try to click the box.

Hope you can help me solve this

Rafik