473,320 Members | 1,810 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Automatic creation of records

17
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
Apr 6 '09 #1
32 1997
ChipR
1,287 Expert 1GB
Run some code like this when you want add the new record:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as String
  2. strSQL = "INSERT INTO destinationTable _
  3. (field1, field2, etc...) _
  4. VALUES (" & value1 & ", " & value2 & ", " & etc... ", #" & [Inspection Date] + 90 & "#, " & probably more values & ")"
  5. DoCmd.SetWarnings False
  6. DoCmd.RunSQL strSQL, 0
  7. 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.
Apr 6 '09 #2
DonRayner
489 Expert 256MB
If you want to add three months you need to change

Expand|Select|Wrap|Line Numbers
  1. [Inspection Date] + 90
to
Expand|Select|Wrap|Line Numbers
  1. DateAdd("n",3,[Inspection Date])
Apr 6 '09 #3
NeoPa
32,556 Expert Mod 16PB
@DonRayner
Or :
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. ... ", #" & 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).
Apr 6 '09 #4
NeoPa
32,556 Expert Mod 16PB
@Chip,
Some nice code, but remember the continuation characters (_) at the end of a line cannot be inside strings.
Wrong :
Expand|Select|Wrap|Line Numbers
  1. str = "ABC _
  2.        DEF"
Right :
Expand|Select|Wrap|Line Numbers
  1. str = "ABC" & _
  2.       "DEF"
Apr 6 '09 #5
DonRayner
489 Expert 256MB
Thanks for catching that NeoPa. It should have been "m" for months, not "n" for minutes.
Apr 6 '09 #6
ChipR
1,287 Expert 1GB
Thank you both for helping with my code. I can only say it was toward the end of the day.
Apr 7 '09 #7
NeoPa
32,556 Expert Mod 16PB
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 :)
Apr 7 '09 #8
Rafik
17
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
Apr 7 '09 #9
NeoPa
32,556 Expert Mod 16PB
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.
Apr 7 '09 #10
Rafik
17
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
Apr 9 '09 #11
ChipR
1,287 Expert 1GB
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
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT " _
  2.   & "INTO " _
  3.   & "destinationTable " _
  4.   ...
Just replace the table, field, and value names with yours, and change my date calculation as NeoPa suggested last.
Apr 9 '09 #12
Rafik
17
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
Apr 9 '09 #13
ChipR
1,287 Expert 1GB
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.
Apr 9 '09 #14
Rafik
17
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub InspectionDone_AfterUpdate()
  2. Dim strSQL As String
  3. strSQL = "INSERT INTO destinationTable _
  4. (field1, field2, field3,  field4, field 5, field 6,) _
  5. VALUES (" & value1 &", " & value2 & "Format(DateAdd("m",3,[Inspection Date]),"dd/mm/yyy" " & value4 &", " & value5 &", " & value6 &" _
  6. DoCmd.SetWarnings False
  7. DoCmd.RunSQL strSQL, 0
  8. DoCmd.SetWarnings True
  9. 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
Apr 9 '09 #15
OldBirdman
675 512MB
I see two different syntax problems in your SQL statement -
1) The line continuation cannot be within quotes, therefore
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO  destinationTable " & _
  2.     "(field1, field2, field3, field4, field 5, field 6,) " & _
  3.     "VALUES . . . . . ."
2) Double quotes (") inside double quotes don't work, as Access thinks the quote has closed. Replace the imbedded double quotes with single quotes ('). The alternate is to use 2 double quotes to signify an embedded double quotes.
Expand|Select|Wrap|Line Numbers
  1. "Format(DateAdd("m",3, ... "
  2. should be either
  3. ]"Format(DateAdd('m',3, ... "
  4. or
  5. ]"Format(DateAdd(""m"",3, ... "
Apr 10 '09 #16
Rafik
17
Hi OldBirdman
Tried changing code as below but still not working
Expand|Select|Wrap|Line Numbers
  1. Private Sub InspectionDone_AfterUpdate()
  2. Dim strSQL As String 
  3. strSQL = "INSERT INTO destinationTable" & _
  4. "(field1, field2, field3,  field4, field5, field6,)" & _
  5. " VALUES (" & value1 &", " & value2 & "Format(DateAdd(""m"",3,[Inspection Date])),"dd/mm/yyy" " & value4 &", " & value5 &", " & value6 &" " & _
  6. DoCmd.SetWarnings False
  7. DoCmd.RunSQL strSQL, 0
  8. DoCmd.SetWarnings True
  9. End Sub
May be doing something wrong.
Let me tell you how and were the code was written.
I opened the form in design view and clicked on the Y/N tab and went into properties and into the codebuilder of the after update event and wrote the code.

Is there something i am doing wrong?

Rafik
Apr 10 '09 #17
OldBirdman
675 512MB
It is still not working??? Not giving me much there to work with.

Without actually trying to create your environment so I can test, this looks like you didn't even follow through with what I said in my first post. You are not looking carefully at your work. You have not removed the "s from within your quoted strings. You would probably find it easier if you used the single quote ' inside the quoted strings. It is 1) easier to read, and 2) easier to code, causing fewer errors.

Quickly, I find the following, but there may be more:

1) There is an extra comma after ~, field6~ ((Note, I'm using ~ as a delimiter because the quotes in your code make it hard to use quotes. Everything between the ~ ~ is your code))

2) ~value2 & "Format(~ needs to have a space before the word ~Format~

3) ~Format(DateAdd(""m"",3,[Inspection Date])),"dd/mm/yyy"~ doesn't have double quotes around the pattern, and the closing paren is in the wrong place

4) ~dd/mm/yyy~ I don't know what ~yyy~ signifies, but it won't work

5) ~value6 &" " & _~ should be implies there is a line missing in your post, or it is just wrong, as the statement should end, or there should be more. Telling Access that there is more code for your strSQL string and then not supplying any more is going to make it "...still not working"
Apr 10 '09 #18
Rafik
17
Sorry Oldbirdman
I am very new to this code businees and copied all this code from Neo and Chip anyways let me put you in the environment i am working in.

I am trying to create a database for the inspection of properties.
I have a created a table and form with the following 6 fields.

InspectionID Index Primary Key
PropertyAddress Address of the property Text
DateOfInspection Date of inspection format dd/mm/yyyy
InspectionDone Y/N tick box
InspectionBy Lookup
Comment Memo

What i am trying to do is initially for each property i will type in the information example in datasheetview
PropertyID PropertyAddress InspectionDate InspectionBy Inspecti
1 Gedding Road 73 08/04/2009 Ash -1
2 King Edward Road 25 06/04/2009 Salim 0
3 Trevino Drive 61 10/04/2009 Ash 0

What i am trying to do is once an inspection is done, and the box INSPECTIONDONE is clicked a new record is created in the next row with the following informatin

InspectionID to change to the next number up
PropertyAddress to stay the same
DateOfInspection to change to current date plus 3 months
InspectionDone to stay the same
InspectionBy to stay the same
Comment to stay blank

I hope you understand and help me sort out the code

Rafik
Apr 10 '09 #19
OldBirdman
675 512MB
If I understand, and were going to do this with just the tables (for simplicity of discussion), I could -
1) Open the table
2) Find the record to update
3) Make the changes in DataSheet View
4) Select the record I just changed by clicking the record selector (left margin)
5) Do Edit->Copy (or cntl+C)
6) Click the little >* at the bottom to go to the new record
7) Do Edit->Paste (or cntl+P)
8) Change the date field to shown date + 3 months

Would that solve your problem? Logically, I mean?
Apr 10 '09 #20
Rafik
17
Oldbirdman
Logically yes as long as the first field which is the ID changes as an index key
and the comment field is left blank, so that on inspection any new comments are typed in.
Also it would have been easier to do what u suggest but in view of the many properties being inspected and various people inputting the data, an update via a click would be more efficient
Rafik
Apr 11 '09 #21
OldBirdman
675 512MB
OK, so I'm right through 8) and so
9) Set comments field to blank
10) Set New Key for new record

Now we are going to start all over. This got messed up with too many details. We have a 10 step plan as a framework.

In your next post, I'd like the name of your table, your form, and the name of the button you want to push.

1) Done - your form is already bound to the table
2) Done - I assume you can position your form on the correct record. If not, you have a lot to learn before you can do this project.
3) Done - You enter the data in the form
4) You have (or should have) a button on your form that says you want the new record created. I hope so. If not, how do you signal the program that the current record is complete? Ok, I assume you have a button, named cmdNewRec (for now)
In the Click event in VBA, you will do all the coding. Any problems with that?
5) We better be sure we have saved the current record, so add the code
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdNewRec_Click
  2. If Me.Dirty Then Me.Dirty = False 'This will save the current record if any changes
  3. ...
  4. End Sub
6-7-8) Add this code after the ~If Me.Dirty Then ...
Expand|Select|Wrap|Line Numbers
  1.  
  2. 'Select the record now being edited
  3. DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
  4. 'Copy the Record
  5. DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
  6. 'Paste append record to create new record
  7. DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
  8.  
9) We're almost there !!!
Your form will now show the new record, and the fields will all be the same as they were, so some more code on the Click procedure
Expand|Select|Wrap|Line Numbers
  1. txtDateField = DateAdd("m",3,txtDateField)
  2. txtCommentField = ""
  3.  
10) Done if key is an autonum field. If not, then txtKey = whatever you do to make a new key

Good Luck
Apr 11 '09 #22
Rafik
17
Oldbirdman
Thanks for all that
I will give it a try but the only thing is i have no button to create a new record
but my intention was for the record to be created when the YES/NO click box iof the INSPECTIONDONE field is clicked a new record is created.
Would there be any changes to the code.
Rafik
Apr 11 '09 #23
OldBirdman
675 512MB
No changes. Just put the code in the Click event for the chkBox.

It is another problem, but don't forget to consider someone going back to the original (older) record to make more changes, and unchecking your box. Then if they click it again, you have duplicate records.

I won't be on this computer intil Monday or Thursday, so Good Luck!
Apr 11 '09 #24
Rafik
17
Thanks Oldbirdman
It works except the date does not change

Here is the code i put in
Expand|Select|Wrap|Line Numbers
  1. Private Sub InspectionDone_Click()
  2. If Me.Dirty Then Me.Dirty = False 'This will save the current record if any changes
  3. 'Select the record now being edited
  4. DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
  5. 'Copy the Record
  6. DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
  7. 'Paste append record to create new record
  8. DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
  9.  txtDateField = DateAdd("m", 3, txtDateField)
  10. txtCommentField = ""
  11.  
  12.  
  13. End Sub
Sorry to be a pain.
Rafik
Apr 11 '09 #25
Rafik
17
Sorry also the comment box is not blank
Rafik
Apr 11 '09 #26
Rafik
17
Oldbirdman
It copies and pastes the information
but does not change the date and niether does it leave
the comment field blank

Here is the code i put in
Expand|Select|Wrap|Line Numbers
  1. InspectionDone_Click() 
  2. If Me.Dirty Then Me.Dirty = False 'This will save the current record if any changes 
  3. 'Select the record now being edited 
  4. DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 
  5. 'Copy the Record 
  6. DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70 
  7. 'Paste append record to create new record 
  8. DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append 
  9.  txtDateField = DateAdd("m", 3, txtDateField) 
  10. txtCommentField = "" 
  11.  
  12.  
  13. End Sub
Sorry to be a pain.

Rafik
Apr 16 '09 #27
NeoPa
32,556 Expert Mod 16PB
I don't know if you believe in Déja vue, but I'm sure I've had to edit your posts before for not using the [ CODE ] tags.

These tags are mandatory, and I'd appreciate if you remembered to use them for all future posts.
Apr 16 '09 #28
OldBirdman
675 512MB
I am having your same problem, but I was trying something slightly differently. This is discussed in http://bytes.com/topic/access/answer...-new-row-table
I need some time to figure out why ALL fields not copied, and why code I supplied not doing the job. Whether I can solve this for you is an open question.
Apr 16 '09 #29
Rafik
17
Oldbirdman,ChipR,Donrainer,NeoPa
Any new ideas on the old question of Creating new record in a table
Rafik
May 1 '09 #30
NeoPa
32,556 Expert Mod 16PB
I can't say how new this will be, but if we go back to first principles, we would have to consider triggering this otherwise than a CheckBox. That is, unless you intend to find and delete the record when the CheckBox is cleared. Alternatively, if it is a one-way only checking, the control should be disabled whenever a true value has been saved to file.

The other point to ponder :
If CODE is the key (I assume PK) then how is it possible to add a new record with nothing changed but the date?

If CODE is an AutoNumber key, then what identifies a record? What is a unique reference? How can we check whether a matching record for three months hence is already there or not?
May 2 '09 #31
Rafik
17
Sorry NeoPa
Got a bit lost there
We seem to have solved the problem of copying and pastiong a record on the click of a tick box INSPECTION DONE Y/N. The problem is we need to somehow change the code to take account of the new date when inspection is due and make the COMMENT field blank.
The code for copy paste is
Expand|Select|Wrap|Line Numbers
  1. Private Sub InspectionDone_Click() 
  2. If Me.Dirty Then Me.Dirty = False 'This will save the current record if any changes 
  3. 'Select the record now being edited 
  4. DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 
  5. 'Copy the Record 
  6. DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70 
  7. 'Paste append record to create new record 
  8. DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append 
  9.  txtDateField = DateAdd("m", 3, txtDateField) 
  10. txtCommentField = "" 
  11.  
  12. End Sub
Hope this makes sense
Rafik
May 3 '09 #32
NeoPa
32,556 Expert Mod 16PB
The trouble with your solution is that it doesn't solve your problems though.

From my perspective, this is because you haven't properly identified the problem to start with. It makes sense to me that you're still looking for new solutions (you asked for this in post #30).

I can only help you if you're prepared to listen to what I have to say. I cannot proceed from your solution, as I am not convinced it's a solution.

That would be like asking me to provide water with a bucket that has holes in it. I can see the holes all too easily. I'm not going to go to the well to fill it up, even though I'd like to help as I can see that it would be no help at all.
May 5 '09 #33

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Ido | last post by:
Hello, I am searching for an automatic forms creation tool that will help me to save time in developin forms (preferrably with validation). My forms get some data from a MySQL database and are...
7
by: svilen | last post by:
hello again. i'm now into using python instead of another language(s) for describing structures of data, including names, structure, type-checks, conversions, value-validations, metadata etc....
4
by: Alistair | last post by:
IIS, Access 2000 I have a page that tabulates data from a DB, this data is items for sale, private ads. is there a way that at the same time it can automatically delete entries that are...
1
by: girlkordic | last post by:
I have a linked table from an Oracle database (that contains HR information for my company)in an Access database. The Oracle DB is updated on a daily basis. I would like one of my tables in...
13
by: Stuart McGraw | last post by:
I haven't been able to figure this out and would appreciate some help... I have two tables, both with autonumber primary keys, and linked in a conventional master-child relationship. I've...
1
by: Earl Anderson | last post by:
Using WinXP & AccessXP, I'd like to automatically have the records that I've just printed in a report automatically updated to have the 'print date & time' immediately inserted into a 'Date/Time'...
58
by: Jorge Peixoto de Morais Neto | last post by:
I was reading the code of FFmpeg and it seems that they use malloc just too much. The problems and dangers of malloc are widely known. Malloc also has some overhead (although I don't know what is...
2
by: =?Utf-8?B?UmV5bm9sZHNVc2Vy?= | last post by:
Is there a way from within a C# app to create a printer? I am looking to automate the creation of (possibly) dozens of printers, to prevent having to go through the "Add Printer Wizard" so many...
0
by: cephal0n | last post by:
Hi All! I have two table tblHome1, contains all the unique PinNo and tblHome2 contains a duplicated PinNo and description. I put an automatic numbering (ProdID) and set it as my index. What I want...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.