Convert recalc VBA expression to Macro

[mikenificent]

Hi there, I have a db that I would like to get web ready one difficulty I'm having is converting the After Update VBA to macro as web db's don't use VBA.

The VBA expression recalc is what I can't figure out, below is the VBA I want to convert:



Private Sub cboRoom_AfterUpdate()
Me.Recalc
Me.Requery
End Sub

The above is probably a very simple one sorry I just can't figure it out.

[rpeare]

Are you changing the control source of an item on a form? is the code you have shown the entirety of the code associated with the AFTER UPDATE property of your cboRoom field? Also... why are you using me.recalc on the field you're exiting or requerying the field you're updating as well? you should only need to use recalc/requery on fields OTHER than the one you're working on.

[mikenificent]

First off thanks for your reply! Sorry I should have detailed my problem better but yes it's for a form where I have 4 cascading comboboxes so basically for the four Row Sources it goes like this in order:

SELECT DISTINCT tblSpace.Campus FROM tblSpace WHERE (((tblSpace.Building) Like Nz(Forms!frmInput!cboBuilding,"*")) And ((tblSpace.Floor) Like Nz(Forms!frmInput!cboFloor,"*")) And ((tblSpace.Room) Like Nz(Forms!frmInput!cboRoom,"*"))) ORDER BY tblSpace.Campus;

SELECT DISTINCT tblSpace.Building FROM tblSpace WHERE (((tblSpace.Campus) Like Nz(Forms!frmInput!cboCampus,"*")) And ((tblSpace.Campus) Like Nz(Forms!frmInput!cboFloor,"*")) And ((tblSpace.Room) Like Nz(Forms!frmInput!cboRoom,"*"))) ORDER BY tblSpace.Building;

SELECT DISTINCT tblSpace.Floor FROM tblSpace WHERE (((tblSpace.Campus) Like Nz(Forms!frmInput!cboCampus,"*")) And ((tblSpace.Building) Like Nz(Forms!frmInput!cboBuilding,"*")) And ((tblSpace.Room) Like Nz(Forms!frmInput!cboRoom,"*"))) ORDER BY tblSpace.Floor;

SELECT DISTINCT tblSpace.Room FROM tblSpace WHERE (((tblSpace.Campus) Like Nz(Forms!frmInput!cboCampus,"*")) And ((tblSpace.Building) Like Nz(Forms!frmInput!cboBuilding,"*")) And ((tblSpace.Room) Like Nz(Forms!frmInput!cboRoom,"*"))) ORDER BY tblSpace.Room;

And then for my After Update event I have this in order:

Private Sub cboCampus_AfterUpdate()
Me.cboFloor.Requery
Me.cboFloor.Requery
Me.cboBuilding.Requery
Me.Requery
End Sub

Private Sub cboBuilding_AfterUpdate()
Me.Recalc
Me.Requery
End Sub

Private Sub cboFloor_AfterUpdate()
Me.cboCampus.Requery
Me.Requery
End Sub

Private Sub cboRoom_AfterUpdate()
Me.Recalc
Me.Requery
End Sub

This works perfectly on a normal DB but because you can't edit VBA for the After Update on a Web DB I have to try do it with macro, I have only put the requery in on a macro because I don't know how to do recalc on macro, but then it breaks everything down fine up untill cboRoom (last one) which shows all rooms for the entire Building instead of just the selected cboFloor.

I apologise if this might seem vague but it's the best way I can explain it as I'm new to access and macro and VBA EVERYTHING, but a quick learner.

Thanks again.

[rpeare]

Have you tried running it without the me.recalc at all in your macro. I don't think you need it, especially now that you say it's cascasing combo boxes. Really all cascading combo boxes are a display of queries with criteria and you do not need a recalc to do that, the requery function will work just fine except I would use

Private Sub cboBuilding_AfterUpdate()
cboFloor.requery
cboRoom.requery
me.requery
End Sub

Private Sub cboFloor_AfterUpdate()
cboroom.requery
me.requery
End Sub

Private Sub cboRoom_AfterUpdate()
Me.Requery
End Sub

What exactly are you using the RECALC to try and do?

[mikenificent]

Yes well I've been trying to, I thought recalc would re-establish what should be shown in the field depending on your selections I gave your method a try and it works great for the first three combos but for some reason I have no idea why the rooms don't get filtered down to floor they only get filtered down to building. To be completely honest with you I'm very unorthodox when it comes to VBA and Macros, I'm like a code pirate that surfs the web for code I can grab then paste it in my code stash folder with descriptions of what they do and minipulate it to a db I'm working on when I need to, so I have some amazing db's that I myself the creator don't FULLY understand.

Thanks for your help, this is how I learn.

[rpeare]

if your ROOMS combo box is not properly showing a list of values for the FLOOR you're on you likely don't have the criteria set up correctly in the ROOMS combo box. I'm just guessing but you likely do not have the FLOOR criteria or you simply haven't assigned a room to a particular floor in a particular building. If that's the case and you are like most places you could use the first character of the room number to determine the floor it's on.

As far as how your approach VBA I think that's how most people do it, if I don't know how to do something or don't remember that's what I do all the time.

[mikenificent]

I'm going to have to go for your option number one a problem on my combo box, the space database comprises of 4 campuses, 430 buildings, lots of floors and 9000 rooms which reflect in the db as they are on the physical doors so definitely can't go changing room numbers because of a db problem.

Basically heres a shortened version of the space table:

Campus	Building	Floor	Room
DFC	DFC - John Orr Building	Ground	G120A1
DFC	DFC - John Orr Building	First	1107
DFC	DFC - Civil Eng Building	Ground	CE-G03
DFC	DFC - Civil Eng Building	First	CE-102
APK	APK - Core 1	Ground	K1-G04
APK	APK - Core 1	First	K1-102
APK	APK - A-Ring	First	A-103
APK	APK - A-Ring	Second	A-202
APB	APB - Security Office	Ground	SO-G01
APB	APB - Security Office	First	SO-101
APB	APB - Fada Building	Lower Ground 1	FAD-L37A
APB	APB - Fada Building	Ground	FAD-G001
SWC	SWC - Academic D & E	Ground	ACD-104
SWC	SWC - Academic D & E	First	ACD-203
SWC	SWC - Admin A	Ground	ADA-102
SWC	SWC - Admin A	First	ADA-201

I'm going to have to figure out what's going wrong with that room combo.

[rpeare]

if that's the setup of your 'space' table you are definitely missing a criteria in your ROOM combo box,

[mikenificent]

Have you any suggestions what could be wrong with my row source string on the room combo:

SELECT DISTINCT tblSpace.Room FROM tblSpace WHERE (((tblSpace.Campus) Like Nz(Forms!frmInput!cboCampus,"*")) And ((tblSpace.Building) Like Nz(Forms!frmInput!cboBuilding,"*")) And ((tblSpace.Room) Like Nz(Forms!frmInput!cboRoom,"*"))) ORDER BY tblSpace.Room;

[rpeare]

Why are you using LIKE in your SQL statement? Your users are picking from a distinct list, it shouldn't be a partial string (which is what like is used to fill in)

But the last part of your statement should be regarding the FLOOR not the ROOM if I understand you pick your Campus first, Then the building, then the FLOOR, then the room, you are limiting this query only to the building/campus not the floor.