Access 2000 - save displayed field as value

[Gram123]

Hello,

I'm trying to set up an Access db and am having a small problem.
I have a main form with a subform with "Invoice Number" as a linked field.
When an invoice number is typed into the main 'parent' form, it is automatically entered on the subform. It saves successfully (as a value) to the table behind the subform. Fine.

However, I also have another form (Log) which is opened when a command button is clicked on the main form. I want the same thing to happen with the invoice number as on the subform - i.e. I want the invoice number to appear automatically on this additional Log form, and to save to the Log table when I save the (Log) record.
I can do the first part - by simply referring to the control on the main form, I can display the invoice number on the Log form:
=[Forms]![MainForm]![InvoiceNumber]
, but this doesn't save the invoice number as a value in the Log table.
Can this be done relatively easily?

Obviously the subform works correctly because of the parent-child link, and the Log form doesn't have such a link. Can I create one? Or can I do something else to convert the displayed invoice number into a value that will save?

Cheers
Gram

 

[downwitchyobadself]

Hi Gram,

You're right all the way down the line--displaying the value with the control source you name cannot affect the underlying table.

I'd recommend a simple SetValue action in a macro that you call on the Log form's Before Insert event. This event (as Access help can describe for you in more detail) is called just before a new record gets added--the only time you should ever need to change an ID value. You set the Item of the SetValue action to the control on the log form, and the Expression of the SetValue should then be [Forms]![MainForm]![InvoiceNumber].

The Control Source of the text box on the log form should then be set to the field to which you wish to save the value.

 

[Gram123]

Hey Down.
Okay,
I've created a SetValue macro called PostalInvoice that has the Item:
[Forms]![Log]![InvoiceNumber]

and Expression:
[Forms]![MainForm]![InvoiceNumber]

In the BeforeInsert event of the Log form, I've selected "PostalInvoice". The Control source for the Invoice Number field on the Log form is set to InvoiceNumber (which, along with the other fields on the form, will be stored in the underlying table).

When I try it, I get nothing. The invoice number doesn't appear.
Have I missed something?

Gram

 

[Gram123]

Okay, I solved the problem by assigning the macro to the OnLoad event of the Log form instead of the BeforeInsert event.
However, I've thought of an additional problem.

What would happen if the invoice number of a record in the main form was changed or deleted? At present, the original record in the Log table remains. I can alter the invoice number on the main form and then open the Log form and add a new Log record. In such an event, the original Log record would be invalid.

I take it this is because of the relationship between the Log table and the Main table, and the fact we're using SetValue rather than a linked field. I tried enforcing referential integrity with cascaded updates and deletes, but this gives me an error message when I try to save the new Log record .

So, basically, I need to get the Log form record to update when the main form record is changed - is this possible?

Confused...

Gram

 

[downwitchyobadself]

True, using BeforeInsert will not actually show you the value in the form, though it will save it correctly to the table (you can try it if you don't believe me ).

As far as changing ID values, well, for one, they shouldn't change. This is the point of using a primary key that is strictly an ID value, with no other sense. Say, for example, I have a part-number table containing one record for each part number. The primary key, IMO, should not be the part number, because one day you might decide XYZ123 should become XYY123, and then you're faced with exactly the problem you're describing. An ID number for a record in Access is not the same thing as an ID number in the real world. But that's a separate issue.

There is no reason that enforcing referential integrity should not work. More importantly, you need to keep in mind that if you change an ID number, you need to change all the instances of it in that Log table (and any other table dependent on it) in order to maintain links between records. While there is some high-level debate over the value of cascade updating, I would strongly recommend that you use it in this relationship and every other one you've created, to avoid "orphaning" of records.

Now we just have to figure out what the error message is that you're getting, as there should not be one. Make sure that the record on MainForm is getting saved before you open the Log form, and if that doesn't solve the problem, then let us know what the error says.

 

[Gram123]

I didn't get back in time to edit my post before you replied....

I added a Save action (to save the Main form) to the OnClick event of the command button that opens the Log form. So, now I can change the Invoice Number on the main form and the change is reflected on the Log form.

I changed the relationship between the Main form and the Log form to One-to-One as their will only ever be one log record per invoice (allegedly).

And I've discovered what I actually need the Log form to do...
Once I've entered an Invoice Number in the Main form, and then clicked the command button that opens the Log form, I need it to check for a record in the Log table that has the same invoice number
- If it finds one, the Log record should be opened for editing.
- If it doesn't find one, it should create one, by inserting the invoice number into a new Log record. This is effectifely what it does now with the SetValue action.

I tried setting a condition on the SetValue macro to [Invoice Number] Is Null, but of course this doesn't work, because clicking the Log form button inserts a new record (so it would be null anyway.

The only error I'm getting now is a duplicate values error when I try and enter a log record after altering the invoice number (the Invoice Number must be unique).

BTW - the primary key in the main table is the Invoice Number, and the primary key in the log table is an autonumber called PostalLogID.

Gram

 

[downwitchyobadself]

Yes, well, the kind of thing you're trying to do is one of the reasons people eventually move away from macros. The macro can't do it, because you need to work with a recordset.

This KB article will show you the basics of opening to a specific record, though it works with input rather than another form value; I suspect you can work through it.

The other thing you then need is the DoCmd.GoToRecord method, which can be used to move to a new record if no matching record has been found.

 

[Gram123]

Okay, bear with me...
So, to make it check an existing entry in the main form instead of the answer supplied to an input box, I need to do something like this:

Function OpenLogForm()

Save.Form "MainParentForm"
If [Tables]![PostalLog]![InvoiceNumber] = [Forms]![MainParentForm]![InvoiceNumber] Then
DoCmd.OpenForm "PostalLogForm", acNormal, , "[InvoiceNumber] = ' " & [Forms]![MainParentForm]![InvoiceNumber] & " ' "
Else
DoCmd.OpenForm "PostalLogForm"

Then I need to add DoCmd.GoToRecord and tell it to go to a new record.

Then I also need to add a SetValue so the InvoiceNumber in the postal log form reflects the InvoiceNumber in the main (parent) form.

Erm.... any pointers?

Gram

 

[downwitchyobadself]

More like this. (This is untested, and assumes that the field InvoiceNumber is a text field)

Public Function OpenLogForm()

    Dim frmMain     As Form
    Dim strInvNum   As Long
    Dim bytLkup     As Byte
    
    Set frmMain = Forms!MainParentForm
    strInvNum = frmMain!InvoiceNumber
    
    bytLkup = DCount("InvoiceNumber", "PostalLog", "InvoiceNumber = '" & strInvNum & "'")
    
    If bytLkup > 0 Then
        DoCmd.OpenForm "PostalLogForm", , , "[InvoiceNumber] = '" & strInvNum & "'"
    Else
        DoCmd.OpenForm "PostalLogForm", acNormal
        DoCmd.GoToRecord acDataForm, "PostalLogForm", acNewRec
        Forms!PostalLogForm!InvoiceNumber = strInvNum
    End If
    
    Set frmMain = Nothing
    
End Function

 

[Gram123]

Okay, cool, getting there....

Right, I can run the code from within the module, with the MainParentForm open and it works.
However, when I refer to it in the OnClick event of the command button, it gives me an error msg.
I tried entering =OpenLogForm() in the OnClick event and it says:

The expression On Click you entered as the event property setting produced the following error: The expresion you entered has a function name that Microsoft Access can't find.
* The expression may not result in the name of a macro, user-defined function, or [Event Procedure]
* There may have been an error evaluating the function, event or macro.

I also tried creating a macro with a RunCode action, and referring to this instead, but received a similar error:

The expression you entered has a function name that MS Access can't find

Not sure why it doesn't recognise the module name from the OnClick event. The module name is OpenLogForm, and the Function name is OpenLogForm().

I also noticed a very minor additional problem - if you run the code for a new record, it also works, but if you then run the code again (or if it was working, click the button again), it gives the following error:
Run-time error '2105':
You can't go to the specified record.
I take it I can get round this by telling it to save the record after the GoToRecord...acNewRec line or somesuch.

Cheers Down - nearly there mate!

Gram

 

[downwitchyobadself]

Bizarre--something else is wrong if it can't find a public function in a public module. From the VBA window, do a debug > compile and see what you get.

You should be able to jump to the function from form design by clicking the ... next to OnClick if the name of the function is properly set.

As for the other proble,, yes, that code implies that you are otherwise saving the record and/or closing the Log form between calls from the Main form. You can get around that by adding a line to the effect of

If IsLoaded("Log") = true then
If Forms!Log.Dirty = true then Forms!Log.Dirty = false
End If

at the beginning of the OpenLogForm function.

 

[Gram123]

Okay, losing it now.
Just to recap on the code I now have:

Option Compare Database

Option Explicit
Public Function OpenLogForm()

If IsLoaded("PostalLogForm") = True Then
If Forms!PostalLogForm.Dirty = True Then Forms!PostalLogForm.Dirty = False
End If

Dim frmMainP
Dim strInvNum
Dim bytLkup

Set frmMainP = Forms!MainParentForm
strInvNum = frmMainP!InvoiceNumber
bytLkup = DCount("InvoiceNumber", "PostalLog", "InvoiceNumber = '" & strInvNum & "'")

If bytLkup > 0 Then
DoCmd.OpenForm "PostalLogForm", , , "[InvoiceNumber] = '" & strInvNum & "'"
Else
DoCmd.OpenForm "PostalLogForm", acNormal
DoCmd.GoToRecord acDataForm, "PostalLogForm", acNewRec
Forms!PostalLogForm!InvoiceNumber = strInvNum
End If

Set frmMainP = Nothing
End Function

When I added the If IsLoaded part, the whole thing stopped working. When I run the macro I get a msg saying Compile Error - Function not defined.
When I removed the new bit of code, the existing code no longer runs properly even from with in the module window - it brings up the PostalLogForm form but doesn't show the Invoice Number for existing records.

When I did teh Debug-Compile thing before adding the If IsLoaded part, nothing happened.

Confused...

Gram

 

[Gram123]

BTW, hitting the build (...) button next to the OnClick event on the command button previously just brought up the Choose Builder window, now it brings up the Expression builder, containing
=OpenLogForm().

 

[downwitchyobadself]

Once again, Gram, you have managed to invent a new and impossible error. The only possible explanation I can come up with is that you have somehow saved this in a module that is attached to a form. What is the full name of the module? Can you get to it from the "Modules" tab of the database window?

Oh, and sorry about IsLoaded(), I've been using it for so many years I forgot it wasn't a native Access function (actually there is one now, but that's a different story...) Here's the code for that, you can paste it into the same (public! not form!) module as the other code:

Public Function IsLoaded(ByVal pstrFrmNm As String) As Boolean
On Error Resume Next

    Dim frm As Form
    Set frm = Forms(pstrFrmNm)
    
    If Err <> 0 Then
        IsLoaded = False
    Else
        IsLoaded = (frm.CurrentView <> 0)
    End If
    
    Set frm = Nothing
    
End Function

Other than that, I'm out of ideas, which means it's time to send some stripped-down version of the database along.

 

[Gram123]

I can't see the module attached to the (any) form.
Yes I can get to it under the Modules tab.
The Module name is OpenLogForm.

As for the new bit of code you just posted, should I be replacing pstrFrmNm with the form name?
It's just that it doesn't work... when I run it, the macros window opens.

Gram