Access 2007 Qs. Don't need help fast!

Factual Questions
1

Okay,

I’m playing around with Access trying to produce a db which will allow me to track and report on several things. I’ve muddled through most of my problems by utilizing another db which does almost exactly what I want. The problem is a few things I want to do that I don’t know how to do. I am a beginner, but have some idea of how things work. I’m hoping someone here can either help me out, or direct me to a god resource.

I have three forms each with it’s own table (Main_Form, FormA and FormB). I have two buttons on MainForm that open into FormA and FormB. When I use the button to open FormA, I want the form to open to the recordset (the recordset is the primary key) that MainForm was at. (I want the same thing for FormB). I don’t seem to be hitting the right combination of things to do that.
Any suggestions?

I have a lot of time/date fields. I want to be able to click into the field and have the time entered. Then I don’t want it to change unless I type something in (in other words, if I accidentally click in the field again, I don’t want it to update). If possible, I’d love to have the time in those boxes restricted to between 8am and 5pm, but that’s not required.

Thanks for any help you can give!

2

This might be the best answer, or the worst! It depends!

If your FormA and FormB are small, you might consider making them sub-forms on the Main form. Linking them is easy and the forms “synch” as records change on the Main form.

3

Church.

4

I thought about that, but MainForm is a pretty full page and I don’t really want to go to subforms. Thanks though.

Heh. That might be my best option at this point!

5

Assuming your data/time is shown in a textbox, you could do something like the following:



Private Sub Text0_Click()
    If Text0.Text = "" Then
        Text0.Text = Now()
    End If
End Sub

The time restriction is more interesting; do you mean you want to have the time restricted to between 8am and 5pm no matter when you click in the box?

6

I haven’t had a chance to try that code yet, but I was able to get my buttons to work like I wanted. Now, when I click on command59 button, I get the form on the same record the MainForm was on.


Private Sub command59_Click()

On Error GoTo Err_command59_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FormA"
    RunCommand acCmdSaveRecord
    stLinkCriteria = "[Record ID]=" & Me![Record ID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_command59_Click:
    Exit Sub

Err_command59_Click:
    MsgBox Err.Description
    Resume Exit_command59_Click
End Sub

It’s probably making some real db people out there bang their head on a desk and mumble to themselves about how stupid that guy whatami is… but I snagged it from another db and made it work.

Now to get to my time fields. And yes Dervorin, I really do want to try to get the date to weekdays between 8 and 5. I know it can be done… I just don’t know how. It may be a bit over my head at this point.

Thanks!

7

Thanks Dervorin, that’s what I needed to get started. I’ll go back some other time and work on restricting it to business hours and days. I know the db I’m using as a demo does it, but I need to dissect through all the field names and such.

8

Something like this, perhaps?



Private Sub Text0_Click()
    
    Dim hourInt As Integer
    Dim result As String

    ' Get the current hour and convert it to an integer
    hourInt = CInt(DatePart("h", Now))
    
    ' Get the date part of the result (adding on a space at the end)
    result = Format(Now, "mmm dd yyyy") + " "
    
    ' If the hour value falls outside our range, limit it to permitted values...
    If hourInt < 8 Then
        result = result + "08:00:00"
    ElseIf hourInt > 17 Then
        result = result + "17:00:00"
    
    ' Inside range, don't change it
    Else
        result = Format(Now, "mmm dd yyyy h:nn:ss")
    End If
    
    ' Put the result into the text box
    Text0.Text = result
     
End Sub

That should at least get you started, even if it’s not quite everything you need.