Create multiple new records using a start and end date on a form

[rhabdo]

I am trying to use a from to enter new data into a table. Instead of entering one record at a time I would like to enter a start and end date into the form which then checks the table to see if these dates exist, if they do they should be displayed, if not new records should be created and displayed so that data can be entered. My table is simple [ID] [Datadate] and a [data] column. I am not sure where to start with this, any advice appreciated.

[June7]

Need code behind a button. I use only VBA.

Have unbound textboxes on form to enter dates to search for. Then something like.

If IsNull(DLookup("ID", "tablename", "Datadate BETWEEN #" & Me.startdatetextbox & "# AND #" & Me.enddatetextbox & "#") Then
dteDate = InputBox("Enter Date")
For i = 1 to number of records to add
CurrentDb.Execute "INSERT INTO tablename(Datadate) VALUES(#" & dteDate & "#)"
Next
End If

code here to open form filtered to the desired records
DoCmd.OpenForm "formname", , , "[Data] Is Null"

or code to filter same form the search boxes are on
Me.Requery
Me.FilterOn = False
Me.Filter = "[Data] Is Null"
Me.FilterOn = True

[rhabdo]

Thanks June7,

This looks like what i am trying to do, but there is a syntax error on the line

For i = 1 to number of records to add

and i am not sure what it should read?

I have attached the example database.

Thanks

[June7]

Need to put a number in place of 'number of records to add'.

I don't know how you want to determine that number. Will it always be the same?

[rhabdo]

it would be the start date minus the end date

Will this bring up existing records to view and add any missing records?
ie. if there are records for 1-15 feb and you enter the dates as 1 to 28 feb will it add the dates 16-28 Feb?

it is also giving me a runtime error on the syntax 'Datadate BETWEEN #start# AND #End#' if i put a value into the line for i =

[June7]

My previously suggested code will enter so many records with the same date. If you want records that increment the date, try:

If IsNull(DLookup("ID", "tablename", "Datadate BETWEEN #" & Me.startdatetextbox & "# AND #" & Me.enddatetextbox & "#") Then
For i = 0 To Me.enddatetextbox - Me.startdatetextbox
CurrentDb.Execute "INSERT INTO tablename(Datadate) VALUES(#" & Me.startdatetextbox + i & "#)"
Next
End If

Concatenate variables into the WHERE CONDITION as shown in suggested syntax.

[rhabdo]

Thanks that's great, only problem is if some records already exist in the table which are the same as the start date, new records are not created for dates between the start and end dates which are not already in the table. ie it wont skip the dates already there and add new ones until reaching the end date.

anyway around this?

[June7]

Are the existing records all sequential by date, no skipped dates up to the last record? You want the append to pickup from that point and continue adding records? Or are you filling in gaps? The latter will be trickier.

[rhabdo]

In theory there should be no skipped dates, each site should have sequential dates with data associated. If for example I had data in the table for 1-15 January and I specify start and end dates of 1 and 30 January respectively then i would like all records from 1-30 Jan to be displayed on the form for the specified site, the first 15 would have data and the next 15 would be new records and blank for the user to enter data.

Alternatively it may be easier and safer to do it by year and month rather, the user selects the year, month and site and the forms displays a full months worth of records which display any current data and adds new records if required for that site and month. Would this be easier? It would cut down in missing dates during entry if the user skips some dates between session on the DB.

[June7]

Site is criteria that must also be considered?

Doesn't matter how the range is specified, if must check db for existence of each date/site before allowing new record, try:

Dim dteDate As Date
Dim i As Integer, intDays As Integer
intDays = CDate(Me.startdatetextbox) - CDate(Me.enddatetextbox)
For i = 1 to intDays
If IsNull(DLookup("ID", "tablename", "Datadate = #" & dteDate & "# AND SiteID=" & Me.SiteID)) Then
CurrentDb.Execute "INSERT INTO tablename(Datadate, SiteID) VALUES(#" & dteDate & "#, " & Me.SiteID & ")"
dteDate = dteDate + 1
End If
Next

[rhabdo]

Hi June7,

Sorry I am getting nothing now, the code wont enter any data into the table, not sure what i have done and i cant see where the problem is compared to the last code.........

[June7]

Sorry, I have the date subtraction backwards. DOH! Change code to: EndDate - StartDate. I also forgot to set the dteDate variable before entering the loop. Need to reset dteDate variable outside the If Then. SiteID is text field so need apostrophe delimiters. And I just realized need to add 1 to the calculated date difference. Working code:

Dim dteDate As Date
Dim i As Integer, intDays As Integer
intDays = CDate(Me.TxtEnd) - CDate(Me.txtStart) + 1
dteDate = CDate(Me.txtStart)
For i = 1 To intDays
If IsNull(DLookup("ID", "MyTable", "Datadate = #" & dteDate & "# AND SiteID='" & Me.SiteID & "'")) Then
    CurrentDb.Execute "INSERT INTO MyTable(Datadate, SiteID) VALUES(#" & dteDate & "#, " & Me.SiteID & ")"
End If
dteDate = dteDate + 1
Next
Me.Requery

Debug techniques could help reveal these errors in code. Review link at bottom of my post. Why don't you apply those techniques to the bad code as learning exercise before changing.

[rhabdo]

Thanks for this, its exactly what I was looking for, only one thing, is it possible to change the SiteID to STRING rather than a VALUE.

Thanks for the advice re:debugging, i do use it but can't always work out what is wrong with the syntax. Sorry i am very new to this and am trying to learn as i go.

Thanks for the great help

[June7]

Don't understand. SiteID is a string (text) data type.

I did forget to use apostrophe delimiters in the INSERT but it still worked.

[rhabdo]

It works fine if i enter a number as a siteID, but if i enter text it does not run, i thought his may have to do with the VALUE entry