Intro

New to the forum, advocate about access to make my job easier

Intro

Finding records in sub-forms

Hi,

I am working on MS Access 2010 and have come across a bit complex situation. I have a table with about 20 fields. In a main form, I have added 4 sub-forms where each sub-form is showing 5 fields of each record from the table. This is because of step by step data entry into the same record. I have made these sub-forms as “Continuous Forms” and have given them look of Data Sheet.

There are about 1000 records currently present in the table and these are showing in my sub-forms (5 fields each). Now the problem is that, when I have my Focus on one record in my first sub-form to enter some data, and then I click on second tab of sub-form, the same records should be shown in second sub-form as well. Currently it is not being happening and I have to scroll down my second sub-form to search that particular record.It will become much more difficult when number of records will increase.


Can anyone guide me how can I work it around and get my cursor on the same record on every sub-form? Your help shall be highly appreciated.

Regards

Finding records in sub-forms

Text box value base on ListBox

Hello All

I have 2 form ( Form A & Form B )
Form A contend Contain ListBox
Form B contend Contain bound Text box

I need to know how when double click ListBox at Form A passing value of listbox.Column(0) to bound Text box at Form B

Please at expiration builder if can

Thanks ...

Text box value base on ListBox

Costum Sort order in Subform

Hi

i have a subform with year and month
subform shows result of a select query ,
when i open query seperatly it show data as requred order but when i show data in subform releted to this query , data show in rearrange order

how can i set order in subform as query reslut

Costum Sort order in Subform

HELP! AddWorkingDays Function

Hi All,

I'm wondering if you could possible help me with my AddWorkingDays function. The issue is that for some reason Access is not recognising 04/05/2015 in my bank holidays table. I'm guessing it is something to do with format of the date, but i have so far come up short with any potential solution.

The function that i am using currently looks like this:

Public Function AddWorkingDays(StartDate As Date, numworkdays As Integer) As Date
'................................................. ...................
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim EndDate As Date
Dim workdayscount As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM TBL_BankHolidays2 WHERE [HolidayDate] > Date()", dbOpenSnapshot)
workdayscount = 0
EndDate = StartDate
Do While workdayscount < numworkdays
EndDate = EndDate + 1
rst.FindFirst "[HolidayDate] = #" & EndDate & "#"
If rst.NoMatch And Weekday(EndDate) <> vbSunday And Weekday(EndDate) <> vbSaturday Then
workdayscount = workdayscount + 1
End If
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
AddWorkingDays = EndDate
End Function

There is then another function that calls this procedure to determine the amount of days added depending on what catergory is selected on the form. A snippet of the code is below:

Select Case frm![Fuel]

Case 2
Select Case frm![Category]

Case 1

numdays = 0
targetdt = AddWorkingDays(Date, numdays)
frm![SLA Date for First Visit] = targetdt

numdays = 5
targetdt = AddWorkingDays(Date, numdays)
frm![SLA Date for Completion] = targetdt

Case 2

numdays = 5
targetdt = AddWorkingDays(Date, numdays)
frm![SLA Date for First Visit] = targetdt

numdays = 15
targetdt = AddWorkingDays(Date, numdays)
frm![SLA Date for Completion] = targetdt

If i was to use this today and case 2 was selected then the 'Date for First Visit' should be set to 06/05/2015 but for some reason it is set to 05/05/2015.

What confuses me even more is that if i create an imaginary holiday for 29/04/2015 then the 'Date for First Visit' is set 06/05/2015 which is correct as it is 5 working days (excluding today).

Again, if i create an imaginary holiday in the table for 14/05/2015 then it sets the 'SLA Date for Completion' to 20/05/2015, which is indeed 15 working days.

I'm probably doing or missing something stupid, so any help would be much appreicated.

Many thanks,
Phil

HELP! AddWorkingDays Function

VBA managing USER's Rights/Access

Hello everyone,

Here I am again seeking for your expert's advice.
In my database there are three roles namely
1. Accountant
2. Bookkeeper
3. Cashier

I have a Main Page Menu form with three buttons, specifically for the above mentioned roles.

My code goes like this:

Code:

---

Private Sub Form_Load()

    txtUser.Value = strUser
    txtRole.Value = strRole

    txtFocus.SetFocus

    'Set Enabled state of command button based on User Role
   
    If strRole = "Accountant" Then
   
        cmdMenu1.Enabled = True
        cmdMenu2.Enabled = True
        cmdMenu3.Enabled = True
       
    ElseIf strRole = "Bookkepeer" Then
   
        cmdMenu1.Enabled = False
        cmdMenu2.Enabled = True
        cmdMenu3.Enabled = False
       
    ElseIf strRole = "Cashier" Then
   
      cmdMenu1.Enabled = False
      cmdMenu2.Enabled = False
      cmdMenu3.Enabled = True
  End If
 
 
End Sub

---

Unfortunately Nothing Happens, meaning each roles can still access all three buttons.
But if My code is like this:

Code:

---

Private Sub Form_Load()

    txtUser.Value = strUser
    txtRole.Value = strRole

    txtFocus.SetFocus

    'Set Enabled state of command button based on User Role
   
    If strRole = "Accountant" Then
   
        cmdMenu1.Enabled = True
    Else
        cmdMenu1.Enabled = False
 
  End If
 
 
End Sub

---

Then its working, for bookkeeper and the accountant.
may I know where did I go wrong?
For what I only want is to set a super user for the accountant, meaning having an access rights to all 3 cmdMenu buttons and setting the bookkeeper rights only at cmdMenu2 and Cashier at cmdMenu3.

Looking again forward to here from you.

Thank you and best regards.

Cheers!
Chino

VBA managing USER's Rights/Access

Hello World!

The age of lurking comes to an end!

I opened a year and a half ago an aquabiking center (Velozeo.com/France), and after a year of looking for the right software to run this place, and after finally ignoring the "access is too hard" opinion, I finally looked into this access thingy 4 months ago.

And it is amazing what we can do with this.

Sooo long story short, I found alot of help in these forums, and as I dwelve deeper and deeper in the access hole, I need to start exchanging with you guys! My database is getting big.

Thanks in advance to all of you and se you around!

Jarod

Hello World!