Changing the RecordSource of a form based on field values (1 Viewer)

jnq

New member
Local time
Today, 05:33
Joined
Feb 17, 2014
Messages
4
Hi all,

What I am trying to do is simple (in theory), but as I'm very much new to Access and VBA I can't seem to get it right. I have a simple add form that will add an entry to one of two tables: Box 1-1, or Box 1-2. They have the exact same fields. In the add form, I want the user to specify a Rack field and a Box field. Box 1-1 would be Rack 1, Box 1. Box 1-2 would be Rack 1, Box 2 (there will be more tables later, but just using two for now until I get it working).
But right now my form always adds to the table Box 1-1 - I can't get it to switch.
I've added the code I have so far below. The first part is what I'm having trouble with, the second part just uses a Submit button to add a new record - I'm just including it in case it's interfering in some way I'm not seeing.
Any help or advice on how I might better go about this would be greatly appreciated! I'm running Access 2007.

Code:
Private Sub Switch_BeforeUpdate()

    If Me.Rack.Value = "1" And Me.Box.Value = "1" Then
        Form_Add.RecordSource = "Box 1-1"
    ElseIf Me.Rack.Value = "1" And Me.Box.Value = "2" Then
        Form_Add.RecordSource = "Box 1-2"
    End If
    Form_Add.Requery
    
End Sub

Private Sub Submit_Click()

On Error GoTo Err_cmdSave_Click

If MsgBox("Are you sure you want to save?", vbOKCancel) = vbOK Then

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

MsgBox ("Save successful.")

DoCmd.GoToRecord , , acNewRec

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End If

End Sub
 

namliam

The Mailman - AWF VIP
Local time
Today, 13:33
Joined
Aug 11, 2003
Messages
11,696
First off, assuming you are going to be adding more tables later on, you dont want to hardcode this... Instead you want something like:
Form_Add.RecordSource = "Box " & Me.Rack & "-" & Me.Box

Also instead of having "hard wired" tables like this... you should (re)work your data model.
Having numbered tables like this is a big sign of a badly chosen design.
Instead you should have one table that simply has 2 columns, Rack and Box, that you can play with, add/remove/filter/query/report/etc to your heart's content.
Never should variables be part of your table names, they are columns, period.

Assuming 10 racks, 10 boxes, you are looking at 100 tables... Really really look hard at your design before you progress doing anything.
 

jnq

New member
Local time
Today, 05:33
Joined
Feb 17, 2014
Messages
4
Thanks for your reply namliam!
I know what you mean. Right now I'm looking at 30 tables this way... :p I'm not a fan myself, but not sure how to do it with one table - it may be possible, but I don't know how.
Right now box and rack are not variables in the actual tables. Basically, this database will store records for items in storage. There are 6 racks, with 5 boxes each, with up to 25 slots in each. So the number slots needs to be limited, and the slot number should increment for each item put into a box.
Is there a way to limit the number of records with a particular variable? Say if the user puts into the form that the item is going into rack 3 box 1, but that box has 25 records - it shouldn't allow that record to be added.
 

namliam

The Mailman - AWF VIP
Local time
Today, 13:33
Joined
Aug 11, 2003
Messages
11,696
In access/excel/vba anything is possible, just a matter of creating it.
And yes, the BOx and Rack may be par of your current table structure but just from your description of your problem it is very clear that they are indeed variables that should simply be a column and not be part of your table naming.

Your box table would stay the same -ish depending on your total setup.
You simply add two columns, Rack and Box, which then contain values 1 thru 6 and 1 thru 5. Instead of having 30 tables with 25 records each you have one table with 750 records....

You can easily determine how many records there currently are in a given box using a query or a dcount... Depending on your logic of how you want to place the items...
If we put in 25 and take out number 10, is that even possible? Does it even matter?
If added a new item should it be (re)added as 10 again or does the number even matter and is it "good enough" to have the item in the box without regard to the slot?
 

jnq

New member
Local time
Today, 05:33
Joined
Feb 17, 2014
Messages
4
Thanks for the advice namliam. I've decided to go with the single table method and am working on creating queries to count records with specified field values instead.
Cheers!
 

jnq

New member
Local time
Today, 05:33
Joined
Feb 17, 2014
Messages
4
Yes DCount is what I'm using. Late night, not using terms correctly :p
 

namliam

The Mailman - AWF VIP
Local time
Today, 13:33
Joined
Aug 11, 2003
Messages
11,696
Good luck, let us know if you run into trouble.
 

Users who are viewing this thread

Top Bottom