If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
date field
O.K. Last time...
the Name of my table is "MainSchedule" the name in the main table for the date is simply "Date" the form in use is called "MainScheduleFrm" that is connected to a query called "MainSchedule Query" the date field is always the same name. Thanks Again "Klatuu" wrote: Yes, all the names have to be names you use. Since I don't know what names you use, I can only offer examples. txtPrimaryKeyField should be the control on your form where you have the primary key for the record bound. PrimaryKeyField should be the name of the field in your table that is bound to txtPrimaryKeyField on your form. "sandrao" wrote: You are probably going to tired of my questions I did as you said but it still not working so, should some of the code be altered to include names in my form. e.g. should the 'Me.txtPrimaryKeyField' be the name of my date field or is there anything else that has to change co comply with my form I am just new at all this and really appreciate your help Thanks "Klatuu" wrote: Okay, I know what the problem is. I should know better. What is happening is that when you enter a new record, it is in the form's recordset, but is not yet in the table. So, here is what we need to do. Use the form's After Insert event to requery the form which will immediately update the table with the new record. We will also have to make sure we stay on the current record, so here is an example of how to do this. Private Sub Form_AfterInsert() Dim rst As Recordset strKey = Me.txtPrimaryKeyField Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[KeyField] = '" & Me.txtKeyControl & "'" Me.Bookmark = rst.Bookmark Set rst = Nothing End Sub "sandrao" wrote: I tested out the code and it works In my case it put 17-dec-06 in the date field, however when I went to and an a new record it did not add 7 to the previous date which would have been 24-dec-06. What I need is at each new record it will continue to add 7 the the previous or the last date entered in the date field. Is that Possible? "Klatuu" wrote: There must be a syntax error in what you coded. To break it down: =DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable")) [ScheduleDate] should actually be the name of the date field in the table. ScheduleTable should be the name of the table. The DMax function will return the hightest date in the table. The DateAdd function will add one week to that date. You can test your syntax in the VBA immediate window by typing in the above formula with the correct field and table name. Just replace the = with ? so it will print in the window. Once you get a correct return, you have the syntax correct and can paste it into the Default Value property of the date field with the = I did create a form and test it, so it does work. "sandrao" wrote: I tried the suggested example and I got an #Error message. You understood my question. Yes each new record should appear one week after the previous entry. Tlhe date field name is txDate and the form is MainScheduleFrm. I am not sure what I did wrong but the suggestion did not work. Do you have any other advise Thanks "Klatuu" wrote: If what you are saying is you want each new record's date to be one week after the previous records date, you can use the Default Value property of the date field control. Since I don't know the names of your objects, the following example uses made up names. Change them to use your names: =DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable")) "sandrao" wrote: How can I have adate field in a form automatically filled with dates. I have a scheduling program that schedules events on a weekly basis. The schedule starts on the first day of the week (vbSunday). I would like to have the date appear each time a new record is started. The first week would be 15-Oct-06 then when those events are filled in the new record would already have the date 22-Oct-06. These dates would continue on appearing each time a new record was started. |
#12
|
|||
|
|||
date field
Using Date as a field name, a control name, or any other name in Access is a
bad idea. It is a reserved word. No reserved words should be used as names. You can usually get around it by putting it in brackets, but that is just a band-aid. I suggest you scout MSDN for some Access or VBA naming convention information. I have modified that is use your name. Private Sub Form_AfterInsert() Dim rst As Recordset strKey = Me.[Date] Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[Date] = '" & Me.[Date] & "'" Me.Bookmark = rst.Bookmark Set rst = Nothing End Sub "sandrao" wrote: O.K. Last time... the Name of my table is "MainSchedule" the name in the main table for the date is simply "Date" the form in use is called "MainScheduleFrm" that is connected to a query called "MainSchedule Query" the date field is always the same name. Thanks Again "Klatuu" wrote: Yes, all the names have to be names you use. Since I don't know what names you use, I can only offer examples. txtPrimaryKeyField should be the control on your form where you have the primary key for the record bound. PrimaryKeyField should be the name of the field in your table that is bound to txtPrimaryKeyField on your form. "sandrao" wrote: You are probably going to tired of my questions I did as you said but it still not working so, should some of the code be altered to include names in my form. e.g. should the 'Me.txtPrimaryKeyField' be the name of my date field or is there anything else that has to change co comply with my form I am just new at all this and really appreciate your help Thanks "Klatuu" wrote: Okay, I know what the problem is. I should know better. What is happening is that when you enter a new record, it is in the form's recordset, but is not yet in the table. So, here is what we need to do. Use the form's After Insert event to requery the form which will immediately update the table with the new record. We will also have to make sure we stay on the current record, so here is an example of how to do this. Private Sub Form_AfterInsert() Dim rst As Recordset strKey = Me.txtPrimaryKeyField Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[KeyField] = '" & Me.txtKeyControl & "'" Me.Bookmark = rst.Bookmark Set rst = Nothing End Sub "sandrao" wrote: I tested out the code and it works In my case it put 17-dec-06 in the date field, however when I went to and an a new record it did not add 7 to the previous date which would have been 24-dec-06. What I need is at each new record it will continue to add 7 the the previous or the last date entered in the date field. Is that Possible? "Klatuu" wrote: There must be a syntax error in what you coded. To break it down: =DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable")) [ScheduleDate] should actually be the name of the date field in the table. ScheduleTable should be the name of the table. The DMax function will return the hightest date in the table. The DateAdd function will add one week to that date. You can test your syntax in the VBA immediate window by typing in the above formula with the correct field and table name. Just replace the = with ? so it will print in the window. Once you get a correct return, you have the syntax correct and can paste it into the Default Value property of the date field with the = I did create a form and test it, so it does work. "sandrao" wrote: I tried the suggested example and I got an #Error message. You understood my question. Yes each new record should appear one week after the previous entry. Tlhe date field name is txDate and the form is MainScheduleFrm. I am not sure what I did wrong but the suggestion did not work. Do you have any other advise Thanks "Klatuu" wrote: If what you are saying is you want each new record's date to be one week after the previous records date, you can use the Default Value property of the date field control. Since I don't know the names of your objects, the following example uses made up names. Change them to use your names: =DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable")) "sandrao" wrote: How can I have adate field in a form automatically filled with dates. I have a scheduling program that schedules events on a weekly basis. The schedule starts on the first day of the week (vbSunday). I would like to have the date appear each time a new record is started. The first week would be 15-Oct-06 then when those events are filled in the new record would already have the date 22-Oct-06. These dates would continue on appearing each time a new record was started. |
#13
|
|||
|
|||
date field
Error message reads after highlighting 'strKey= 'Compile Error Can't find
project or library' then the date disappears fron the field. sorry it muse be me "Klatuu" wrote: Using Date as a field name, a control name, or any other name in Access is a bad idea. It is a reserved word. No reserved words should be used as names. You can usually get around it by putting it in brackets, but that is just a band-aid. I suggest you scout MSDN for some Access or VBA naming convention information. I have modified that is use your name. Private Sub Form_AfterInsert() Dim rst As Recordset strKey = Me.[Date] Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[Date] = '" & Me.[Date] & "'" Me.Bookmark = rst.Bookmark Set rst = Nothing End Sub "sandrao" wrote: O.K. Last time... the Name of my table is "MainSchedule" the name in the main table for the date is simply "Date" the form in use is called "MainScheduleFrm" that is connected to a query called "MainSchedule Query" the date field is always the same name. Thanks Again "Klatuu" wrote: Yes, all the names have to be names you use. Since I don't know what names you use, I can only offer examples. txtPrimaryKeyField should be the control on your form where you have the primary key for the record bound. PrimaryKeyField should be the name of the field in your table that is bound to txtPrimaryKeyField on your form. "sandrao" wrote: You are probably going to tired of my questions I did as you said but it still not working so, should some of the code be altered to include names in my form. e.g. should the 'Me.txtPrimaryKeyField' be the name of my date field or is there anything else that has to change co comply with my form I am just new at all this and really appreciate your help Thanks "Klatuu" wrote: Okay, I know what the problem is. I should know better. What is happening is that when you enter a new record, it is in the form's recordset, but is not yet in the table. So, here is what we need to do. Use the form's After Insert event to requery the form which will immediately update the table with the new record. We will also have to make sure we stay on the current record, so here is an example of how to do this. Private Sub Form_AfterInsert() Dim rst As Recordset strKey = Me.txtPrimaryKeyField Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[KeyField] = '" & Me.txtKeyControl & "'" Me.Bookmark = rst.Bookmark Set rst = Nothing End Sub "sandrao" wrote: I tested out the code and it works In my case it put 17-dec-06 in the date field, however when I went to and an a new record it did not add 7 to the previous date which would have been 24-dec-06. What I need is at each new record it will continue to add 7 the the previous or the last date entered in the date field. Is that Possible? "Klatuu" wrote: There must be a syntax error in what you coded. To break it down: =DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable")) [ScheduleDate] should actually be the name of the date field in the table. ScheduleTable should be the name of the table. The DMax function will return the hightest date in the table. The DateAdd function will add one week to that date. You can test your syntax in the VBA immediate window by typing in the above formula with the correct field and table name. Just replace the = with ? so it will print in the window. Once you get a correct return, you have the syntax correct and can paste it into the Default Value property of the date field with the = I did create a form and test it, so it does work. "sandrao" wrote: I tried the suggested example and I got an #Error message. You understood my question. Yes each new record should appear one week after the previous entry. Tlhe date field name is txDate and the form is MainScheduleFrm. I am not sure what I did wrong but the suggestion did not work. Do you have any other advise Thanks "Klatuu" wrote: If what you are saying is you want each new record's date to be one week after the previous records date, you can use the Default Value property of the date field control. Since I don't know the names of your objects, the following example uses made up names. Change them to use your names: =DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable")) "sandrao" wrote: How can I have adate field in a form automatically filled with dates. I have a scheduling program that schedules events on a weekly basis. The schedule starts on the first day of the week (vbSunday). I would like to have the date appear each time a new record is started. The first week would be 15-Oct-06 then when those events are filled in the new record would already have the date 22-Oct-06. These dates would continue on appearing each time a new record was started. |
#14
|
|||
|
|||
date field
I just realized I wrote this incorrectly.
rst.FindFirst "[Date] = '" & Me.[Date] & "'" It should be rst.FindFirst "[Date] = #" & Me.[Date] & "#" If that doesn't fix it, the error you are getting indicates a missing or broken reference. "sandrao" wrote: Error message reads after highlighting 'strKey= 'Compile Error Can't find project or library' then the date disappears fron the field. sorry it muse be me "Klatuu" wrote: Using Date as a field name, a control name, or any other name in Access is a bad idea. It is a reserved word. No reserved words should be used as names. You can usually get around it by putting it in brackets, but that is just a band-aid. I suggest you scout MSDN for some Access or VBA naming convention information. I have modified that is use your name. Private Sub Form_AfterInsert() Dim rst As Recordset strKey = Me.[Date] Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[Date] = '" & Me.[Date] & "'" Me.Bookmark = rst.Bookmark Set rst = Nothing End Sub "sandrao" wrote: O.K. Last time... the Name of my table is "MainSchedule" the name in the main table for the date is simply "Date" the form in use is called "MainScheduleFrm" that is connected to a query called "MainSchedule Query" the date field is always the same name. Thanks Again "Klatuu" wrote: Yes, all the names have to be names you use. Since I don't know what names you use, I can only offer examples. txtPrimaryKeyField should be the control on your form where you have the primary key for the record bound. PrimaryKeyField should be the name of the field in your table that is bound to txtPrimaryKeyField on your form. "sandrao" wrote: You are probably going to tired of my questions I did as you said but it still not working so, should some of the code be altered to include names in my form. e.g. should the 'Me.txtPrimaryKeyField' be the name of my date field or is there anything else that has to change co comply with my form I am just new at all this and really appreciate your help Thanks "Klatuu" wrote: Okay, I know what the problem is. I should know better. What is happening is that when you enter a new record, it is in the form's recordset, but is not yet in the table. So, here is what we need to do. Use the form's After Insert event to requery the form which will immediately update the table with the new record. We will also have to make sure we stay on the current record, so here is an example of how to do this. Private Sub Form_AfterInsert() Dim rst As Recordset strKey = Me.txtPrimaryKeyField Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[KeyField] = '" & Me.txtKeyControl & "'" Me.Bookmark = rst.Bookmark Set rst = Nothing End Sub "sandrao" wrote: I tested out the code and it works In my case it put 17-dec-06 in the date field, however when I went to and an a new record it did not add 7 to the previous date which would have been 24-dec-06. What I need is at each new record it will continue to add 7 the the previous or the last date entered in the date field. Is that Possible? "Klatuu" wrote: There must be a syntax error in what you coded. To break it down: =DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable")) [ScheduleDate] should actually be the name of the date field in the table. ScheduleTable should be the name of the table. The DMax function will return the hightest date in the table. The DateAdd function will add one week to that date. You can test your syntax in the VBA immediate window by typing in the above formula with the correct field and table name. Just replace the = with ? so it will print in the window. Once you get a correct return, you have the syntax correct and can paste it into the Default Value property of the date field with the = I did create a form and test it, so it does work. "sandrao" wrote: I tried the suggested example and I got an #Error message. You understood my question. Yes each new record should appear one week after the previous entry. Tlhe date field name is txDate and the form is MainScheduleFrm. I am not sure what I did wrong but the suggestion did not work. Do you have any other advise Thanks "Klatuu" wrote: If what you are saying is you want each new record's date to be one week after the previous records date, you can use the Default Value property of the date field control. Since I don't know the names of your objects, the following example uses made up names. Change them to use your names: =DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable")) "sandrao" wrote: How can I have adate field in a form automatically filled with dates. I have a scheduling program that schedules events on a weekly basis. The schedule starts on the first day of the week (vbSunday). I would like to have the date appear each time a new record is started. The first week would be 15-Oct-06 then when those events are filled in the new record would already have the date 22-Oct-06. These dates would continue on appearing each time a new record was started. |
#15
|
|||
|
|||
date field
It seems you have a reference problem. This is not you, it could be a VBA
problem. Open your VBA editor. Click on Tools, References. Look for any references that say Missing. If none say missing, then there is one that needs to be selected, but is not. Post back with what you have checked. "sandrao" wrote: Sorry I guess I am a lost cause I still get the error message No need to reply Thank for all your help and time spent sandrao "Klatuu" wrote: I just realized I wrote this incorrectly. rst.FindFirst "[Date] = '" & Me.[Date] & "'" It should be rst.FindFirst "[Date] = #" & Me.[Date] & "#" If that doesn't fix it, the error you are getting indicates a missing or broken reference. "sandrao" wrote: Error message reads after highlighting 'strKey= 'Compile Error Can't find project or library' then the date disappears fron the field. sorry it muse be me "Klatuu" wrote: Using Date as a field name, a control name, or any other name in Access is a bad idea. It is a reserved word. No reserved words should be used as names. You can usually get around it by putting it in brackets, but that is just a band-aid. I suggest you scout MSDN for some Access or VBA naming convention information. I have modified that is use your name. Private Sub Form_AfterInsert() Dim rst As Recordset strKey = Me.[Date] Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[Date] = '" & Me.[Date] & "'" Me.Bookmark = rst.Bookmark Set rst = Nothing End Sub "sandrao" wrote: O.K. Last time... the Name of my table is "MainSchedule" the name in the main table for the date is simply "Date" the form in use is called "MainScheduleFrm" that is connected to a query called "MainSchedule Query" the date field is always the same name. Thanks Again "Klatuu" wrote: Yes, all the names have to be names you use. Since I don't know what names you use, I can only offer examples. txtPrimaryKeyField should be the control on your form where you have the primary key for the record bound. PrimaryKeyField should be the name of the field in your table that is bound to txtPrimaryKeyField on your form. "sandrao" wrote: You are probably going to tired of my questions I did as you said but it still not working so, should some of the code be altered to include names in my form. e.g. should the 'Me.txtPrimaryKeyField' be the name of my date field or is there anything else that has to change co comply with my form I am just new at all this and really appreciate your help Thanks "Klatuu" wrote: Okay, I know what the problem is. I should know better. What is happening is that when you enter a new record, it is in the form's recordset, but is not yet in the table. So, here is what we need to do. Use the form's After Insert event to requery the form which will immediately update the table with the new record. We will also have to make sure we stay on the current record, so here is an example of how to do this. Private Sub Form_AfterInsert() Dim rst As Recordset strKey = Me.txtPrimaryKeyField Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[KeyField] = '" & Me.txtKeyControl & "'" Me.Bookmark = rst.Bookmark Set rst = Nothing End Sub "sandrao" wrote: I tested out the code and it works In my case it put 17-dec-06 in the date field, however when I went to and an a new record it did not add 7 to the previous date which would have been 24-dec-06. What I need is at each new record it will continue to add 7 the the previous or the last date entered in the date field. Is that Possible? "Klatuu" wrote: There must be a syntax error in what you coded. To break it down: =DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable")) [ScheduleDate] should actually be the name of the date field in the table. ScheduleTable should be the name of the table. The DMax function will return the hightest date in the table. The DateAdd function will add one week to that date. You can test your syntax in the VBA immediate window by typing in the above formula with the correct field and table name. Just replace the = with ? so it will print in the window. Once you get a correct return, you have the syntax correct and can paste it into the Default Value property of the date field with the = I did create a form and test it, so it does work. "sandrao" wrote: I tried the suggested example and I got an #Error message. You understood my question. Yes each new record should appear one week after the previous entry. Tlhe date field name is txDate and the form is MainScheduleFrm. I am not sure what I did wrong but the suggestion did not work. Do you have any other advise Thanks "Klatuu" wrote: If what you are saying is you want each new record's date to be one week after the previous records date, you can use the Default Value property of the date field control. Since I don't know the names of your objects, the following example uses made up names. Change them to use your names: =DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable")) "sandrao" wrote: How can I have adate field in a form automatically filled with dates. I have a scheduling program that schedules events on a weekly basis. The schedule starts on the first day of the week (vbSunday). I would like to have the date appear each time a new record is started. The first week would be 15-Oct-06 then when those events are filled in the new record would already have the date 22-Oct-06. These dates would continue on appearing each time a new record was started. |
#16
|
|||
|
|||
date field
Sorry I guess I am a lost cause I still get the error message No need to reply
Thank for all your help and time spent sandrao "Klatuu" wrote: I just realized I wrote this incorrectly. rst.FindFirst "[Date] = '" & Me.[Date] & "'" It should be rst.FindFirst "[Date] = #" & Me.[Date] & "#" If that doesn't fix it, the error you are getting indicates a missing or broken reference. "sandrao" wrote: Error message reads after highlighting 'strKey= 'Compile Error Can't find project or library' then the date disappears fron the field. sorry it muse be me "Klatuu" wrote: Using Date as a field name, a control name, or any other name in Access is a bad idea. It is a reserved word. No reserved words should be used as names. You can usually get around it by putting it in brackets, but that is just a band-aid. I suggest you scout MSDN for some Access or VBA naming convention information. I have modified that is use your name. Private Sub Form_AfterInsert() Dim rst As Recordset strKey = Me.[Date] Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[Date] = '" & Me.[Date] & "'" Me.Bookmark = rst.Bookmark Set rst = Nothing End Sub "sandrao" wrote: O.K. Last time... the Name of my table is "MainSchedule" the name in the main table for the date is simply "Date" the form in use is called "MainScheduleFrm" that is connected to a query called "MainSchedule Query" the date field is always the same name. Thanks Again "Klatuu" wrote: Yes, all the names have to be names you use. Since I don't know what names you use, I can only offer examples. txtPrimaryKeyField should be the control on your form where you have the primary key for the record bound. PrimaryKeyField should be the name of the field in your table that is bound to txtPrimaryKeyField on your form. "sandrao" wrote: You are probably going to tired of my questions I did as you said but it still not working so, should some of the code be altered to include names in my form. e.g. should the 'Me.txtPrimaryKeyField' be the name of my date field or is there anything else that has to change co comply with my form I am just new at all this and really appreciate your help Thanks "Klatuu" wrote: Okay, I know what the problem is. I should know better. What is happening is that when you enter a new record, it is in the form's recordset, but is not yet in the table. So, here is what we need to do. Use the form's After Insert event to requery the form which will immediately update the table with the new record. We will also have to make sure we stay on the current record, so here is an example of how to do this. Private Sub Form_AfterInsert() Dim rst As Recordset strKey = Me.txtPrimaryKeyField Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[KeyField] = '" & Me.txtKeyControl & "'" Me.Bookmark = rst.Bookmark Set rst = Nothing End Sub "sandrao" wrote: I tested out the code and it works In my case it put 17-dec-06 in the date field, however when I went to and an a new record it did not add 7 to the previous date which would have been 24-dec-06. What I need is at each new record it will continue to add 7 the the previous or the last date entered in the date field. Is that Possible? "Klatuu" wrote: There must be a syntax error in what you coded. To break it down: =DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable")) [ScheduleDate] should actually be the name of the date field in the table. ScheduleTable should be the name of the table. The DMax function will return the hightest date in the table. The DateAdd function will add one week to that date. You can test your syntax in the VBA immediate window by typing in the above formula with the correct field and table name. Just replace the = with ? so it will print in the window. Once you get a correct return, you have the syntax correct and can paste it into the Default Value property of the date field with the = I did create a form and test it, so it does work. "sandrao" wrote: I tried the suggested example and I got an #Error message. You understood my question. Yes each new record should appear one week after the previous entry. Tlhe date field name is txDate and the form is MainScheduleFrm. I am not sure what I did wrong but the suggestion did not work. Do you have any other advise Thanks "Klatuu" wrote: If what you are saying is you want each new record's date to be one week after the previous records date, you can use the Default Value property of the date field control. Since I don't know the names of your objects, the following example uses made up names. Change them to use your names: =DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable")) "sandrao" wrote: How can I have adate field in a form automatically filled with dates. I have a scheduling program that schedules events on a weekly basis. The schedule starts on the first day of the week (vbSunday). I would like to have the date appear each time a new record is started. The first week would be 15-Oct-06 then when those events are filled in the new record would already have the date 22-Oct-06. These dates would continue on appearing each time a new record was started. |
#17
|
|||
|
|||
date field
Dim rst As Recordset
strKey = Me.txtScheduleDate Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[txtScheduleDate] = #" & Me.[txtScheduleDate] & "#" Me.Bookmark = rst.Bookmark Set rst = Nothing I changed my Date fields name in the table to the above the date seems to be going into the table but I still get the error related to 'strKey = ' thought you would like to know I think we are on the right tract Thought you would like to know "Klatuu" wrote: I just realized I wrote this incorrectly. rst.FindFirst "[Date] = '" & Me.[Date] & "'" It should be rst.FindFirst "[Date] = #" & Me.[Date] & "#" If that doesn't fix it, the error you are getting indicates a missing or broken reference. "sandrao" wrote: Error message reads after highlighting 'strKey= 'Compile Error Can't find project or library' then the date disappears fron the field. sorry it muse be me "Klatuu" wrote: Using Date as a field name, a control name, or any other name in Access is a bad idea. It is a reserved word. No reserved words should be used as names. You can usually get around it by putting it in brackets, but that is just a band-aid. I suggest you scout MSDN for some Access or VBA naming convention information. I have modified that is use your name. Private Sub Form_AfterInsert() Dim rst As Recordset strKey = Me.[Date] Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[Date] = '" & Me.[Date] & "'" Me.Bookmark = rst.Bookmark Set rst = Nothing End Sub "sandrao" wrote: O.K. Last time... the Name of my table is "MainSchedule" the name in the main table for the date is simply "Date" the form in use is called "MainScheduleFrm" that is connected to a query called "MainSchedule Query" the date field is always the same name. Thanks Again "Klatuu" wrote: Yes, all the names have to be names you use. Since I don't know what names you use, I can only offer examples. txtPrimaryKeyField should be the control on your form where you have the primary key for the record bound. PrimaryKeyField should be the name of the field in your table that is bound to txtPrimaryKeyField on your form. "sandrao" wrote: You are probably going to tired of my questions I did as you said but it still not working so, should some of the code be altered to include names in my form. e.g. should the 'Me.txtPrimaryKeyField' be the name of my date field or is there anything else that has to change co comply with my form I am just new at all this and really appreciate your help Thanks "Klatuu" wrote: Okay, I know what the problem is. I should know better. What is happening is that when you enter a new record, it is in the form's recordset, but is not yet in the table. So, here is what we need to do. Use the form's After Insert event to requery the form which will immediately update the table with the new record. We will also have to make sure we stay on the current record, so here is an example of how to do this. Private Sub Form_AfterInsert() Dim rst As Recordset strKey = Me.txtPrimaryKeyField Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[KeyField] = '" & Me.txtKeyControl & "'" Me.Bookmark = rst.Bookmark Set rst = Nothing End Sub "sandrao" wrote: I tested out the code and it works In my case it put 17-dec-06 in the date field, however when I went to and an a new record it did not add 7 to the previous date which would have been 24-dec-06. What I need is at each new record it will continue to add 7 the the previous or the last date entered in the date field. Is that Possible? "Klatuu" wrote: There must be a syntax error in what you coded. To break it down: =DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable")) [ScheduleDate] should actually be the name of the date field in the table. ScheduleTable should be the name of the table. The DMax function will return the hightest date in the table. The DateAdd function will add one week to that date. You can test your syntax in the VBA immediate window by typing in the above formula with the correct field and table name. Just replace the = with ? so it will print in the window. Once you get a correct return, you have the syntax correct and can paste it into the Default Value property of the date field with the = I did create a form and test it, so it does work. "sandrao" wrote: I tried the suggested example and I got an #Error message. You understood my question. Yes each new record should appear one week after the previous entry. Tlhe date field name is txDate and the form is MainScheduleFrm. I am not sure what I did wrong but the suggestion did not work. Do you have any other advise Thanks "Klatuu" wrote: If what you are saying is you want each new record's date to be one week after the previous records date, you can use the Default Value property of the date field control. Since I don't know the names of your objects, the following example uses made up names. Change them to use your names: =DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable")) "sandrao" wrote: How can I have adate field in a form automatically filled with dates. I have a scheduling program that schedules events on a weekly basis. The schedule starts on the first day of the week (vbSunday). I would like to have the date appear each time a new record is started. The first week would be 15-Oct-06 then when those events are filled in the new record would already have the date 22-Oct-06. These dates would continue on appearing each time a new record was started. |
#18
|
|||
|
|||
date field
I just noticed there is an error in the code. It is my fault, I wrote it
wrong. The way it is, it will not go to be current record, but back to the first record. Here is the correction: Dim rst As Recordset Dim strKey As String strKey = Me.txtScheduleDate Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[txtScheduleDate] = #" & strKey & "#" Me.Bookmark = rst.Bookmark Set rst = Nothing Notice I added an Dim statement for strKey. If it doesn't work as a string, change it to Dim strKey as Date Not a Lost cause at all, we are almost there! "sandrao" wrote: Dim rst As Recordset strKey = Me.txtScheduleDate Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[txtScheduleDate] = #" & Me.[txtScheduleDate] & "#" Me.Bookmark = rst.Bookmark Set rst = Nothing I changed my Date fields name in the table to the above the date seems to be going into the table but I still get the error related to 'strKey = ' thought you would like to know I think we are on the right tract Thought you would like to know "Klatuu" wrote: I just realized I wrote this incorrectly. rst.FindFirst "[Date] = '" & Me.[Date] & "'" It should be rst.FindFirst "[Date] = #" & Me.[Date] & "#" If that doesn't fix it, the error you are getting indicates a missing or broken reference. "sandrao" wrote: Error message reads after highlighting 'strKey= 'Compile Error Can't find project or library' then the date disappears fron the field. sorry it muse be me "Klatuu" wrote: Using Date as a field name, a control name, or any other name in Access is a bad idea. It is a reserved word. No reserved words should be used as names. You can usually get around it by putting it in brackets, but that is just a band-aid. I suggest you scout MSDN for some Access or VBA naming convention information. I have modified that is use your name. Private Sub Form_AfterInsert() Dim rst As Recordset strKey = Me.[Date] Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[Date] = '" & Me.[Date] & "'" Me.Bookmark = rst.Bookmark Set rst = Nothing End Sub "sandrao" wrote: O.K. Last time... the Name of my table is "MainSchedule" the name in the main table for the date is simply "Date" the form in use is called "MainScheduleFrm" that is connected to a query called "MainSchedule Query" the date field is always the same name. Thanks Again "Klatuu" wrote: Yes, all the names have to be names you use. Since I don't know what names you use, I can only offer examples. txtPrimaryKeyField should be the control on your form where you have the primary key for the record bound. PrimaryKeyField should be the name of the field in your table that is bound to txtPrimaryKeyField on your form. "sandrao" wrote: You are probably going to tired of my questions I did as you said but it still not working so, should some of the code be altered to include names in my form. e.g. should the 'Me.txtPrimaryKeyField' be the name of my date field or is there anything else that has to change co comply with my form I am just new at all this and really appreciate your help Thanks "Klatuu" wrote: Okay, I know what the problem is. I should know better. What is happening is that when you enter a new record, it is in the form's recordset, but is not yet in the table. So, here is what we need to do. Use the form's After Insert event to requery the form which will immediately update the table with the new record. We will also have to make sure we stay on the current record, so here is an example of how to do this. Private Sub Form_AfterInsert() Dim rst As Recordset strKey = Me.txtPrimaryKeyField Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[KeyField] = '" & Me.txtKeyControl & "'" Me.Bookmark = rst.Bookmark Set rst = Nothing End Sub "sandrao" wrote: I tested out the code and it works In my case it put 17-dec-06 in the date field, however when I went to and an a new record it did not add 7 to the previous date which would have been 24-dec-06. What I need is at each new record it will continue to add 7 the the previous or the last date entered in the date field. Is that Possible? "Klatuu" wrote: There must be a syntax error in what you coded. To break it down: =DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable")) [ScheduleDate] should actually be the name of the date field in the table. ScheduleTable should be the name of the table. The DMax function will return the hightest date in the table. The DateAdd function will add one week to that date. You can test your syntax in the VBA immediate window by typing in the above formula with the correct field and table name. Just replace the = with ? so it will print in the window. Once you get a correct return, you have the syntax correct and can paste it into the Default Value property of the date field with the = I did create a form and test it, so it does work. "sandrao" wrote: I tried the suggested example and I got an #Error message. You understood my question. Yes each new record should appear one week after the previous entry. Tlhe date field name is txDate and the form is MainScheduleFrm. I am not sure what I did wrong but the suggestion did not work. Do you have any other advise Thanks "Klatuu" wrote: If what you are saying is you want each new record's date to be one week after the previous records date, you can use the Default Value property of the date field control. Since I don't know the names of your objects, the following example uses made up names. Change them to use your names: =DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable")) "sandrao" wrote: How can I have adate field in a form automatically filled with dates. I have a scheduling program that schedules events on a weekly basis. The schedule starts on the first day of the week (vbSunday). I would like to have the date appear each time a new record is started. The first week would be 15-Oct-06 then when those events are filled in the new record would already have the date 22-Oct-06. These dates would continue on appearing each time a new record was started. |
#19
|
|||
|
|||
date field
Final note.
I decided to ignore the recordset seeing that it caused me to many problems instead I entered this simple code: Private Sub Form_AfterInsert() Me.txtScheduleDate.Requery End Sub I ran the code severals times and the table is being updated thought you would like to know Thanks again for all your help The information you gave me for the default in the date field works like a charm sandrao "sandrao" wrote: Sorry I guess I am a lost cause I still get the error message No need to reply Thank for all your help and time spent sandrao "Klatuu" wrote: I just realized I wrote this incorrectly. rst.FindFirst "[Date] = '" & Me.[Date] & "'" It should be rst.FindFirst "[Date] = #" & Me.[Date] & "#" If that doesn't fix it, the error you are getting indicates a missing or broken reference. "sandrao" wrote: Error message reads after highlighting 'strKey= 'Compile Error Can't find project or library' then the date disappears fron the field. sorry it muse be me "Klatuu" wrote: Using Date as a field name, a control name, or any other name in Access is a bad idea. It is a reserved word. No reserved words should be used as names. You can usually get around it by putting it in brackets, but that is just a band-aid. I suggest you scout MSDN for some Access or VBA naming convention information. I have modified that is use your name. Private Sub Form_AfterInsert() Dim rst As Recordset strKey = Me.[Date] Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[Date] = '" & Me.[Date] & "'" Me.Bookmark = rst.Bookmark Set rst = Nothing End Sub "sandrao" wrote: O.K. Last time... the Name of my table is "MainSchedule" the name in the main table for the date is simply "Date" the form in use is called "MainScheduleFrm" that is connected to a query called "MainSchedule Query" the date field is always the same name. Thanks Again "Klatuu" wrote: Yes, all the names have to be names you use. Since I don't know what names you use, I can only offer examples. txtPrimaryKeyField should be the control on your form where you have the primary key for the record bound. PrimaryKeyField should be the name of the field in your table that is bound to txtPrimaryKeyField on your form. "sandrao" wrote: You are probably going to tired of my questions I did as you said but it still not working so, should some of the code be altered to include names in my form. e.g. should the 'Me.txtPrimaryKeyField' be the name of my date field or is there anything else that has to change co comply with my form I am just new at all this and really appreciate your help Thanks "Klatuu" wrote: Okay, I know what the problem is. I should know better. What is happening is that when you enter a new record, it is in the form's recordset, but is not yet in the table. So, here is what we need to do. Use the form's After Insert event to requery the form which will immediately update the table with the new record. We will also have to make sure we stay on the current record, so here is an example of how to do this. Private Sub Form_AfterInsert() Dim rst As Recordset strKey = Me.txtPrimaryKeyField Me.Requery Set rst = Me.RecordsetClone rst.FindFirst "[KeyField] = '" & Me.txtKeyControl & "'" Me.Bookmark = rst.Bookmark Set rst = Nothing End Sub "sandrao" wrote: I tested out the code and it works In my case it put 17-dec-06 in the date field, however when I went to and an a new record it did not add 7 to the previous date which would have been 24-dec-06. What I need is at each new record it will continue to add 7 the the previous or the last date entered in the date field. Is that Possible? "Klatuu" wrote: There must be a syntax error in what you coded. To break it down: =DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable")) [ScheduleDate] should actually be the name of the date field in the table. ScheduleTable should be the name of the table. The DMax function will return the hightest date in the table. The DateAdd function will add one week to that date. You can test your syntax in the VBA immediate window by typing in the above formula with the correct field and table name. Just replace the = with ? so it will print in the window. Once you get a correct return, you have the syntax correct and can paste it into the Default Value property of the date field with the = I did create a form and test it, so it does work. "sandrao" wrote: I tried the suggested example and I got an #Error message. You understood my question. Yes each new record should appear one week after the previous entry. Tlhe date field name is txDate and the form is MainScheduleFrm. I am not sure what I did wrong but the suggestion did not work. Do you have any other advise Thanks "Klatuu" wrote: If what you are saying is you want each new record's date to be one week after the previous records date, you can use the Default Value property of the date field control. Since I don't know the names of your objects, the following example uses made up names. Change them to use your names: =DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable")) "sandrao" wrote: How can I have adate field in a form automatically filled with dates. I have a scheduling program that schedules events on a weekly basis. The schedule starts on the first day of the week (vbSunday). I would like to have the date appear each time a new record is started. The first week would be 15-Oct-06 then when those events are filled in the new record would already have the date 22-Oct-06. These dates would continue on appearing each time a new record was started. |
|
Thread Tools | |
Display Modes | |
|
|