Using a switchboard control to open a parameter screen then access an update form

[mtgrizzly52]

Hi all,

I've looked for an answer for this in lots of books, online in several discussion groups and have not found the answer which I feel may be very simple.

What I want to do is have a switchboard with several active buttons on it for entering new data, updating data, reports etc. The new data and report stuff is easy, but the update button has me baffled.

What I want to have happen is when the update button is clicked, a parameter msgbox will pop up asking for a specific unique number/letter combination. This is called a DOT Number. Each record has a DOT Number which is unique to that record with no duplication in any other record.

Once the parameter box opens, the user will have to type in the specific DOT Number for the record they will be updating. Once they have entered the number then they would hit a button that would open up the one and only form in the database.

The form consists of the main form where new records are developed, and a subform for updating various activities that are specific to the main record.

Once they are done doing their updates and saving them, then they would hit a command button that would close the form and take them back to the main switchboard, or they could hit another button that would open up the parameter box we previously discussed so they could type in a new DOT Number and it would bring up the proper record for updating.

Is all of this possible. I am fairly new to database building, but am learning fast. I'm pretty shaky with VBA, but again slowly but surely learning.

Thank you all so much for any help you can give me on this last thing I need to do before turning over the database for testing.

Rick Flink, i.e. the Grizz!

[NeoPa]

Firstly, I think all you've described is possible.
There may be alternatives which are more suitable though.

• It is usual for an operator to enter a key or search criteria in a TextBox (or even select from a ComboBox) on the form itself before loading up the relevant record details (See Example Filtering on a Form. for a Tutorial on this concept).
• Updating records on a form is normally done by simply moving off that particular record (this is the natural Access way using Bound forms) and this could be done programmatically (as in behind a Command Button) if required. It is also possible to create and update the data using RecordSet objects in the code. Needless to say, it's easier doing it the Access way.

[mtgrizzly52]

Firstly, I think all you've described is possible.
There may be alternatives which are more suitable though.

• It is usual for an operator to enter a key or search criteria in a TextBox (or even select from a ComboBox) on the form itself before loading up the relevant record details (See Example Filtering on a Form. for a Tutorial on this concept).
• Updating records on a form is normally done by simply moving off that particular record (this is the natural Access way using Bound forms) and this could be done programmatically (as in behind a Command Button) if required. It is also possible to create and update the data using RecordSet objects in the code. Needless to say, it's easier doing it the Access way.

I'm sorry, I didn't explain myself very well I think. The basic information that is originally entered into the database via the main form will not change. What this main information represents is a railroad crossing area, where we have had multiple incidents over a period of three years.

The update subform will be used to submit what type of activities our managers have done to lessen the chances of additional incidents occurring in this location. These activites will be tracked by date and type basically.

The tie in of course is that the subform is tied to the main form through child/master links. The only real unique field is the DOT Number field so that is what I want to use as the way to get to the proper record for updating purposes.

[NeoPa]

The unbound TextBox (or maybe ComboBox would be better for your situation) solution I suggested is merely for selecting the DOT number you're interested in. I don't see this would be in any (material) way different from a popup input box.
Unusually ;) Your post is actually quite clear and well expressed. Unless I misunderstand you still, both ideas would be appropriate for your situation. I'm not suggesting you need the full complexity of the filtering found in the Tutorial. Simply a single unbound control that would allow you to select a DOT number which is then the active one.

[ADezii]

Hi all,

I've looked for an answer for this in lots of books, online in several discussion groups and have not found the answer which I feel may be very simple.

What I want to do is have a switchboard with several active buttons on it for entering new data, updating data, reports etc. The new data and report stuff is easy, but the update button has me baffled.

What I want to have happen is when the update button is clicked, a parameter msgbox will pop up asking for a specific unique number/letter combination. This is called a DOT Number. Each record has a DOT Number which is unique to that record with no duplication in any other record.

Once the parameter box opens, the user will have to type in the specific DOT Number for the record they will be updating. Once they have entered the number then they would hit a button that would open up the one and only form in the database.

The form consists of the main form where new records are developed, and a subform for updating various activities that are specific to the main record.

Once they are done doing their updates and saving them, then they would hit a command button that would close the form and take them back to the main switchboard, or they could hit another button that would open up the parameter box we previously discussed so they could type in a new DOT Number and it would bring up the proper record for updating.

Is all of this possible. I am fairly new to database building, but am learning fast. I'm pretty shaky with VBA, but again slowly but surely learning.

Thank you all so much for any help you can give me on this last thing I need to do before turning over the database for testing.

Rick Flink, i.e. the Grizz!

The simplest, but not necessarily the most efficient, solution to your problem is as follows:
__01 Edit your Switchboard Item (Button) in the following manner:
______A Text: Open <your Form Name here>
______B Command: Run Code
______C Function Name: fSearchForDOTNumber
_02 Clicking on this Switchboard Button will now run the code below. This code is for display purposes only, you must substitute your own values in the code to work within your context:

Public Function fSearchForDOTNumber()
Dim strResponse As String
 
'Here is your Prompt
strResponse = InputBox$("ENTER the DOT Number to search for", "DOT Number Search")
 
'User pressed Cancel or OK with no value entered, get out of Function!
If Len(strResponse) = 0 Then Exit Function
 
'If the next line = TRUE, then no DOT Number exists (make necessary substitutions in your code). Inform the User of this and exit the Function.
If IsNull(DLookup("[LastName]", "tblEmployee", "[LastName]='" & strResponse & "'")) Then
  MsgBox "The DOT Number " & strResponse & " does not exist in the Database", vbExclamation, "No Record Found"
    Exit Function
Else
  'The DOT Number does in fact exist, so open your Form in Edit Mode with the specific Record (DOT Number) being displayed. (again, make your substitutions)
  DoCmd.OpenForm "frmEmployees", acNormal, , "[LastName]='" & strResponse & "'", acFormEdit
End If
End Function

'Simply closing the Form at the end of your Edits will automatically return you to the Switchboard. Hope this helped.

[mtgrizzly52]

Thank both of you so much. It took awhile to figure things out, but I learned a ton and everything works so sweet!

ADezii, I tried your suggestion out, but I kept getting errors, and I am not educated enough in code to figure out what was going wrong. Thanks for taking the time though to help me out!!!!

NeoPa, I studied and studied your tutorial, and finally figured out the logic of what you were doing. I followed your suggestions, started out with a textbox and converted to a combo box. The one thing I can't figure out though is how to get the focus set on the unbound combo box that is being used to pull the proper record out for updating. This is so minor though compared to getting the form to work properly, I'm not worried about it.

Again, thank you both so much for your help!!!

Rick

[MMcCarthy]

Thank both of you so much. It took awhile to figure things out, but I learned a ton and everything works so sweet!

ADezii, I tried your suggestion out, but I kept getting errors, and I am not educated enough in code to figure out what was going wrong. Thanks for taking the time though to help me out!!!!

NeoPa, I studied and studied your tutorial, and finally figured out the logic of what you were doing. I followed your suggestions, started out with a textbox and converted to a combo box. The one thing I can't figure out though is how to get the focus set on the unbound combo box that is being used to pull the proper record out for updating. This is so minor though compared to getting the form to work properly, I'm not worried about it.

Again, thank you both so much for your help!!!

Rick

To set focus to any control you use

Me.ControlName.SetFocus

Mary

[NeoPa]

Thank both of you so much. It took awhile to figure things out, but I learned a ton and everything works so sweet!

ADezii, I tried your suggestion out, but I kept getting errors, and I am not educated enough in code to figure out what was going wrong. Thanks for taking the time though to help me out!!!!

NeoPa, I studied and studied your tutorial, and finally figured out the logic of what you were doing. I followed your suggestions, started out with a textbox and converted to a combo box. The one thing I can't figure out though is how to get the focus set on the unbound combo box that is being used to pull the proper record out for updating. This is so minor though compared to getting the form to work properly, I'm not worried about it.

Again, thank you both so much for your help!!!

Rick

I'm very pleased to hear it Rick.
Messages like that make it all worthwhile somehow :)
I'd answer your last question but Mary has already ;)

[mtgrizzly52]

Thanks Mary,

I was trying to figure out how to do that for another minor glitch I have going on.

You guys and gals are the best and thanks for helping out a rookie!

Rick

[MMcCarthy]

Thanks Mary,

I was trying to figure out how to do that for another minor glitch I have going on.

You guys and gals are the best and thanks for helping out a rookie!

Rick

You're welcome Rick.

That's what we're here for.

Mary