Help needed please

[gear]

So far I haven’t got help on my DB.
I am attaching my DB.
The DB has two tables: DiaryTable & RBCaseTable
I want to resolve two issues on this DB

First issue: The DyNo field will start from No.1 on new year (DMax function). Since there will be duplicate records with the same DyNo for every year, I want to filter the records year-wise. For the current year, I should be able to do data entry, edit, search, print etc. For the previous year’s records, no data entry should be allowed and only search and print to be allowed.

Second issue: Two fields in both the tables are same i.e. DyNo and Remarks. I want this to be linked. What I want to do is that after I open ‘frmRBCaseAdd’ when I type the DyNo, the form should show the ‘Remarks’ field of DiaryTable so that I could update/edit.

I know nothing about VBA coding etc. Whatever codes I put in this DB was obtained from this forum and I simply made changes to suit my requirements. However, I failed to get help on these two issues. I shall be grateful if you could kindly help me. Thanks in advance.

 

[Premy]

Well, I think for issue nr. 1 you should make a query (underlying your form) that takes year as parameter, and then do an If year = Year(Date()) to check if the year (entered in the textbox txtYear on your form) is the current year or not. If it is, set the forms AllowAdditions property to True, otherwise to false:

1. Put Year(Date()) as the default value for txtYear on your form
2. As for the query, suppose your date field in the DiaryTable is called DateDiary, then in the column for DateDiary, on the Field-line put:
theYear: Year(DateDiary)
On the criteria line of the query you put:
Forms!frmDiary!txtYear

When you load your form the query will look for the value of txtYear and only return the entries that match that year.

3. Attach this code to txtYear_Afterupdate Event:

Sub txtYear_Afterupdate()
If me.txtYear = Year(Date()) Then
Me.AllowAdditions = True
Else
Me.AllowAdditions = False
End if
Me.Requery
End Sub

This will requery the form based on the new value of txtYear. For printing purposes I suppose you have created a Report; you may use the same query for the report.

As for the second issue you could put the 2 forms together as Main and subform, linking Child and Parent fields through the common ID of both Forms.
You could also create a separate Pop-up form for frmRBCaseAdd. Then you put a btn on the main form with the following:

DoCmd.OpenForm "frmRBCaseAdd", , ,"RemarksID=" & Me.txtDyNo, , acDialog

Obviously "RemarksID" is the name of the Id field underlying frmRBCaseAdd and Me.txtDyno a textBox showing the current ID of the DiaryTable. Clicking the button on the 1st form will open the 2nd as a pop-up showing only the record that matches txtDyNo.

Hope this helps

 

[gear]

Thank you for the guidance. I accomplished it as per your suggestions and it worked. I could do Data Entry, Edit and Print now. However, I face difficulty in searching.

I have devised a form (sample taken from this forum attached) where any word or number can be searched in multiple fields. I have put a text box 'txtYear' on the form. The criteria is as under :

SELECT tblAllDet.DyNo, tblAllDet.DocFileNo AS [Doc or File No], [Subject] & ", " & [LinkedFiles] AS [Subject and LF], [Sender] & ", " & [Remarks] AS [Sender and Remarks], tblAllDet.DateRecd FROM tblAllDet WHERE Year=Forms!frmAdvancedSearch!txtYear AND (((tblAllDet.DocFileNo) Like "*" & Forms!frmAdvancedSearch!txtSearch2 & "*")) Or ((([Subject] & ", " & [LinkedFiles]) Like "*" & Forms!frmAdvancedSearch!txtSearch2 & "*")) Or ((([Sender] & "," & [Remarks]) Like "*" & Forms!frmAdvancedSearch!txtSearch2 & "*")) Or (((tblAllDet.SDyNo) Like "*" & Forms!frmAdvancedSearch!txtSearch2 & "*")) ORDER BY tblAllDet.DocFileNo;

This code is not working and list shows all year's records. How do I make the list to display only records with the typed word and pertaining to the year selected in 'txtYear'? Grateful for help.

 

[Premy]

Ok let's fix the Year issue first. When you want to use a given year as criterium you must extract that year from a date field. I suspect that in this case tblAllDet.DateRecd is the date field? If so you must do:

WHERE Year([DateRecd])=Forms!frmAdvancedSearch!txtYear And ...

Good luck,

Premy

 

[RuralGuy]

http://www.access-programmers.co.uk/forums/showthread.php?t=85042