Assigning new values to blank fields

[Ben_Entrew]

Hi all,

I got one months table containing a reporting_month ,timeperiod and an Index column ID with data type Autonumber.

Basically I want to search through the table whenever the User types in a new reporting month or timeperiod over the dialogue.
Now I want to realize the following options:
1. The user types in a new reporting month, when a record in the months table exists with a timeperiod and a blank reporting month field, it should be assigned there. For example the User types in reporting month = 032014 it should be assigned to the Time_Period Value = 042014-032015

2. Vice versa, the user types in a time period. This value shuold be assigned to the blank field beneath the existing reporting month.

Can someone help me here,how can I realize this in Access VBA?

Thanks in advance.
Regards,
Ben

Months Reporting_Month Time_Period ID 102013 112013-102014 1 112013 122013-112014 2 012014 022014-012015 3 022014
4
042014-032015 5

 

[Ben_Entrew]

Sorry guys, the table format looks bad.

 

[Mihail]

Show us the Relationships window (a pic)
Ensure that all fields in the tables are visible.

 

[Ben_Entrew]

Hello Mihail,

I only got a this months table , there are no relations existent.

Rgds,
Ben

 

[Mihail]

Seems that Time_Period is a calculated field:
Time_Period = Reporting_Month + 1 day ------ Reporting_Month + 1 year

Anyway, I think that the best you can do is to upload the DB (the table and the associated form). Use Access 2003 version

 

[Ben_Entrew]

Hi,
the timeperiod and reporting month values are typed in by the user, no calculated fields. At the end it should make sense what the user types in, therefore I have to write a documentation about this for the users.

Unfortunately I can't upload the database, cause we only use Access 2010 here.

What I tried so far is this for the reporting month part. Somehow I have to expand the Monthsets.AddNew part by a If condition to check wheter a timeperiod record exists with a blank reporting month or not :

Public Sub IMPORT_Click()
Dim filelocation As Variant
Dim f As Object
Dim Message As String, Title As String, Default As String
Dim sql As String
Dim MsgReply As Integer
Dim Monthsets As DAO.Recordset
'Ask User for Reporting month. Set prompt:
Message = "Enter a reporting month (mmyyyy)"

'Set title:
Title = "Reporting Month"

'Set default:
Default = " "

'Display dialog box at position 5000, 5000.
repmonth = InputBox(Message, Title, Default, 5000, 5000)


If DCount("Reporting_Month", "months", "Reporting_Month = '" & repmonth & "'") > 0 Then

MsgReply = MsgBox("This reporting month already exists. Do you want to overwrite the data?", vbYesNo)

Select Case MsgReply

    Case vbYes
    CurrentDb.Execute "Delete FROM Months Where Reporting_Month = '" & repmonth & "'", dbFailOnError
    Me.Combo1.Requery
    Case vbNo


End Select

Else
MsgBox ("Yes it's indeed new")
Set Monthsets = CurrentDb.OpenRecordset("Months")
If Months.Forecast_Time_Period Is Null Then
Set Months.Forecast_Time_Period = repmonth
Else


Monthsets.AddNew
Monthsets("Reporting_Month").Value = repmonth
Monthsets.Update
Me.Combo1.Requery
End If

End Sub

 

[Mihail]

Unfortunately I can't upload the database, cause we only use Access 2010 here.

Go to Office button and Save As... Access 2002-2003

 

[Ben_Entrew]

Here you go Mihail,

the database is attached.

 

[Mihail]

I write this post in order that anyone to know that I can't help you, so, if someone else can...
Really I don't understand your request.
Sorry.

 

[pr2-eugin]

I write this post in order that anyone to know that I can't help you, so, if someone else can...
Really I don't understand your request.
Sorry.

I gave up few weeks ago !

 

[Ben_Entrew]

Sorry guys, that I have confused you.
Over the weekend I will rewrite this in a clearer way.

Regards,
Ben

 

[Ben_Entrew]

Hi all,

let me describe my problem again.

I ask the user to type in a reporting month and a timeperiod variable.
There should be no rule what to type in first.

The Syntax should be :

reporting month : mmyyyy

timeperiod : mmyyyy-mmyyyy

The logic behind these variables is:
E.g. reporting month = 092013, then the timeperiod are the following twelve months = 102013-092014.

The Problem I got now is: How can I assign the right reporting months and timeperiod variables together?`

I created a months table containing the columns reporting month and timeperiod. As soon as the user gives one of these variables it's added new on the months table.

Does anyone has an idea how to resolve this ?

Do I have to use the Dateserial command to calculate the variables at my own?

Thanks in advance .

Regards,

Ben

 

[Ben_Entrew]

Hi all,

maybe someone can help me, when I attach a new test database.

In this database I left out the import and error handling.

Thanks in advance.


Regards,
Ben

 

[JHB]

I've made a solution for you, when you type in a reporting month in the combobox, the timeperiod is automatic updated and versa visa.
Take it or leave it, it's your choice!

 

[Ben_Entrew]

Thanks JHB,

this will definitely help me, I will use this.

Thanks.

Regards,
Ben

 

[JHB]

You're welcome, luck with it.