UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Subform Find Record As I Type, Access 2016    
 
   
brastedhouse
post Sep 6 2019, 10:16 PM
Post#1



Posts: 108
Joined: 16-March 15
From: Chautauqua, NY


Hi, I'm not sure the title is accurate. When I have a combo box and I type something, the combo box starts to fill as I type so I can find and existing record. Cool. That was great when I had the entire address in one field and the data was in a separate table. But that is lousy for searching and queries. So now I have a subform in form mode (not datasheet) who date is in a separate table. So is there a way to make sure I am not duplicating an address record when I enter a new record? Compound primary key of id, address number and street name? Is there a way to create a search form that would lookup the address and see if it exists? If it exists, use that address, if not add it to the table? As I write this I think I have may have made a structural mistake in my table design. Below are my fields. I have a table that records the info on an EMS call. Then this is a table linked to that table that records the address of the call. I need to be able to search for address by addressNumber, addressStreet or both.

One to many: tblCall/tblCallLocation
Parent: callID
Child: callTableFK

tblCallLocation Fields:
addressID
callTableFK
addressType
addressNumber
addressStreet
addressUnit
addressCity
addressAlias

Any thoughts would be appreciated.

Best, Scott
Go to the top of the page
 
brastedhouse
post Sep 6 2019, 10:38 PM
Post#2



Posts: 108
Joined: 16-March 15
From: Chautauqua, NY


Hi, new thought.

I think I may have this backwards. I think I need the FK in tblCall. tblCallLocation should be a table of unique addresses. Then the address fk for the address is in the call record. But if this is so, then can I still use a subform for the address because it is the logical way the users will see the relationship?
Go to the top of the page
 
projecttoday
post Sep 6 2019, 10:45 PM
Post#3


UtterAccess VIP
Posts: 11,067
Joined: 10-February 04
From: South Charleston, WV


No. If call is the one and call location is the many then callid goes in tblcalllocation.

Is this subform a single-record form? Did you try clicking on the address field and then clicking on Find?

--------------------
Robert Crouser
Go to the top of the page
 
brastedhouse
post Sep 7 2019, 07:13 AM
Post#4



Posts: 108
Joined: 16-March 15
From: Chautauqua, NY


Hi and thanks for the reply. In this case, a call can have one location and a location can have many calls. That's how I got to the FK in the call table.

This has been percolating in my brain overnight and I am thinking about using a combo box on the call form and a not in list event with a form instead of one field. How doe that sound? I am playing with that right now.

Best, Scott
Go to the top of the page
 
projecttoday
post Sep 7 2019, 07:52 AM
Post#5


UtterAccess VIP
Posts: 11,067
Joined: 10-February 04
From: South Charleston, WV


Please answer my questions in my previous post. Also, if you are using a single record subform, have you tried a datasheet or a continuous record subform?

If a call can only have one location then location id goes in the calls tables. So you would have 2 tables, one for calls and one for locations. If you want to enter/update calls by location then the main form is locations and the subform is calls. It would be a good idea to have a separate single-record calls form.

Why do you want to enter calls by location? Why do you need to search to see if a call has been entered? The number of calls for a location will increase over time. You will need to filter the calls if you do it by location. But explain why you need to do it this way.

--------------------
Robert Crouser
Go to the top of the page
 
brastedhouse
post Sep 7 2019, 09:49 AM
Post#6



Posts: 108
Joined: 16-March 15
From: Chautauqua, NY


Calls is a single record form.
Location is single record form. It is possible to change this to a datasheet, but as I said, the users are accustomed to a certain way of doing things and I want to create the easiest learning curve possible.

The data that is collected for a call is:
call #
date
location (what I am working on now)
type of call

So you get a call. We are told the location and type of call. We create the number. The date is the date we are called. We need to record this data. So I have a table that contains the following fields:

callID - autonumber
callNumber - number field we generate
callDate - date field
callLocation - number field for combobox to lookup in tblCallLocation table
callType - number for lookup in combobox to lookup in tblCallType table

I am trying to make it so the users will not have to do searches. I want to have it so they can type an address and it will either be in the list or they will need to add it. So I use a combo box and a not in list event. But I want to fill all the location table fields in the process of using the not in list event. The tblCallLocation structure is so I can create queries to search addresses easily i.e. every call on Main St. or all calls in Mayville or all calls at the Training Center (alias field).

tblCallLocation:
callStreetNumber
callUnitNumber
callCity
callAlias
callAddress
callNotes
Go to the top of the page
 
projecttoday
post Sep 7 2019, 11:46 AM
Post#7


UtterAccess VIP
Posts: 11,067
Joined: 10-February 04
From: South Charleston, WV


Okay.
I went back and looked at the original post. I think the word "subform" threw me off making me think that you have a one-to-many setup. To avoid duplicates in the locations you can create a unique index in the locations table. Sorry about the long-way-around to come to that simple answer. If you have a custom-search data entry popup instead of a combo box that's great. It is possible to make new entries from a combo box but I don't think you'll get the searching that you want.

Also, I would simplify the locations table a little. (It's about locations.)
tblLocations:
StreetNumber
UnitNumber
City
Alias
Address
Notes

--------------------
Robert Crouser
Go to the top of the page
 
brastedhouse
post Sep 7 2019, 05:00 PM
Post#8



Posts: 108
Joined: 16-March 15
From: Chautauqua, NY


Further refinement to on going idea in development.

1. In frmCall - field callLocationFK is a combo box (cboCallLocation) that gets it's data from tblCallLocation concatenating callAddress and callStreet (Address:Trim([callAddress] & " " & [callStreet]).)
2. Then using Not in List Event, if that combination is not in the table call the form frmSubCallLocation which has the fields for the tblCallLocation.
3. Fill in fields on frmSubCallLocation.
4. Click save and close button on frmSubCallLocation form. That should close the form and write the data ot the table.
5. When I encounter a not in list address I get the message box. I click it and I get the frmSubCallLocation form.

Here is the code section from the Not in List Event:
CODE
Dim intAnswer As Integer

    intAnswer = MsgBox("The Address" & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "CFD Operations")

    If intAnswer = vbYes Then
        DoCmd.OpenForm "frmSubCallLocationDE", , , , , acDialog
    Else
        MsgBox "Please choose another Address from the list.", vbInformation, "CFD Operations"
        Response = acDataErrContinue
    End If


Here is the code for the save and close button:
CODE
If Me.Dirty Then Me.Dirty = False
DoCmd.Close "frmSubCallLocation"
Forms!frmCall!cboCallLocation.SetFocus


Some of this works. Some does not.

I get the form to open when I enter an address that is not in list of addresses.
I fill in the fields and click the save and close button.
The new address data is written to the table, but I get an error message saying:
Type mismatch, but that does not make sense. I have checked the tables and all seems fine and as I said the data is written to the table correctly.

Here are the table structures:

tblCall:
callID - autonumber
callNumber - short text
callDate - date time
callLocationFK - Number (long integer)

tblCallLocation:
callAddressID - autonumber
callAddress - number
callStreet - short text
callStreet2 - short text
callCity - Number (long integer)
callAlias - short text
callLocationNote - long text

Can anyone tell me what code is missing or not working?

Best, Scott
Go to the top of the page
 
projecttoday
post Sep 7 2019, 05:48 PM
Post#9


UtterAccess VIP
Posts: 11,067
Joined: 10-February 04
From: South Charleston, WV


The city is a number?

--------------------
Robert Crouser
Go to the top of the page
 
brastedhouse
post Sep 7 2019, 09:12 PM
Post#10



Posts: 108
Joined: 16-March 15
From: Chautauqua, NY


yes. it gets it data from another table called tblCallMutualAidDept.
Go to the top of the page
 
projecttoday
post Sep 7 2019, 09:57 PM
Post#11


UtterAccess VIP
Posts: 11,067
Joined: 10-February 04
From: South Charleston, WV


You get that error every time you click on the save button on the form? Do you get the same error if you open the form manually? Can you move through the existing records without the error? What is the code behind the save button?

--------------------
Robert Crouser
Go to the top of the page
 
brastedhouse
post Sep 8 2019, 07:02 PM
Post#12



Posts: 108
Joined: 16-March 15
From: Chautauqua, NY


error every time? Yes
manually? Yes
move through records? Yes

Code behind button:
CODE
If Me.Dirty Then Me.Dirty = False
DoCmd.Close "frmSubCallLocation"
Forms!frmCall!cboCallLocation.Requery
Forms!frmCall!cboCallLocation.SetFocus


I have the form with the combo box (not in list event). Here is the code for that:
CODE
    Dim intAnswer As Integer

    intAnswer = MsgBox("The Address" & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "CFD Operations")

    If intAnswer = vbYes Then
        DoCmd.OpenForm "frmSubCallLocationDE", , , , , acDialog
    Else
        MsgBox "Please choose another Address from the list.", vbInformation, "CFD Operations"
        Response = acDataErrContinue
    End If


So I think I must be missing some code in the not in list event. I get a type mismatch error on closing the form. But he data is written to the table.

Thank, Scott
Go to the top of the page
 
projecttoday
post Sep 9 2019, 12:29 AM
Post#13


UtterAccess VIP
Posts: 11,067
Joined: 10-February 04
From: South Charleston, WV


My experience has been "data type mismatch" means just that - one of the columns has some wrong data in it. But in my experience the data don't get written. And the code I'm running is my own SQL code, as I recall, not a bound form.

And when you go back and display a record that previously produced an error the error is gone (to reiterate an earlier question)?

A (new) question is are you entering something in each one of the fields on the form? Have you tried it without entering anything? Or remove one or more of the fields entirely (temporarily, of course). Getting back to my experience, if you remove the offending field the error goes away.

Maybe somebody else has an idea. Or you could post it (minus confidential data).

--------------------
Robert Crouser
Go to the top of the page
 
brastedhouse
post Sep 10 2019, 09:02 PM
Post#14



Posts: 108
Joined: 16-March 15
From: Chautauqua, NY


So, I am ashamed to say that I JUST found the code archive. Yipee! I found a NotInLIst function by pere de chipstick that can accommodate the use of a form to enter the new data. I need to fill in possibly 6 fields of data. But I ran into a problem. One of the fields is filled by the use of another combo box that gets it's data from another table. so five can just be entered. But one needs to be chosen. That means that when I try to add a record with the NotInLIst function, it causes an error that another record is required in that table so the data addition fails. Ugh!

Does anyone have a suggestion on how to handle this.

Thanks, Scott
Go to the top of the page
 
projecttoday
post Sep 10 2019, 09:34 PM
Post#15


UtterAccess VIP
Posts: 11,067
Joined: 10-February 04
From: South Charleston, WV


I don't think a table update is dependent upon another records existence. Maybe you're just getting another error.

--------------------
Robert Crouser
Go to the top of the page
 
brastedhouse
post Sep 10 2019, 09:37 PM
Post#16



Posts: 108
Joined: 16-March 15
From: Chautauqua, NY


Ok, so I fixed that problem. But now I have another one. Jeesh. So my table of addresses has a field for the street number, the street name, a field for the apt. or unit number and the city. I concantenate the street number and street name in the combo box SQL so there is not a duplicate of the whole address. I thought about whether to just combine the fields. But it makes searching for an address difficult in some instances. And because sometimes the location address is i.e. Pratt and Ramble, for instance, I don't have a street number so I can't use Left to get just the street name. I sometimes need to run a report to say, count the number of calls on Main St., but then other times, 123 Main St. So I need to decide this before I get to the NotInList event issue.

All that means that the cool function code does not work because of the concatenated field.

Does anyone have any thoughts on this one?

Thanks, Scott
Go to the top of the page
 
brastedhouse
post Sep 10 2019, 09:38 PM
Post#17



Posts: 108
Joined: 16-March 15
From: Chautauqua, NY


Ugh, I am about sick of errors today.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th October 2019 - 02:37 AM