Question DLookUp????

[Steve_T]

DLookUp???? (Someone please help!!!!!!!)

Hi,
My problem is that i have a form which is used to book dates against a employee. I have a SubForm placed on the form that shows all dates currently booked against that employee, which automatically populates when the EmployeeID is entered into the Form.
I am trying to find a way to stop the same date being booked against the same employee.

The Employee info is stored on one table and the dates are stored on another using the EmployeeID as the join for both.

I think what i need is a DLookup but i am not sure as it will need to check that the same Employee does not already have the date booked already.

I have attached a sample just incase anyone can help me.

I am very new to Access so please try not to just baffle me with technicle terms.

 

[wazz]

you should first change some field names.
- ID is too vague; EmpID or sim.;
- Date is reserved; tblBookingDate (table), BookingDate (field), or sim.

dlookup could work, or dcount:
if dcount("BookingDateID", "tblBookingDate", "EmpID = " & me.EmpID) > 0 then
'already have that date.

 

[Steve_T]

Hi thanks but this only checks that the EmpID is not already in the table i need it to also check that the BookingDateID is also not booked by that EmpID. So to stop the same date being booked against the same employee.

 

[wazz]

just had a closer look. put only one ID field in each table.

change tblEmployeeInfo to:
tblEmployee
EmployeeID (autonumber)
etc


change tblDate name to:
tblBooking
BookingID (autonumber)
EmployeeID
BookingDate

repost db after making changes if poss.

edit: you're right, ultimately it will be *something* like
If DCount("BookingID", "tblBooking", "EmployeeID = " & Me.txtEmployeeID And "BookingDate = #" & Me.txtBookingDate "#") > 0 Then

or possibly
If DCount("EmployeeID", "tblBooking", "BookingDate = #" & Me.txtBookingDate "#") > 0 Then
i think there might be a simpler way though...too many strange names ...

 

[Steve_T]

Hi sorry to bother you again the EmployeeID in the tblEmployee cannot be a autonumber as its a clock number.

 

[wazz]

i don't know what that is but still, change the autonumber field's name to EmpID or something recognizable and that should help a lot. maybe change EmployeeID to EmpNum if you can so it doesn't look like the 'ID' (primary key) of the table.

 

[NigelShaw]

Hi,

you could use the format of the last DLookup a provided to you as the format is usually the same but this should work-

Dim DteCheckDte As Date
Dim strEmpName As String

'set variable as date value from form
DteCheckDte = Nz(Me!Date.Value, 0)
'set variable to employee current name
strEmpName = Me!FirstName & " " & Me!LastName
'stop zero entries to the table
If DteCheckDte = 0 Then
MsgBox ("You need to enter a booking date!")
Exit Sub
Else
'check the date against the date list
If DteCheckDte = Nz(DLookup("date", "tblDates", "EmployeeID=" & EmployeeID)) Then
MsgBox ("This date is already booked for " & strEmpName)
Exit Sub
Else
DoCmd.GoToRecord , , acNewRec
End If
End If

i would change the way you are adding the record as it doesnt seem to be stable enough in the sense of the code will work with data already in the table yet entering new data, the code doesnt work until the table is closed and re-opened. i would prefer either a SQL INSERT statement or Recordset.

i included a get out for no data as currently, empty records were being created.

someone might have better solutions but this works for me.

NS

 

[Steve_T]

NS,
Your Codes looks just what i need but it does not work as it still allows the user to book dates already booked against an employee.

 

[Steve_T]

WAZZ,
Thanks also for your help but neither of the suggestions worked, just out of interest did you try then in the sample i attached? The reason i ask is just wondering if it was something i have not done correctly or not.

 

[Steve_T]

DLookup/DCount (PLEASE HELP ME!!!!!)

Hi,
My problem is that i have a form which is used to book dates against a employee. I have a SubForm placed on the form that shows all dates currently booked against that employee, which automatically populates when the EmployeeID is entered into the Form.
I am trying to find a way to stop the same date being booked against the same employee.

The Employee info is stored on one table and the dates are stored on another using the EmployeeID as the join for both.

I think what i need is a DLookup but i am not sure as it will need to check that the same Employee does not already have the date booked already.

I have attached a sample just incase anyone can help me.

I am very new to Access so please try not to just baffle me with technicle terms.

 

[georgedwilkinson]

Any particular reason you feel you need to post this more than once?

Stated another way...don't post the same thing twice.

 

[Steve_T]

Any particular reason you feel you need to post this more than once?

Stated another way...don't post the same thing twice.

And how does this concern you??

Stated another way... Do you think it might be that i got the title wrong (Hence why the titles are different).

But the real question here is that you looked at both and did not take the time to help. So 10/10 to you!!!

 

[WayneRyan]

Steve,

And how does this concern you??

Doesn't concern me either ... just passing through.

Keep up the good work George.

Wayne

 

[georgedwilkinson]

Not sure that this deserves an answer, but I'm going to anyway.

And how does this concern you??

Because as a volunteer here who is here to help people get their problems resolved, I have to read each and every request. It takes time to do that. Time is a finite resource, for all of us. Reading the same request twice wastes the time it takes to read one of them. And being a good forum community member, I realize that there are many other great posters out there who are doing the same thing, wasting the same amount of time reviewing duplicate posts.

Stated another way... Do you think it might be that i got the title wrong (Hence why the titles are different).

No, I don't think that at all since the other post had already had several responses before you posted this one. All you needed to do was edit it. And the titles convey the same non-information (QQ).

You posted this because you didn't like the answer you received on the other post and didn't want to bump it and ask for someone else to help out.

Also, was that a question? I don't want to baffle you with technical stuff, but usually, in the English language when you use the words "Do you" at the beginning of a sentence, your intent is to ask a question. An additional rule in the English language is to use a question mark when you're asking a question...it is the regular slash ("/") shifted...that means you press the shift key and then type the slash.

But the real question here is that you looked at both and did not take the time to help. So 10/10 to you!!!

I don't even know what 10/10 means but I have to accept it as a compliment, since ten divided by ten is equal to one...my assumption is that you're saying I'm number one. I really appreciate that but there are others on the forums who are much better than I.

Do you believe that everybody who reads your post thinks they're qualified, or have time, or believe they are smarter than the guy who already is trying to help you? You already have one of the smartest people on the forum all over your other post. I can't imagine what more I can say that Wazz can't say, especially since I go out of my way to design stuff so I don't have to use DAnything and really don't want to go read the help file so I can answer your question when a smart dude is already helping you.

An additional rule I use to determine whether I'm gonna answer a post is whether I think the person is gonna be able to understand my answer. Since you said:

I am very new to Access so please try not to just baffle me with technicle terms.

I had to assume that you would need things spelled out for you. And as I said earlier, I have a finite amount of time and don't have time to teach you Access, especially on a sub-topic I'm not versed on, so I opted to just let the very smart person who was already helping you work on it with you.

In conclusion:
Stated another way, don't post the same thing twice.

 

[NigelShaw]

hi Steve

I tried this in a mock version I created and it worked fine though I was using a different method of adding the record. I created a blank form and collected the data throught dao.recordset and then added the record using the same method.

For whatever reason, I could do dao.recordset on your sample but I think the problem might be due to the fact that the form is based on the table and the date box is too thus entering a date will by default add a record irrespective of prior conditions set up.

Another cheat way could be to make the current date box visible.false andhave a new unbound textbox to enter the date. You would then check this value against existing dates without a record being made. Only if the date is not there would a new record be made. I will look at this when I get to work.

Nigel

 

[wazz]

i added a query and a bit of code to your command button.
you really must rename all your objects. it's painful to work with the objects as they are.

hi steve and nigel. i'm sure nigel is right about all of that. i started to redesign a bunch of things and was about to work with a recordset but there was no reference to dao and blah blah blah ... there are so many things that should be changed i just created something that works as it is. i was also about to create an index on employee/date so a duplicate couldn't be added but that would mean more code to deal with the error msg, etc.

steve, if you have the time, try working with the forms in a simpler way. for example, you have employees and bookings. set the main form to the employees table and the subform to the bookings table. the relationship will be created automatically, if it's set up properly. let access do some of the work for you. if you need to lock some controls from input, like an ID field, lock them or don't even put them on the form. set the relationships in the relationship window. (change the ID field names! don't use Date as a field name. consider a subform in datasheet view instead of continuous form.)

 

[boblarson]

And how does this concern you??

Stated another way... Do you think it might be that i got the title wrong (Hence why the titles are different).

But the real question here is that you looked at both and did not take the time to help. So 10/10 to you!!!

It DOES concern him, AND me (as Admin) because we do not allow multiple posts here. If you find you posted incorrectly you let the moderators know by clicking on the Black exclamation mark in the red triangle and give us information so we can move the post.

 

[Steve_T]

Not sure that this deserves an answer, but I'm going to anyway.



Because as a volunteer here who is here to help people get their problems resolved, I have to read each and every request. It takes time to do that. Time is a finite resource, for all of us. Reading the same request twice wastes the time it takes to read one of them. And being a good forum community member, I realize that there are many other great posters out there who are doing the same thing, wasting the same amount of time reviewing duplicate posts.



No, I don't think that at all since the other post had already had several responses before you posted this one. All you needed to do was edit it. And the titles convey the same non-information (QQ).

You posted this because you didn't like the answer you received on the other post and didn't want to bump it and ask for someone else to help out.

Also, was that a question? I don't want to baffle you with technical stuff, but usually, in the English language when you use the words "Do you" at the beginning of a sentence, your intent is to ask a question. An additional rule in the English language is to use a question mark when you're asking a question...it is the regular slash ("/") shifted...that means you press the shift key and then type the slash.



I don't even know what 10/10 means but I have to accept it as a compliment, since ten divided by ten is equal to one...my assumption is that you're saying I'm number one. I really appreciate that but there are others on the forums who are much better than I.

Do you believe that everybody who reads your post thinks they're qualified, or have time, or believe they are smarter than the guy who already is trying to help you? You already have one of the smartest people on the forum all over your other post. I can't imagine what more I can say that Wazz can't say, especially since I go out of my way to design stuff so I don't have to use DAnything and really don't want to go read the help file so I can answer your question when a smart dude is already helping you.

An additional rule I use to determine whether I'm gonna answer a post is whether I think the person is gonna be able to understand my answer. Since you said:

I had to assume that you would need things spelled out for you. And as I said earlier, I have a finite amount of time and don't have time to teach you Access, especially on a sub-topic I'm not versed on, so I opted to just let the very smart person who was already helping you work on it with you.

In conclusion:
Stated another way, don't post the same thing twice.

Georgedwilkinson,
I owe you an apology it was uncalled for and unprofessional to speak to you like that. I took out my frustration and comments my ungrateful boss made about my Database, which lead to my original posting of the question. Like all boss's they seem to lack the understanding of whats involved in creating them and want everything now.
I have always been grateful for the help, support and advice all at the AWF have given me.
Once again sorry for the comments

 

[Steve_T]

It DOES concern him, AND me (as Admin) because we do not allow multiple posts here. If you find you posted incorrectly you let the moderators know by clicking on the Black exclamation mark in the red triangle and give us information so we can move the post.

boblarson,
Thank you for the information on the Black exclamation mark in the red triangle, i was not aware of this function.

 

[Steve_T]

i added a query and a bit of code to your command button.
you really must rename all your objects. it's painful to work with the objects as they are.

hi steve and nigel. i'm sure nigel is right about all of that. i started to redesign a bunch of things and was about to work with a recordset but there was no reference to dao and blah blah blah ... there are so many things that should be changed i just created something that works as it is. i was also about to create an index on employee/date so a duplicate couldn't be added but that would mean more code to deal with the error msg, etc.

steve, if you have the time, try working with the forms in a simpler way. for example, you have employees and bookings. set the main form to the employees table and the subform to the bookings table. the relationship will be created automatically, if it's set up properly. let access do some of the work for you. if you need to lock some controls from input, like an ID field, lock them or don't even put them on the form. set the relationships in the relationship window. (change the ID field names! don't use Date as a field name. consider a subform in datasheet view instead of continuous form.)

Wazz,
I am very grateful for your help and advice, i think sometimes it pays to step back and go back to basics. As i am sure you have seen i am very new to Access and always trying and willing to learn.
Once again thanks.