create a query!

[Matthew38]

Hi

I am very new at all of this, so please help. I have written a database that books out equipment on a daily bases. What I am struggling with is, I have created a query that shows me the equipment I have booked out, but although I can see it, it must not allow me to double book it. I have absolutely no idea on how to do this.

Please can anyone give me some tips on how I can solve this.

Thanks

[Stwange]

Hi

I am very new at all of this, so please help. I have written a database that books out equipment on a daily bases. What I am struggling with is, I have created a query that shows me the equipment I have booked out, but although I can see it, it must not allow me to double book it. I have absolutely no idea on how to do this.

Please can anyone give me some tips on how I can solve this.

Thanks

How do you book it out? Using a combobox? Textbox? What field is set if equipment is booked out? I need more information.

Thanks.

[Matthew38]

How do you book it out? Using a combobox? Textbox? What field is set if equipment is booked out? I need more information.

Thanks.

Hi

I use a combobox with a drop down menue with "In" and "OUT" as the the field. I am not sure if I did the correct way.

What happens is that on a daily bases I have to book In and Out equipment. There is a lot of it. What also happens is that equipment will booked out and then brought back a few minutes later and if I don't book it back because it is so busy I make the mistake of booking it out again. This I lose track of the in and out. That's why I need it to show me it is booked out by maybe a field color change and by not allowing me not to book it out again until it is booked in.

I hope this makes sense.

Thanks

[Stwange]

One way would be to have a command button for booking it out, and ensuring the combobox has the not in list property set to false.
Now, assuming you have some field ID to identify the equipment, and this is stored in txtID, and your command button is cmdBookOut (so obviously, modify the code as necessary):

Private Sub cmdBookOut_Click()
dim rs as DAO.recordSet
Set rs = DBEngine(0)(0).openRecordSet("SELECT * FROM Equipment WHERE ID = '" & txtID.value & "';")
if rs!booked = "OUT" then
msgbox "That equipment is already booked out.",vbcritical,"Equipment unavailable"
else
docmd.runsql("UPDATE Equipment SET booked = 'OUT' WHERE ID = '" & txtID.value & "';")
msgbox "Equipment successfully booked.",vbinformation,"Database Updated"
end if
End Sub
 

Let me know if you have any problems with this.

[Matthew38]

Hi
Thanks for the quick response. Please, Like I said, I am very new at this. The form runs of a query. Now I am not sure if you can still do what you have suggested.
If I where to put in a cmdbox, where will I put it and The "not in list" properties, How do I set the property to False. Do I type it in?

Form: Runs of a query

JobNumber
WorkerName = Combobox = Table Employee
Location = Combobox = Table Location
Equipment = Combobox = Table Equipment
InOut = Combobox = Table In/Out
Date

Please help!!
Thanks

[Stwange]

Hi
Thanks for the quick response. Please, Like I said, I am very new at this. The form runs of a query. Now I am not sure if you can still do what you have suggested.
If I where to put in a cmdbox, where will I put it and The "not in list" properties, How do I set the property to False. Do I type it in?

Form: Runs of a query

JobNumber
WorkerName = Combobox = Table Employee
Location = Combobox = Table Location
Equipment = Combobox = Table Equipment
InOut = Combobox = Table In/Out
Date

Please help!!
Thanks

Just so I don't write irrelevant code for you again (although it is slightly relevant...), please can you paste me the rowsource of each of those comboboxes (you can find this by right clicking on them in design view -> properties -> row source (the "not in list" property is here also, and you just click and select no - not completely necessary, but will help prevent user error). I also want to see the design of Table Equipment and Table In/Out (just the relevant fields will do - primary key, foreign keys, and any fields you might want the code to update).
These few bits of information should give me enough information to provide you with a full answer. It would also be helpful (but not strictly necessary) if you could take a screenshot of this form (press Print Screen), and then paste this into paint, label any controls (doesn't have to look good, just a rough indication), and upload it somewhere or attach it.

Thanks, and sorry for asking for so much.

[Matthew38]

Hi
Thanks so much for wanting to help. Is there no way I can send you the database. It is only 2.37megs big. My email address is [e-mail]. Pleae let me know. If not I will try and do as you asked.

Thanks again