Transferring data from one form to another using on KeyPress

[JeanieGreene]

Hello all.

I have attached my database. I need my entry on Main_Page to self populate into the form Vehicle on the press of the return key. The Main_Page is the data entry site for my "Radiolog" table. The point of the radiolog is to keep track of and time stamped all radio traffic that occurs. But, when a LE request to run something (is on a traffic stop) we have to log the information in the radio log, but I do not want to then reenter the information in the vehicle form/table. I want the information to self populate in the appropriate fields and bring open the Vehicle Form to enter further data.

This is the code I am currently using...
Private Sub Form_Main_Page_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
Key Code = 0
If IsNull(FunctionKey) Then
Me.FunctionKey.SetFocus
Else
If Me.FunctionKey = t Then
DoCmd.OpenForm Vehicle
Forms!Vehicle!UnitCalling = Me.UnitCalling
Forms!Vehicle!(Location, [Vehicle State], Plate) = Split(Me.Notes, ",")

End If

End Sub

However, this code does absolutely nothing! :banghead:

Thank you for any help. You guys were life savers before.

 

[JeanieGreene]

Ok, so I got my KeyPress figured out.

No I am having problems with the splitting of information from my Notes Field in the Main_Page form into 3 fields in the Vehicle Form.

Forms!Vehicle!(Location, [Vehicle State], Plate) = Split(Me.Notes, ",")

I want to type in the notes field: "location,FL,Plate#" and have the comma differentiate which field it is to populate.

Thank you!

 

[jdraw]

Please describe the purpose of this database in plain simple English --just as you would tell an 8 yr old.
No jargon and no database terminology - just simple words. I see vessels and vehicles

Your form looks extremely busy and its purpose/use is not obvious (to me at least).

Your tables show no relationships in the relationships window. It seems 2 of the tables are not present in the relationships window.

You should be aware of reserved words used as field names. Most will tell you to avoid embedded spaces and special characters (/ #) in field and object names.

I have no idea what this is intended to do
Forms!Vehicle!(Location, [Vehicle State], Plate) = Split(Me.Notes, ",")

Good luck with your project.

 

[JeanieGreene]

Thank you for your help. This is supposed to be a self built Computer Aided Dispatch. I run a small emergency and law enforcement dispatch center, and I am just trying to make our lives easier.

We must keep a log of all radio and phone calls that come into the dispatch center. In addition, we need to have separate records for our Calls For Service (think 911 calls), vehicle stops, and vessel stops. The radio log needs to have the information that is called into us, while the Calls For Service, Vehicle Stops and Vessel Stops need more details.

None of the tables really have any relations besides the fact I need to pull a few of the details from them.

Thanks for helping out.

 

[JeanieGreene]

Forms!Vehicle!(Location, [Vehicle State], Plate) = Split(Me.Notes, ",",3) is intended to take the information from the Notes field on the Main_Page and enter it into the three fields (Location, Vehicle State, Plate) on the vehicle form.

 

[jdraw]

I don't think that's how Split works.

Usually I take some string/text that has a delimiter and use Split to put each segment into an array.
I think you could do that, then take those segments in order 1, 2, 3 and assign them as you intend.
(untested)

 

[JeanieGreene]

Ok. I guess I will have lots to think about during Thanksgiving day weekend! Thanks.

 

[JeanieGreene]

Is there a better way to accomplish my goal?

 

[isladogs]

Jack beat me to it.

Here's a link with lots of examples on using the split function
https://www.exceltrick.com/formulas_macros/vba-split-function/

There's plenty more examples online.
The same ideas can be used in any Office program

However, you could save yourself a lot of work by not combining the data into one multivalued field in the first place

If you save the 3 items to 3 separate fields, you can just concatenate them for display in an unbound textbox (called Notes if you want). Much easier.

There is no need to save the same data twice - in fact you should never do so.

 

[JeanieGreene]

Thanks Ridders. That link is helpful.

I know that I am not supposed to save twice, but the purpose of the database I am working on is to be a one point of entry for the dispatchers in a fast passed environment.

Thanks again.

 

[Pat Hartman]

The goal of any application is to input data once and only once. It should also be stored once and only once so you might want to review your design so see how that might be accomplished. it's late and I don't have time to review your schema but if you are duplicating data, it is very likely that it needs to be modified.

Also, you would never use keystrokes to cause the record to be saved. Assuming, you actually have to duplicate the data, you would use the AfterUpdate event of the form to push the data to the other table. In order to do this once only when the record is created, you might need a form level variable that is set to True for new records. But again, I didn't look at your app - what happens if you change the dispatch record? Is the change supposed to propagate to the other table? If so, all the more reason for there to not be two tables.

 

[isladogs]

I know that I am not supposed to save twice, but the purpose of the database I am working on is to be a one point of entry for the dispatchers in a fast passed environment.

Reiterating both my last comment & Pat's, the statement above is still not a reason for saving the data twice.
I've also not looked at your db (partly because I forgot it was in post 1) but the best solution will almost certainly be what I wrote before

save the 3 items to 3 separate fields, you can just concatenate them for display in an unbound textbox (called Notes if you want).

 

[Pat Hartman]

The company is gone so I had a few minutes to look at the database. Vehicle and Vessel have mostly the same information except that their IDs have different names You could solve that problem by having a VehicleType (Car, truck, boat, etc) and it looks like there are "internal" and "external" identifiers so you could use more generic names. Then we get to the bad part which seems to be personal information. Is this related to the person reporting the incident or the "driver"? Regardless, this data does not belong here. It belongs in a "persons" table and you would just link to that table using a FK. But in no event should you need SSN and DOB. Once you start carrying both of those pieces of data, you run the risk of subjecting these people to identity theft. Jet/ACE is a poor database choice for storing sensitive data. SQL Server would be a better choice. That way at least no one can simply copy the .accdb to a thumb drive and walk it out of the building.

You need more design work. Do some reading on normalization and how to define entities and attiributes. I think you'll find that Vehicles and Vessels have more in common than it appears in your model and they should be kept in the same table. Worst case is you might need a separate 1-1 table that holds data specific to one of the vehicle types.

You never described the type of business you are trying to model so I can't tell if you are dealing with a fleet of known vehicles and drivers or something more like Jiffy Lube where you service walk in customers and so have to gather a lot of information up front if you have not serviced the vehicle in the past.

FYI, object names including tables and columns should be comprised of ONLY letters (upper and lower), numbers, and the underscore. They should never include spaces or special characters such as / and #. You also need to avoid reserved words, especially function and property names suck as Date and Time.

 

[JeanieGreene]

This is for a Law Enforcement dispatch, and emergency dispatch center.

Good idea about saving people separately. I will work/think on that.

As a dispatcher, we will be putting a lot of information into the one entry point. This is the "Radio Log" basically all information that comes into the dispatch center goes there. I then want to take that information, and based on the function key, put it into the correct tables, and eventually push data to two separate LE databases to search National Files.

One entry into the radio log will be something like In Service, but the next might be a 911 call for a heart attack, and then a traffic stop. Right now I am working on getting the traffic stop to work properly, but the end goal is to have the vessel stop, running a person and a "call for service" (911 call) do the same type of thing. And each of those situations will require the "Notes" field to be separated differently.

In addition, the radio log has to have the data that is said on the radio in it. It is the official log of LE activity. I am using the database management aspect of Access, but I need the CAD (computer aided dispatch) more.

That being said, I am not sure splitting is the correct way. I am also looking at the InStr expressions. But, right now I am getting an error.

Private Sub Form_AfterUpdate()
If IsNull(FunctionKey) Then
DoCmd.GoToRecord , Main_Page, acNewRec
Me.FunctionKey.SetFocus
Else
If Me.FunctionKey = "t" Then
DoCmd.OpenForm "Vehicle", , , , acFormAdd
Forms!Vehicle!UnitCalling = Me.UnitCalling
Expr: Left([Me.Notes], InStr(1, [Notes], ",") - 1) = Forms!Vehicle!Location
End If
End If
End Sub
The above is the code I am using. The error I am getting is: Run-Time error '2465': Microsoft Access can't find the field '!1' referred to in your expression. (The ! is actually a line)

Any ideas?

 

[isladogs]

There are 2 lines of code shown in RED that aren't clear, at least to me:

...
[COLOR="Red"]If Me.FunctionKey = "t" Then[/COLOR]
DoCmd.OpenForm "Vehicle", , , , acFormAdd
Forms!Vehicle!UnitCalling = Me.UnitCalling
[COLOR="Red"]Expr: Left([Me.Notes], InStr(1, [Notes], ",") - 1) = Forms!Vehicle!Location[/COLOR]
...

1. Is Me.FunctionKey a form control?

2. The Expr line looks very odd...
a) Why is expr there? Remove it.
b) If you are setting the Location control value that needs to be on the left of the equals sign (like the previous line)
c) In the Instr part you refer to Notes twice but in different ways.
If its a form control then Me.Notes may be correct.
If its a field value then Notes is correct.
BUT it shouldn't be both of these

Try this line instead:

Forms!Vehicle!Location = Left([Notes], InStr(1, [Notes], ",") - 1)

 

[Mark_]

Does the error point to any specific line in your VB code? If not you may need to do some debugging by putting a field on the screen and updating it before each line of code to narrow down exactly where it is being generated.

From a technical perspective, a couple notes:
1) Use the description fields in your table definitions. This can elaborate a bit on what goes into each field, if needed. The field "FunctionKey" seems to reference a key that would have been pressed on the keyboard, such as "F11" though without knowing what values you are looking for this is mostly meaningless.

2) Vessel / Vehicle should be one file. Should have added "Type" -Add lookup file to hold values for "Type". This would allow for "Motorcycle","Sedan","Fishing Trawler", ect... I know others have touched on this but it really does make a lot of sense in your environment. Would also allow you to expand to include aircraft if that becomes an issue.

3) Give controls names that make sense. Command14 has no inherent meaning. Btn_StopTimer would make a world of sense if seen in code though.

In the future you will want to also check into using DLookup on the SSN. This way you can quickly identify repeat offenders by noting previous entries in your own Radiolog as well have adding an external file holding individuals you want to find.

I'd also make your screens a bit smaller... I know this is for use in a high traffic environment but I also know there are going to be times when your dispatchers will need to have other windows up, especially if they are using a utility to identify traffic issues or checking the status of an ongoing emergency that isn't internal to your program.

Hope this helps!

 

[JeanieGreene]

Ridders, I can't seem to thank you again, BUT... Thank You!

I did get it to work with Splitting. I will see if it still works when I do the same with the Vessels and Calls For Service. This is the code I ended up using...

Private Sub Form_AfterUpdate()
If IsNull(FunctionKey) Then
DoCmd.GoToRecord , Main_Page, acNewRec
Me.FunctionKey.SetFocus
Else
If Me.FunctionKey = "t" Then
DoCmd.OpenForm "Vehicle", , , , acFormAdd
Forms!Vehicle!UnitCalling = Me.UnitCalling
txtString = Me.Notes
WrdArray() = Split([txtString], ",")
For i = 0 To UBound(WrdArray)
Forms!Vehicle!Location = WrdArray(0)
Forms!Vehicle!VehicleState = WrdArray(1)
Forms!Vehicle!Plate = WrdArray(2)
Next i
DoCmd.GoToRecord , Main_Page, acNewRec
Forms!Vehicle!Make.SetFocus
End If
End If
End Sub

 

[isladogs]

Glad you got it sorted....
At a quick glance, your latest code looks good to me