Open a form on a set record

[fatboy95]

I am starting to get back into access and I am finding out that I am very rusty at it. I am having a hard time figuring out how to open a form from a button on a specific record. What I have is a table that is built like this:

Shop Table
ShopID
Shop

What I want to do is have a form that has a button for each shop in the "Shop Table" and open a form showing only the records for each shop. I am sure this isn't very hard but for some reason I can not get it to work no matter what I do. Thank in advance.

 

[ShaneMan]

Hey Fatboy,

How are you going to make the list of shop tables? Are you going to use a combobox, listbox or continuous form, etc.? Depending upon what you use could make a difference on how its coded.

Shane

 

[fatboy95]

First off I think I got it working the way I want. At least it seems to be working. See the code below.

I was using a list box to start with and it works fine, but I wanted to just have a button that is asigned to each Shop. I guess I kind of wanted to have something like a Switchboard but I don't want to use the switchboard thing in access, if that makes sense.

This code seems to work the way I want it to. Let me know what you think.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

If IsNull(DLookup("shopid", "[req table]", "shopid = 1")) Then
DoCmd.OpenForm "mainForm", acNormal, , "[ShopID] = 1"
Forms!mainForm.AllowAdditions = True
DoCmd.GoToRecord acDataForm, "mainForm", acNewRec
Forms!mainForm!ShopID = 1

Else
DoCmd.OpenForm "mainForm", acNormal, , "[ShopID] = 1"

End If


Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click
If IsNull(DLookup("shopid", "[req table]", "shopid = 2")) Then
DoCmd.OpenForm "mainForm", acNormal, , "[ShopID] = 2"
Forms!mainForm.AllowAdditions = True
DoCmd.GoToRecord acDataForm, "mainForm", acNewRec
Forms!mainForm!ShopID = 1

Else
DoCmd.OpenForm "mainForm", acNormal, , "[ShopID] = 2"

End If


Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub

 

[ShaneMan]

How many shops do you think there are going to be? That would be alot of code if there are going to be a number of them.

Shane

 

[fatboy95]

Yeah, I was thinking of that to.. but to make this easier on my users and be able to open up the shop with all the required data that I need, which is coming from multipule tables, this is the only way I can see doing it. I am very rusty at this so I may be missing on other options available to me.

What I am thinking of doing is showing them the option between the list box or the buttons.

I did try using the Switchboard thing but I am not a real fan of that add in for Access.

 

[ShaneMan]

If you think there are going to be a fair amount of shops, then maybe one suggestion that I can throw your way. Use a continuous form to show all shops. Be sure to add the ShopID, then use a command button over the top of both fields and set it to transparent, then code so that it opens a form, using the ShopID as criteria, which would show a single shops information. You could then add a separate command button in the header that would allow the user to add a new shop. It's still in a list view format so you may not like that idea but I thought I would throw out there.

Shane

 

[fatboy95]

ShaneMan - I want to thank you for helping me first of all.

I do like your suggestion and I think I am going to try it. The main reason I don't won't to use a drop down list is that it creates a risk that someone will select the wrong shop and enter data for their shop into it and then loose it not relizing it. I wish I could use the drop down list because it sure would make my life easier at this point. I have that working. There will be at least 20 to 25 shops. But some of those shops will fall under a section. So it actually kind of looks like this:

Section 1
Shop 1
Shop2

Section 2

Section 3
Shop1
Shop 2
Shop 3

The problem I find in using the switchboard is it creates to much data in the table to break down the sections/shops. Then when I try to create the rest of my tables to support what data I need it won't work.

 

[WIS]

If you think there are going to be a fair amount of shops, then maybe one suggestion that I can throw your way. Use a continuous form to show all shops. Be sure to add the ShopID, then use a command button over the top of both fields and set it to transparent, then code so that it opens a form, using the ShopID as criteria, which would show a single shops information. You could then add a separate command button in the header that would allow the user to add a new shop. It's still in a list view format so you may not like that idea but I thought I would throw out there.

Shane

I use Shanes idea of a continuous form as well - however I make the colour of one field eg ShopID or ShopName etc different to the other columns and have the user double click on that. Heres an example of the code in the continuous form frmJobs:

Private Sub txtTitle_DblClick(Cancel As Integer)
Dim DocName As String
Dim LinkCriteria As String
DocName = "frmJob"
LinkCriteria = "[JobID] = Forms!frmjobs![JobID]"
DoCmd.OpenForm DocName, , , LinkCriteria
End Sub
(lazy me - no error trapping in the above)

Here the user double clicks on a different field to the one that is in the LinkCriteria - you must have an unique field in the LinkCriteria line, in this case the JobID (PK).

For a new record I have a button "New Job" in the Header section of the form. Heres the code for the Click event:

Private Sub cmdNewJob_Click()
On Error GoTo Err_cmdNewJob_Click

DoCmd.OpenForm ("frmJob")
DoCmd.GoToRecord , , acNewRec

Exit_cmdNewJob_Click:
Exit Sub

Err_cmdNewJob_Click:
MsgBox Err.Description
Resume Exit_cmdNewJob_Click
End Sub

Hope this helps you.