Query SQL to VBA code for use as a recordset

[jdlewin1]

tried uploading the database but it failed due to a security key or something...i will try again

 

[jdlewin1]

failed to upload again.....missing security token or something!

 

[isladogs]

Try zipping it

 

[jdlewin1]

i tried as a .accdb and .rar file but both did the same thing. The files size is 37,000KB so should have been ok

 

[isladogs]

If you look in Manage Attachments section the max file size for accdb or zip is 2MB. Looks like rar isn't supported

So you're going to have to remove everything unnecessary to this issue before it will let you upload the file.

 

[jdlewin1]

i removed everything so only the form is there and the 3 tables. I have removed all the additional records so there isnt much in each table.

The file siz is 37,000 KB and then zipped its 34,000 KB i cant see how i can get it smaller than that to upload it.

 

[jdlewin1]

few....sorted it.....file attached

 

[isladogs]

I'm guessing you compacted the database!

 

[jdlewin1]

i created a new database a copied the form, tables etc across and it changed the size from 37,000 to 780 KB

You will see in the VBA code there are a lot of strSQL's i have commented them out as i have been trying new things. I have added a comment before each one to try and keep track of it. The one you supplied is in the middle (roughly).

If the Union query SQL wont / is too difficult to crack with the WHEREs above in also then the last one in the code (The one that just uses the Transactions table) can be used if it can be amended to take the Wheres linking the records to the Reserve Form Asset field. I can amend the database so the reservations and transactions are all in one table if thats easier!!!

 

[MarkK]

I would do this with 7 daily subforms on a weekly subform, and a monthly main form with 4 (or 5) weekly subforms. Then you don't have to take such a monolithic approach to the overall problem.

Just make the daily form first and make it work for one day. Then make the weekly form drive data to the 7 daily subforms it contains. And so on.

In the end you should be able to throw a date at the main form, and it throws 4 (or 5) dates at its weekly subforms, and each weekly subform throws 7 dates at its daily subforms, and you don't need a monolithic management strategy for the whole problem, but rather, you will have broken in down into many simple repetitive chunks, distributed to each object to manage on its own.

hth

 

[isladogs]

Hi

Several responses here:

1. You left out the Assets table so I've added a 2 field table so I could check out the form. It still says 1 parameter is missing but I've ignored that as its obviously caused by you removing items to upload the file

Anyway I've checked the form, updated the SQL for the union query (qryUnion) and as far as I know it now works. New version uploaded.
You need to check it with real data

2. I've not checked the rest of the code in this procedure, but I agree with MarkK that it seems far more complicated than it needs to be.
Until I looked at the file, I was just trying to get your union query sorted and had no idea what you were actually trying to do.

For what its worth, I've attached screenshots of my calendar forms (month/week & day) which I populate using another form

3. So now you say you can get rid of the Transactions table!!!
I did wonder about this being as the fields are identical
Yes of course it would make life easier - at least for this particular issue
The question is do you need them separate for any other reason?

4. As previously stated, do get rid of spaces in field names.

Hope that helps

Colin

 

[isladogs]

forgot to say,
1. the updated version includes the form frmSql2vba which is my updated version of Allen Browne's code and related module code
2. It also contains another module modResize which has code to resize forms for any screen size. If you look at the added form frmSql2vba in Design view it has a font size=7 for most items but should look fine in Form View whatever screen it is viewed on.

 

[jdlewin1]

hi ridders, thank you....i will try your code shortly!!!

Your calendar looks awesome and looks like it would be really useful....I would never be able to build one of those!

 

[jdlewin1]

tried copying the code into the database and tried both versions you provided. They both throw up an error "Too few parameters. Expected 1."

 

[jdlewin1]

it maybe due to the rest of the where statements being missing. If you look at the below i also need the [FCheckOut] Between " & lngFirstOfMonth & " And " & lngLastOfMonth or [FInDate] Between " & lngFirstOfMonth & " And " & lngLastOfMonth or ([FCheckOut] < " & lngFirstOfMonth & " and [FInDate] > " & lngLastOfMonth & "......i am not sure if this would make a difference or if the code should work as you gave me?!

strSql = "Select Transactions.[Job Number], Transactions.Initials, Transactions.FCheckOut, Transactions.FInDate " & _
            "From Transactions Where Transactions.[FCheckOut] " & _
            "Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
            " or Transactions.[FInDate] Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
            " or (Transactions.[FCheckOut] < " & lngFirstOfMonth & _
            " and Transactions.[FInDate] > " & lngLastOfMonth & ")" & _
            " ORDER BY Transactions.[FCheckOut], " & _
            "Transactions.[Job Number];"

 

[isladogs]

That's what I got with your cut down db. Assumed it meant something you had left out

Access is looking for one bit of information not included in the SQL
You should be able to identify what that is.
I won't be some easy for me ...unless its the select year combo bx on the right which from memory wasn't populated???

 

[isladogs]

We're managing to reply at the same time

it maybe due to the rest of the where statements being missing. If you look at the below i also need the [FCheckOut] Between " & lngFirstOfMonth & " And " & lngLastOfMonth or [FInDate] Between " & lngFirstOfMonth & " And " & lngLastOfMonth or ([FCheckOut] < " & lngFirstOfMonth & " and [FInDate] > " & lngLastOfMonth & "......i am not sure if this would make a difference or if the code should work as you gave me?!

That wasn't in your original post & so I didn't look at that bit.
Try & add it to the union query then use the converter tool I attached to convert to sql

As for the calendar, it did take a while to build ...

 

[jdlewin1]

:banghead::banghead::banghead::banghead:

i cant get the union code to work and i cant see anything missing. The below code works if it is all in the Transactions table so im thinking its easier to do it that way.

strSql = "Select Transactions.[Job Number], Transactions.Initials, Transactions.FCheckOut, Transactions.FInDate " & _
            "From Transactions Where Transactions.[FCheckOut] " & _
            "Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
            " or Transactions.[FInDate] Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
            " or (Transactions.[FCheckOut] < " & lngFirstOfMonth & _
            " and Transactions.[FInDate] > " & lngLastOfMonth & ")" & _
            " ORDER BY Transactions.[FCheckOut], " & _
            "Transactions.[Job Number];"

The only thing that's missing is to filter the above based on the [Asset] field on the Reserve form that would get me the recordset i need. i copied the sql code from the debug.print of the above and put it into an SQl query, switched to design view and added the Assets field with the criteria [Forms]![Reserve]![Asset] and it shows my recordset im after. When i use the tool to convert to VBA code it gives the below code (obviously the date numbers should be as the code above, but it shows where the new code sites). When i put try and put this into the VBA code it doesn't seem to work.

" SELECT Transactions.[Job Number], Transactions.Initials, Transactions.FCheckOut, Transactions.FInDate "  & _
" FROM Transactions "  & _
" WHERE (((Transactions.Asset)=[Forms]![Reserve]![Asset]) AND (Transactions.[FCheckOut] Between 42826 And 42855)) or (((Transactions.Asset)=[Forms]![Reserve]![Asset]) AND (Transactions.[FInDate] Between 42826 And 42855) or (((Transactions.Asset)=[Forms]![Reserve]![Asset]) AND (Transactions.[FCheckOut] < 42826) and ((Transactions.[FInDate]) > 42855))) "  & _
"ORDER BY Transactions.[FCheckOut], Transactions.[Job Number];"

can you or ANYONE else get the Transactions.Asset=[Forms]![Reserve]![Asset] parts from the bottom code into the top code and get this to work.....or is there another way to filter out the initial recordset, not sure if it could be done with the a.filter or something!?

HELP :banghead::banghead::banghead::banghead:

 

[isladogs]

Hi

I can look at this again later today if I have time.
In the meantime, I'm more than happy if someone else wants to step in to assist

 

[jdlewin1]

hi colin, you have been a massive help so far and i really appreciate your efforts in giving me a hand !!