INSERT INTO Code Problem

[xmeb]

Hi:

I am using the following code to save several text boxes to my data table and the ones listed to my locations table. It works okay except that it creates one blank record every time it saves a record in the locations table.

Private Sub DataEntryFormSubmitButton_Click()
myReply = MsgBox("Are you sure you want to save this record? You cannot edit data after you save it.", vbYesNo)
     If myReply = vbYes Then
         RunCommand acCmdSaveRecord
             
Set conn = CurrentProject.Connection

conn.Execute "INSERT INTO LocationsTable (PrimaryLocation) VALUES ('" & Me![Areas/Buildings/Places] & "');"

conn.Execute "INSERT INTO LocationsTable (PrimaryLocation) VALUES ('" & Me![Roads] & "');"
        
     Me.Requery
     End If
End Sub

Thanks,

xmeb

 

[dhookom]

Why the RunCommand acCmdSaveRecord? Is your form bound?

It looks like the code is inserting two records into the LocationsTable.

Duane
Hook'D on Access
MS Access MVP

 

[xmeb]

Yes, it is bound and unbound. The unbound combo boxes are that way so I can save them to the locations table. I guess that is not the right way to do it but I know of no other way. I have spent hours on Google trying to find a way to have my mostly bound form that saves to the data tables save a few combo boxes to the locations table.

The following works okay so far.

Private Sub DataEntryFormSubmitButton_Click()
myReply = MsgBox("Are you sure you want to save this record? You cannot edit data after you save it.", vbYesNo)
     If myReply = vbYes Then
         
         RunCommand acCmdSaveRecord
         
         Dim strValue As String
If Len("" & Me.[Areas/Buildings/Places]) > 0 Then
   strValue = Me.[Areas/Buildings/Places]
ElseIf Len("" & Me.Roads) > 0 Then
  strValue = Me.Roads
ElseIf Len("" & Me.[Condominiums/Lodging]) > 0 Then
  strValue = Me.[Condominiums/Lodging]
'and so on, and so forth
End If

Set conn = CurrentProject.Connection
conn.Execute "INSERT INTO LocationsTable (PrimaryLocation) VALUES ('" & strValue & "')"

     Me.Requery
     
     End If
     
End Sub

 

[xmeb]

Any thoughts? Thanks.

 

[dhookom]

I can't answer unless you tell me what your form is used for. I need to know about bound and unbound controls and record source and row sources of combo boxes.

Duane
Hook'D on Access
MS Access MVP

 

[xmeb]

It is just a simple form that users use to save records to my main table. All the fields were bound. Then I read an article about normalization and I got to thinking that since I have seven location fields and only two are used per record I might want to eliminate the seven location fields from the main table and create a new table with primary location and secondary location fields. So using the code posted here (I would have to finish it) I would have 13 fields bound and seven unbound on the form and the seven location fields would be saved to the new locations table with two fields. Doing this eliminates five fields on the main table that would always be blank per record. Is this a good idea or is there a better way to do it? Thanks.

 

[dhookom]

I'm not sure why you don't just post the significant table and field names. I can't understand why a table would have more than one location field.

Duane
Hook'D on Access
MS Access MVP

 

[xmeb]

The main table has 13 fields (officer, date, start time, end time, action, narrative, etc.) The seven location fields are where the action took place. The three primary location fields are either areas/buildings/places, roads, or condominiums/lodging. The four secondary location fields just further describe the primary locations. For instance, the primary location might be the name of a hotel and the secondary location is somewhere in that hotel, say the main lobby. Because this database is for a very large resort a secondary location is necessary to pinpoint exactly where the action happened. Thanks.

 

[xmeb]

Some help would be appreciated. Thanks.

 

[xmeb]

I appreciate the help I have received. Thanks! I guess my problem is beyond the experience of those that can help further. Regardless, thanks for all your help so far.

 

[dhookom]

The only reason I can justify the locations fields broken out to another table is if there are multiple locations per main record.

Duane
Hook'D on Access
MS Access MVP

 

[xmeb]

I do not know what you mean by "multiple locations per main record". I do know you are one of the best ones here but despite many years of doing this I still do not know very much. Thanks!

 

[xmeb]

The following are the current fields on the main table:

ID Officer DDate StartTime EndTime Action Areas/Buildings/Places MainBaseLodge XBaseLodge Businesses Room/Unit Number Condominiums/Lodging TheMountain Road Number Roads CPUBagsEnvelopes PTIState PTIRegistration VJSState VJSRegistration CDOBagsEnvelopes AlarmActivationType AlarmActivationCause AlarmActivationDisposition UnlockForOnAuthorizedDemand Verified Narrative SubjectIdentity DoorsChecked Directions/Escort/Parking Brook

Again, using a second "locations table" would do away with seven fields on the main table resulting in five fields on the main table that would always be blank.

 

[dhookom]

Apparently the main record in your application relates to an alarm or response. If that alarm/response is to one location then I don't see a need to break out fields to another table. If a single alarm/response record could have more than one location then I would remove all locations fields and place them in a separate child table linked by the ID field in the main table.

I would consider a table with all Points Of Interest (unique locations). These might be related to other larger locations like a Chair in a cube in an area on a floor in a building at an address on a street... If my location is this chair, that's all should really need to store.

Duane
Hook'D on Access
MS Access MVP

 

[xmeb]

So if all seven location fields were to go to a child table I still have my original problem which is how to save to two tables.

 

[dhookom]

I would ideally have one location field in a table. I would not store chair, cube, area, floor, building, address, and street. I would store only the point of interest ID for chair.

I don't think you have ever answered my question about if the record (whatever you are storing) has a one-to-one relationship with a single location.

Duane
Hook'D on Access
MS Access MVP

 

[xmeb]

I see what you mean, but the resort is so large we have multiple locations with the same name. For instance there are many places called "Main Lobby" and so on. In addition, the resort wants to see on their reports that the action occurred at "The Inn" in the "Main Lobby" or whatever.

I do not know what you mean by one-to-one relationship with a single location.

Thanks for your help.

 

[dhookom]

Each Main Lobby would have its own unique ID. If I were to create this, I would probably have a table of all unique locations with either a field ParentLocationID to use in a self-join or a couple other tables that would have the parent of the lobby. I have a feeling this is a bit over your capability to implement and mine capability to explain.

If I were you, I would probably use something similar to the web sites that allow you to select a vehicle company like "Honda" and then next choose the model like "CR-V" then the package like "LE" and then the year (or similar with maybe year first). This would require creating cascading combo boxes which has resources on the web about how to set up.

Duane
Hook'D on Access
MS Access MVP