By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,816 Members | 2,151 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,816 IT Pros & Developers. It's quick & easy.

Access form problem

P: 30
Hi. I am relatively new in MS Access. I want to make an entry/edit form to enter the number of hours a person took off for a given day. In the form I have a person search button which I am able to choose the person from a tblPerson table, and a calendar to get a specific date. The table I want to update is tblHoursOff which contain the PersonID, DateOff, ( both are part of the primary key), HoursOff, Reason, Comment fields.

My problem is how to use the PersonID from the person search and the date I got from the calendar to show the record from the tblHoursOff if it is existing or enter a new record if not in it yet.
Oct 25 '06 #1
Share this Question
Share on Google+
14 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
The easiest way is to create a button. Lets call it cmdAddPerson.

Then you need the following code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdAddPerson_OnClick()
  3. Dim pID As Integer
  4.  
  5.     pID = Dlookup("[PersonID]","tblHoursOff","[DateOff]=" & Me.DateOnForm)
  6.  
  7.     If pID = 0 Or IsNull(pID) Then
  8.         strSQL = "INSERT INTO tblHoursOff (PersonID, DateOff, HoursOff, Reason, Comment) " & _
  9.                         "VALUES (" & Me.PersonID, Me.DateOnForm, Me.HoursOff, Me.Reason, Comment & ");"
  10.         DoCmd.RunSQL
  11.     Else
  12.         MsgBox " This is already entered", vbOkOnly
  13.     End If
  14.  
  15. End Sub
  16.  
  17.  
I don't know what the textboxes on your form are called so I just used the field names. Change them as appropriate.
Oct 26 '06 #2

P: 30
Thanks, mmccarthy. I'll try it out and will let you know the result. Thanks again.
Oct 26 '06 #3

P: 30
Hi mmccarthy. Thanks agian. I placed your statements in. It works, but it pops up a window asking for a date. Sorry I didnít make myself clear. Maybe, I should give more detailed specs. I have these tables and forms:

Tables.
tblPeople with the following fields PeopleID - autonum primary key, CheckinID, LastName, FirstName, Super, Type.

tblOffDetail with ChekinID , DateOff (these two make up the primary key), OffHours, OffType, Comments.

Forms:
frmPeople using tblPeople fields and a command button cmdPersonSearch to open another frmSearchPeople form when clicked.

frmSearchPeople. Used in frmPeople to select the specific person by typing the letters of the last name.

frmDateDropDown. Used in frmPeople to select the date using a calendar control. It has a Text2 textbox where the delected date is passed.

sfrmOffDetail is subform in the frmPeople with same fields as the tbleOffDet. This is linked to the main form by the CheckinID. So when I choose a record in the tblPeople records associated with it displays in the sub form.

My problem is how to use the date in Text2 in frmDateDropDown to show only the records for the current person for the selected date in the subform. Hope it is much clearer now. Thanks.
Oct 27 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
re my original code if you change

Me.DateOnForm

to

Me.Text2

it would work

Anyway ...

What you are trying to do is to filter records on a subform. Each subform has a name property as well as its name as a form. You will need to go to the other tab in the property list for the subform to find out what that is. I'm using the form name for now.

use a command button to set the filter.

Me.sfrmOffDetail.Form.Filter = "DateOff=" & Me.frmDateDropDown.Form.Text2
Me.sfrmOffDetail.Form.FilterOn = True
Oct 27 '06 #5

P: 30
Thank you. I am learning a lot.

Here is the script in on click event procedure with names in the Other tab of the forms. However it is telling me that it is giving me the message "You didn't specifity search criteria with a FindRecord Action.

Private Sub cmdGetRecord_Click()
Me.sfrmOffDetail.Form.Filter = "DateOff=" & Me.frmDateDropDown2.Form.Text2
Me.sfrmOffDetail.Form.FilterOn = True
Oct 27 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Is frmDateDropDown2 a subform of frmPeople or a popup form.



Thank you. I am learning a lot.

Here is the script in on click event procedure with names in the Other tab of the forms. However it is telling me that it is giving me the message "You didn't specifity search criteria with a FindRecord Action.

Private Sub cmdGetRecord_Click()
Me.sfrmOffDetail.Form.Filter = "DateOff=" & Me.frmDateDropDown2.Form.Text2
Me.sfrmOffDetail.Form.FilterOn = True
Oct 27 '06 #7

P: 30
Hi.Sorry for the late reply, been side tracked due to end and beginning of month tasks at work.
Anyways, frmDateDropDown2 (source object is frmDateDropDown) form with in frmPeople. It ahs the following objects: cmdCal- command button to show (pop up) the calendar calCtl1 where I choose the date, and and text2 where the date is passed to. So, I frmDateDropDown is subform with a pop calendar. Hope this helps.
Nov 3 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Thank you. I am learning a lot.

Here is the script in on click event procedure with names in the Other tab of the forms. However it is telling me that it is giving me the message "You didn't specifity search criteria with a FindRecord Action.

Private Sub cmdGetRecord_Click()
Me.sfrmOffDetail.Form.Filter = "DateOff=" & Me.frmDateDropDown2.Form.Text2
Me.sfrmOffDetail.Form.FilterOn = True
Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdGetRecord_Click()
  3.   Me.sfrmOffDetail.Form.Filter = "DateOff=" &  Forms![frmPeople]![frmDateDropDown2].Form![Text2]
  4. Me.sfrmOffDetail.Form.FilterOn = True
  5.  
  6.  
Nov 7 '06 #9

P: 30
Hi, I tried it. It is giving me the same error message. Is it possible to send you the access file so you can look at it? Let me know.
Nov 7 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi, I tried it. It is giving me the same error message. Is it possible to send you the access file so you can look at it? Let me know.
In the design view of frmPeople click on the frame of one of the subforms and open the properties window. Under the other tab what is in the Name property. Is it what you expected? Now do the same with the other subform.
Nov 7 '06 #11

P: 30
Hi.

Subform sfrmOffDetail has Property Name sfrmOffDetail

and for subform frmDateDropDown the Property Name is frmDateDropDown2.

What I expect to happened is that in the subfrom sfrmOffDetail the records from tblOffDet table (correction: the for this table is compound key is CheckInID and Dateoff; not PeopleID and DateOff) for the person with CheckInID I got from people search, with date (text2) I got in frmDateDropDown
Nov 8 '06 #12

Expert 5K+
P: 8,434
Try this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdGetRecord_Click()
  2.   Me.sfrmOffDetail.Form.Filter = "DateOff=" &  Forms![frmPeople]![frmDateDropDown2].Form![Text2]
  3. Me.sfrmOffDetail.Form.FilterOn = True
Is it possible that we're simply missing "#" delimiters around the date here? Perhaps this would work...
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdGetRecord_Click()
  2.   Me.sfrmOffDetail.Form.Filter = "DateOff=#" &  Forms![frmPeople]![frmDateDropDown2].Form![Text2] & "#"
  3. Me.sfrmOffDetail.Form.FilterOn = True
Nov 10 '06 #13

NeoPa
Expert Mod 15k+
P: 31,426
I know one can usually add delimiter characters in SQL by including them in the literal strings
Expand|Select|Wrap|Line Numbers
  1. MyField = "WHERE [MyDate]=#" & Format(MyDate,'m/d/yyyy') & "#"
but there are sometimes situations where you want to include the delimiter chars within the string returned from the Format() function. To do this use the escape character '\' before your delimiters.
Expand|Select|Wrap|Line Numbers
  1. MyField = "WHERE [MyDate]=" & Format(MyDate,'\#m/d/yyyy\#')
Nov 10 '06 #14

P: 30
Hey, thank you guys, with the delimeter it works. One stumbling block down. I can try another feature now. Thank you again.
Nov 10 '06 #15

Post your reply

Sign in to post your reply or Sign up for a free account.