RI is still beating me...

[Rich_Lovina]

Okay fellow developers, maybe I'm slow, but here's where I'm falling over again with p'raps too much code.

My form has 6 related tables and is based on a query. Data comes into my main table by import and also by data entry.

The data-entry often means trying to add new codes which do not exist in the related table(s). Which way is best to solve this:

Option1: Do a macro to open related table, add new record, then return to form (this doesn't work until that form is closed and re-opened to accept new record.

Option2: Write some DLookup code which 'pastes the new record in the related table and also updates simultaneously the form where that new record is being added?

Any suggestions appreciated greatly. After my HD crashed Ive had to re-assemble many database bits & pieces, hence may have asked this last year.
Tks any ideas.

 

[Rich_Lovina]

Thanks greatly Pat. That sounds like the path I'd forgotten.

 

[Fornatian]

I use this function which works on one field tables used for lookups:

Public Function Add2Source(tbl As String, fld2update As String, NewData As String) As Integer
'Adds record to the list if not already present
'This only works if there is just one field to update....
Dim strMessage As String
Dim dbs As Database
Dim RstTypes As Recordset
Dim Response As Integer
strMessage = "'" & NewData & "' is not in current list" & _
" To add the item for future reference choose yes, or choose no to select from the present options."
Response = MsgBox(strMessage, vbYesNo, "Not in List")
If Response = vbYes Then
Set dbs = CurrentDb()
Set RstTypes = dbs.OpenRecordset(tbl)
RstTypes.AddNew
RstTypes.Fields(fld2update) = NewData
RstTypes.Update
Add2Source = acDataErrAdded
dbs.Close
Else
Add2Source = acDataErrDisplay
End If
strMessage = ""
Set dbs = Nothing
Set RstTypes = Nothing
End Function

and its called like this:

Response = Add2Source("TableName","FieldName",NewData)

This goes in the NotInList event.

HTH

Ian

 

[Rich_Lovina]

Dear Fornation
Thank you very much for yr input. Ive just installed the following code, and hv one or 2 problems....
Can you tell me, I hv a Form POS_INPUT which opens as a pop-up ready for data-entry (Data=yes). My code :
1. Doesnt open the form (where do I put Docmd.open form, etc.?
2. Tells me my RowSource is wrong somehow and Im confusing this with RecordSource.

As a real VB Dummy, does yr code hv an Open form and add the new record here?

My Form (query-based) is called INPUTFORM and my related table is POSCODES, my Form for adding new poscodes is POS_INPUT, and POSCODE is a PK in POSCODES.

Im getting close on this one, with a little more help...thanks

Based on MS AccessHelp, here's my code:-
(Combo119 is my combobox for INPUTFORM!POSCODE + relatedfld POSCODES!TITLE.
Private Sub Combo119_NotInList(NewData As String, Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Combo119
' Prompt user to verify they wish to add new value.
If MsgBox("This code is not in Poscodes. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End Sub

Turning all this into commonspeech, I hv 250 jobtitles (in POSCODES)and they keeeep growing.....so new ones are developed by bureaucrats every fortnight...
Thanks any help.

 

[Fornatian]

The code you have would be right if the combo box were based on a value list like:

pears;apples;bananas;

but your list is based on table/query therefore what you are trying to say is base it on everything in th table AND the new value I've just entered. This is not possible unless you add the new value to the underlying datasource.

So....

Ensuring that the table POSCODES(your lookup table) has only one required field which is the one you fill in from Combo19 then you should be able to get away with:

Response = Add2source("POSCODES","FieldToUpdate",NewData)

That should work using the code above.
Remember that a combo box can only reference EITHER a table/query or a value list not both at the same time.

Ian

 

[Rich_Lovina]

Dear Ian
Tks yr suggestions. Hve some simple questions.
When you refer to "above" code, do you mean my entered code or yrs 'above' that?
If mine, I changed the line:
Response = acDataErrAdded
to your suggestion:
Response = Add2Source("POSCODE228", "Poscode", NewData)
at which point when I testrun I get a compile error on "Add2Source" words.

I know I'm close but what has this dummy done? Tks any suggestion.

 

[Fornatian]

You should be able to replace ALL of the above code with:

Response = Add2Source("Table","FieldToUpdate",NewData)

Ian

 

[Fornatian]

ADD:
You need to copy the function from above and include it in a module of its own the database.

Ian

 

[FLOODSER]

You should set up a query with all the related tables. Each table should have some sort of relationship. One to many, One to One etc. A common index or a shared index.
The query should display fields from each of the table. When you enter in data in one field, the relationship will update the inexes in the related tables. Play around with it, I am sure it will work.

 

[Rich_Lovina]

Dear Ian
Thanks greatly. Its working. I put the function as a new module and called it by the response line code....great.

Checking the related table, the Poscode fld pasted in. Beautiful!

I designed a form to add info in a related field (Poscode228!Title), do I type in under response code :
Docmd.Opentable (TableName) or some such code?
Tks in advance
Note, the related table's field is also on my MainInputForm, and I entered DT (PosCode) and then entered DirectTest (Title) and all has worked perfectly. Checking the realted table all is great too...
Looks like I dont even need Form!Poscode228 at all.
Tks Ian, now I'll test 2nd related table in MainForm.

[This message has been edited by Rich_Lovina (edited 02-06-2002).]

 

[Fornatian]

Good luck, it is mighty difficult to picture your application and so easy to misinterpret intentions with text only comms.

Pls. come back if still stuck as we can tailer Add2Source()

Ian

 

[Fornatian]

FLOODSER, you CANNOT pull info through for a related record when the record has not yet been created, it's like reeling a line in without a fish on the end!

 

[Rich_Lovina]

Tks Ian, now have 5 combos working and only one small conflict emerging. Gotta run more tests as one was keeping old PK field with an After_Update and an OnEnter tag method for carrying forward records.

One combo i built where there's no related table (I just want it to lookup existing entries in the current dbase. Should I use a ListBox in this instance?

Also the text has text and numerics. What codeline covers both instances from yr sample?

Note also that when fields are carried forward my tab index sequenctial numbers are ignored and cursor goes to next available blank. How do I set tabstop to the original order so user can choose to 'tab-through' or change again?

 

[Fornatian]

LAST COMBO...
For the combo where you only want to select existing records, just set the limit to list property to Yes and then put a nasty message in the NotInList event like "You dumbass, you've done this a thousand times and still type the wrong thing!"

Sorry,didn't understand the last bit at all...long day at both ends sounds like!

Ian

 

[Rich_Lovina]

Tks Ian, yeh in my 2nd part I was commenting on another totally unrelated piece of code I found in the Forum the other day (author Raskew), relating to carrying forward flds to next record.

Now, my focussed question for this posting:

E.G. Add2Source MyTable, MyField, etc.

In my MainForm, MyField (Poscode) has related field (Title).

The Add2Source code lets me fill in the new Title on the Mainform, such that there's no need to use code to open up related table's input form. That part's great also!

Only tiny problem is vb(Uppercase) worrks on the Mainform but the pasting in the related table comes up lower case. Any simple codeline to make Uppercase apply to the new text in the related form?


[This message has been edited by Rich_Lovina (edited 02-07-2002).]

 

[Fornatian]

try changing:

RstTypes.AddNew
RstTypes.Fields(fld2update) = NewData
RstTypes.Update

to

RstTypes.AddNew
RstTypes.Fields(fld2update) = UCASE(NewData)
RstTypes.Update

in the function

to change the entry in the related table..I may be following a wrong avenue...

Ian

 

[Rich_Lovina]

Tks Fornation for the suggested change...it didn't work........
I'm still thinking of other ways...

[This message has been edited by Rich_Lovina (edited 02-10-2002).]

 

[Fornatian]

If it's a simple formatting job, just put > in the format to force all display to be Uppercase.

If it's that much of a pain run an update query to update the value to Ucase(theField)

Ian

 

[Rich_Lovina]

Actually I've found, as with some other commentators here, that > is less effective than strconv....,vbUppercase code.

Problem here is that I'm playing with field TITLE as per following example:

FormINPUTFORM:
MAINDBASE FIELDS
1-30 fields, 'many' being POSCODE.

POSCODE links to POSCODE 228, consisting of
Fld1: Poscode_ID
Fld2: Title
Fld 3-6, etc.

INPUTFORM based on query of 6 tables, includes TITLE, and when using
After_Update Combo_Poscode...
Me.[Combo121] = StrConv(Me.[Combo121], vbUpperCase)
If Not IsNull(Me.Combo121) Then
Me.Combo121.DefaultValue = "='" & Me.Combo121 & "'"
'Use
'For text fields 'Me.Operatorid.DefaultValue = "='" & Me.OperatorID & "'"
'For date fields 'Me.Operatorid.DefaultValue = "=#" & Me.Operatorid & "#"
'For number fields 'Me.Operatorid.DefaultValue = "=" & Me.Operatorid
Me.Combo121.TabStop = False
Else
Me.Combo121.TabStop = True
End If
End Sub
[Note: Excellent bit of code from a Raskew post !]

.....TOGETHER WITH

Private Sub Combo121_NotInList(NewData As String, Response As Integer)
Response = Add2Source("POSCODE228", "Poscode", NewData)
End Sub

This enables an instant entry of a new Poscode AND lets me enter the matching new TITLE field text, but without allowing strconv vbUppercase to work.

You may have understood all this but I outline it for other readers.

Problem is I can GlobalUpdate UCase in a query, but I have 34,000 records and probably 1,000 titles coming in a year, each title (in Govt) gives me additional info about a specific job function, and on average, every 3 years, the Govt changes all its titlenames again.

(e.g 10 yrs ago Computer Systems Officers (Grade 1 - 5) were popular; then the computer industry became more specific and now we have Comms Officers, IT Managers, Systems Analysts, Web Managers, Net Managers, etc, etc, etc, etc. AND the grading no. tells me their annual salary ranges).

In short global updating by query is not an option when Ive 32,ooo employees, adding 3-500 new ones weekly, related to over 800 Dept addresses, over 2500 sub-structure names (3 more combos!)and 1% duplicates arising weekly.

It'd just be nice to finesse this problem.

 

[Fornatian]

Just a thought, but had you tried:

Response = Add2Source("POSCODE228", "Poscode", UCASE(NewData))

Ian