[Macro] Store new value as new record in separate table, then link the ID

[jderica]

I’m not sure how to search for such a feature and find ideeas. I’ve been browsing for something similar for the past week now.

I want to streamline a form used to store location addresses for events. As for the database structure, the “Events” table stores a foreign key from an “Adress” table. Currently, the “Adress” table is designed with columns for “streettype”, “streetname”, “streetnumber”, “state”, “city” and “areacode”.
I can setup a form in which you can just type those in and be done with, but I wonder if it’s not better to break the “Adress” table down and create a table for each column, thus storing a bunch of foreign keys in the “Address” table.

How can I design a form for this feature? I’d like to maintain one Text Field for each address component and use a macro for keypressed to search for previously inputed and stored data (similar to the one typed in for past records) and trigger a second macro when comitting the user input, to store the ID of an exisitng entry or store that data as a new entry and then grab the new ID to store.

So in essence:

• User types some text into the Street Name textfield → macro searches for matches LIKE the one inputed and autofills
• User commits the inputed data (enter or textfield looses focus) → if matching name exists in the “streetname” table, save that entry ID as a foreign key in the current record of the “Address” table
• User commits the inputed data (enter or textfield looses focus) → if NO matching name exists in the “streetname” table, create new record there and then save that new ID as a foreign key in the current record of the “Address” table.

Any pointers regarding this? I assume something similar must exist somewhere.

I think I just found something here:

Add entries to listbox or combobox

I have two tables that are related with a field on one table and the primary key on the other in a one-to-many relationship. (One table for names, other table with a field for the name) I have set up a listbox on a form with the data from the names table. Right now, I can pull a name off the list and place the ListID number in the name field, which is fine. What I’d like to do is to be able to start to type the name in the listbox field and if the name is there, save the Primary Key number in t…

I’ll post a model for the database if I can get this to work.

Edit: I got to this point. It works how I want it to, but if you select any record and press Enter while an existing name is selected, the macro creates a new record in the address table, instead of updating the previous one.

REM  *****  BASIC  *****

Option Explicit

Sub KeyHandler_KeyPressed(oEvent)
    If oEvent.KeyCode = com.sun.star.awt.Key.RETURN Then
    	CheckAndSetName(oEvent)
    End If
End Sub

Sub CheckAndSetName(oEvent)
	Dim sSelectedItem		As String
	Dim sSQL				As String
	Dim sSQL2			As String
	Dim oForm				As Object
	Dim oStatement			As Object
	Dim vResult				AS Variant
	Dim vCursorTest			AS Variant
REM Get text from combo box
	sSelectedItem = oEvent.Source.Text
REM Get the forms
	oForm = oEvent.Source.Model.Parent 'Get Form
REM Create for SQL
	oStatement = oForm.ActiveConnection.createStatement() 'Create an SQL statement object
	oStatement.ResultSetType = 1005
	sSQL = "SELECT ""nameID"", ""name"" FROM ""name"" WHERE ""name"" = '" & sSelectedItem & "'"
	sSQL2 =  "INSERT INTO ""address"" (""nameFK"") VALUES (SELECT""nameID"" FROM ""name"" WHERE ""name"" = '" & sSelectedItem & "')"
	oStatement.executeUpdate(sSQL2)
	vResult = oStatement.executeQuery(sSQL)
    vCursorTest = vResult.first
REM Check if selection already exists
    If vCursorTest = "False" Then
REM Name does not exist - add to table
	   	sSQL = "INSERT INTO ""name"" (""name"") VALUES ('" & sSelectedItem & "')"
	   	sSQL2 =  "INSERT INTO ""address"" (""nameFK"") VALUES (SELECT""nameID"" FROM ""name"" WHERE ""name"" = '" & sSelectedItem & "')"
		oStatement.executeUpdate(sSQL)
		oStatement.executeUpdate(sSQL2)
    End If
	oForm.getByName("fmtnameID").refresh()
End Sub

I tried this too, for the first sSQL2 and it returns a java error.

sSQL2 =  "UPDATE ""address"" SET ""nameFK"" = ""nameID"" FROM ""name"" WHERE ""name"" = '" & sSelectedItem & "')"

address book.odb

[Ratslinger]

Hello,

Your code has a few problems. You check if a record exists after you have done the Insert. In that case it will always exist.

You do need to have an Insert for new and Update for existing. You Update statement is incorrect.

For the Update you need the address ID in a where statement otherwise you will update all existing records.

You already retrieved the nameID but in your SQL you retrieve it again.

Here is your Base file back with these corrected items. Had to slightly modify you for as it had duplicate control names. Renamed leftmost ID control to ‘fmtID2’. Needed to use name to access record to update.

Returned sample ----- jderica.odb

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[jderica]

This works a lot better, thank you Ratslinger! I gave up after 9 hours hammering my head at this today.
At first it didn’t seem to work properly, but then the name field worked perfectly! I think I’m too tired.

I’ll give it another go tomorrow with your sample. I added the same feature to the city and type columns and I noticed some wierd results, particularly when using the “new record” button on the form and typing in new values for type, name and city. I’ve got three new address entries, each containing only the foreign key for the new data inserted.

I’ll look into it in the morning and try to find some steps to reproduce or resolve it.
Thank you again, for this issue and past ones!

[jderica]

But what I don’t understand is why does this seem so complicated to do? At first I decided to just use combo boxes with SELECT DISTINCT name from name and just fill in the adresses with duplicate cities and what not into a single address table.

But then I couldn’t shake the thought that I should use normalization on it and break it down.
I could do list boxes everywhere with SELECT name, nameid from name (with bound field 1). But it seems too complicated have either a separate form for adding new entries for the list box or an extra text field on the current form.

[Ratslinger]

@jderica,

I would not take the approach you have. Would simply use a list box. If the item is not there, you need to add it into the proper table like any other table entry. Then refresh the list box for the new entry to appear.

Your normalization is overkill in my opinion. Would not break down into those minute items.

There is a time and place to automate processes but sometimes we over do it. This may be one of those cases.

[jderica]

@Ratslinger,

Yea I think you are right… this is overkill. I’ll use combo boxes and just store the adresses into a single table, as I like the autofill feature more and I plan to keep it interface simple.
It doesn’t seem that bad now. Thank you. I feel relieved after this 9 hour ordeal, lol.

[Ratslinger]

@jderica,

I stated a list box not a combo box. Big difference.