adding new data without typing in pk 3

[mstekkie]

i've got this one form with the firm number as the pk. when you click on the view employees button, it shows the employees associated with that company. problem is, when i want to add a new employee, i can't do that without inputting the firm number again.

i want the employee linked form to take the pk from the previous form. i don't want the user to have to input it again. if the pk isn't put in, then the employee isn't saved.

any ideas on how to accomplish this?

thank you.

cate

 

[RickBerem]

Hi,

I suggest you set one of your textboxes CONTROL SOURCE to
=[Forms]![PreviousForm].[TextBoxWithTheFirmNumber]

like this the Firm number will always be a the top of a new employee.

Hope it's of some help. Salvatore Grassagliata

 

[oharab]

If you set up your relationships so to enable cascade updates, then change your form so you have the employees as a subform of the firm form, then the key will automatically update as you add new records.

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[FoxProProgrammer]

If the primary key is in a control on the previous form, you can refer to it with [Forms]![formname]![controlname].Value I believe that the Value property is optional, but it makes it clearer what you are doing if anyone reads your code. If the primary key isn't in a contol, you can put it in a hidden control and refer to the hidden control.

dz

 

[mstekkie]

i've tried what you suggested, and the firm number is always showing at the top of the new employee, but when i try to add in a new one, it doesn't save that employee.

what am i doing wrong?

 

[mstekkie]

i've tried all these suggestions, but whenever i try to save, it won't let me. it keeps giving me the error:

'can't add/change a record because a related record is required'

 

[FoxProProgrammer]

What method are you using to save the record? If you don't want to use a subform like Ben suggested, you will need to assign the value of the key field before saving the record. If you are using a record set named recset, and the primary key is named pk, assign the value with:

recset!pk = [Forms]![formname]![controlname].Value

dz

 

[mstekkie]

my employer doesn't want me to use a subform, which is why i'm using linked forms.

sorry, but i don't know what you mean by record set.

 

[FoxProProgrammer]

One way to save a record is by creating a record set similar to below. Note that "db" and "recset" are user defined names. You can make them anything that you want.

Dim db as Database
Dim recset as Recordset

Set db = CurrentDb
Set recset = db.OpenRecordset(&quot;tablename&quot;, <option>)

recset.AddNew
recset!<field1> = <value in control on form>
recset!<field2> = <value in control on form>
recset!<field3> = <value in control on form>
recset!pk = [Forms]![formname]![controlname].Value
....more fields the same way
recset.Update
recset.Close

The <> are not part of the syntax. I included them to show where you enter the names for your application.

You can also save a record with:

DoCmd.RunCommand acCmdSaveRecord

This runs the Save Record command on the Access menu bar, but it will only work if your fields are bound to the table. If your fields are unbound, you will have to use the first method.

Please post the code that you are using to save the record so we can see where the problem might be.

Thanks,

dz

 

[mstekkie]

i'm not using any code to save the record, i just used the buttons to create a 'save record' button. that's the only way i know how to do it.

where exactly would i put in that code?

 

[RickBerem]

Hi,

In that case you should Put the code [Forms]!...
in the DEFAULT VALUE property and link the CONTROL SOURCE property to the table like you do for all your other fields

Hope it helps! Salvatore Grassagliata

 

[FoxProProgrammer]

Ok, you must have used the wizard to create the button. The code will be in the button's OnClick event procedure. To view it, open the form in design view. Click on the button. Open the control's properties if they aren't already open. Click on the Event tab. Click on ... next to On Click. A Visual Basic window should open, and you should be able to see the code that the wizard created. If you used the wizard to create the button, it probably used DoCmd.RunCommand acCmdSaveRecord to save the record. The reason that you aren't saving your primary key is because there isn't a bound field on that form that contains data for the primary key. All you have to do is add the following line of code before the DoCmd.RunCommand acCmdSaveRecord command.


Me.pk = [Forms]![formname]![controlname].Value

insert the name of your field in place of pk if that isn't what it is called in the table. I made some assumptions in here as to what you and the wizard did, so please post back if it's wrong and I'll try to help further.

dz

 

[mstekkie]

THANK YOU SO MUCH!

it finally worked! thank you so much for your help

cate

 

[mstekkie]

sorry to bug you again, but i've got one more question. i used the exact same method to save a record for another form. it's the exact same thing, only different names for the controls/buttons. but whenever i try to save the record, this error pops up saying:

can't assign a value to this object

and i don't know what that means or why it's happening because i didn't do anything differently.

 

[FoxProProgrammer]

You would get that error if you were trying to assign a value to a control that doesn't exist on the form. For example, if you have a statement like:

Me.pk = [Forms]![formname]![controlname].Value

you would get that error if pk doesn't exist.

If that isn't the problem, you can set a breakpoint in the debugger so you can see exactly which statement it's barfing on. Also, when you get the error, if you click on the debug option, it will take you to the statement that it doesn't like. If you can't figure out what the problem is, post the problem statement and I'll see if I can figure out what's wrong.

dz

 

[mstekkie]

the firmNumber isn't the pk of my second form (the linked form), but it's the pk of the first form.

but with that first problem you helped me with, it was the same thing. the pk of the first form was firmNumber, and the pk of the second form wasn't firmNumber.

the code i'm using is:

Me.firmNumber = [Forms]![frmCurrentClients]![firmNumber].Value

thanks again for all your help.

 

[FoxProProgrammer]

Does the current form have a control named firmNumber? If the control exists, when you type &quot;Me.f&quot; in the VB editor, you should see firmnumber in the list. If it isn't there, then either you don't have a hidden textbox on the current form named firmNumber or you misspelled it. If that isn't the problem, the Me.firmnumber = [Forms]!... statement isn't causing the problem. Is that statement highlighted when you run the debugger? How do you know that statement is causing the error? Make sure that statement is causing the error, and if it isn't, post the statement that it doesn't like.

 

[mstekkie]

There is a control named firmNumber, so that's not the problem.

I don't know how to run the debugger to check what the problem is.

 

[FoxProProgrammer]

There are two ways to invoke the debugger. When some errors occur, the Debug button will be enabled in the error dialog box. If you click it, the VB editor will open and the problem statement will be highlighted. The more proactive way to debug a program is to open the VB editor and left click the mouse in the bar to the left of a VB statement. A bullet will appear indicating that it has set a breakpoint on that statement. When your program reaches that statement, the VB editor will open and program execution will suspend. You can then step through your program one line at a time by pressing the F8 key, or selecting Step Into from the Debug menu within the VB editor. The debugger is a very powerful tool, and it is absolute necessity to learn how to use it when debugging code. Without it, you are blind to what is happening. In this case, I have a feeling it is barfing on a different line than you think it's barfing on. After you get the debugger running, post back and I'll see if I can help further.

dz

 

[mstekkie]

I put a breakpoint on the Me.firmNumber...and the next line that was highlighted (before the error msg came up) was:

MsgBox Err.Description

The error msg (can't assign value...) showed up, then the next line that was highlighted was:

Resume Exit_btnSaveEmp_Click

wasn't this code generated by the button wizard?