Linking foreign key when button pushed

[Jlc614]

Help I am new and struggling. I have a form with subforms and with all your help and reading here I got them linked to auto fill the client id’s from one form/ table to the other.

Now I am trying to insert a button which will pull up another form, which works fine but I cannot get it to fill in that client id the same way.

My main form is called client details based on a table of the same name, the primary key and id is client Idmstr. The pop up form would be Bank Details again based on a table of the same name, the id field is client id.

I do have the tables linked with a relationship of 1 to many. How can I get it to auto fill that client id into that form. This will be used for data entry if that matters

 

[CJ_London]

presumably you are using docmd.openform to open your other form?

if so, then one of the parameters enables you to put in a 'where' clause - something like

docmd.OpenForm "myform",,,"ClientID=" & me.idmstr


the odd name for your main form PK implies it is a string in which case it would be

docmd.OpenForm "myform",,,"ClientID='" & me.clientID & "'"

 

[Jlc614]

Sorry but I am REALLY new. I just built the form in access I don’t do anything to open it and haven’t written any code. The main form is just “client details” why is that an odd name? I am sorry to be so needy and I need to find some good books for writing code because I am very lost

 

[theDBguy]

Hi. Welcome to the forum. If you can share a copy of your db, we could try to show you how to write the code and where to place it.

 

[Jlc614]

I really wish I could but I work for a county office and am not allowed.

 

[theDBguy]

I really wish I could but I work for a county office and am not allowed.

Hi. One way to avoid getting in trouble is to remove all "real" data and just enter a couple of "dummy" data. Can you do that?

 

[CJ_London]

The main form is just “client details” why is that an odd name?

I was referring to what you called the primary key

the primary key and id is client Idmstr

I just built the form in access I don’t do anything to open it and haven’t written any code

you said this

Now I am trying to insert a button which will pull up another form, which works fine but I cannot get it to fill in that client id the same way.

which implies you have code which opens the form

You may have used a wizard to create the button and the action it performs when clicked, it which case you need to edit the macro

 

[Jlc614]

I just went to the toolbox in the ribbon and selected the button and then yes followed the steps to open a form

 

[CJ_London]

go into form design select the button and click properties on the ribbon if not already showing.

On the properties sheet click the events tab. You should see something next to the click event. Click on the three dot button on the far right on the click event. This will either open the vba editor or the macro builder depending on how you have set things up.

Take a screenshot of what you see and upload it here (use the advance editor to do that)

 

[Jlc614]

I have attached the screen shot I hope you can see it

Thank you for the help

 

[CJ_London]

there is no code there so whatever you did with the wizard didn't work. Suggest you either create a new button and follow the steps again - it will give you options for what you want the button to do.

Alternatively put the code I suggested into the bank_details_click sub i.e.

sub bank_details_click()
 
docmd.OpenForm "myform",,,"ClientID='" & me.clientID & "'"
 
end sub

change myform to the name of the form you want to enter (BankDetails?) ClientID to the name of the field in the form (the id field is client id?) and me.clientID to the name of the control in your main form (primary key and id is client Idmstr).

Note if your field/table/control names have spaces you need to use square brackets (e.g. [client id]). Using spaces is a bad practice and should be avoided. For example you may have named your button 'bank details' - but you will see in the code access has replaced the space with an underscore.

 

[Jlc614]

Did I not enter something right because it is not entering the number. I actually changed the table, forms and Id fields to eliminate spaces.

The form that the button opens is called BankDetails, the field on that form is ClientID. The id field in the main form which I want it to pull from is ClientIDmstr.

 

[CJ_London]

Clearly not - but no idea what you entered or should enter - I don't use the wizards or macros.

Have you tried creating a new button? - and follow the prompts closely.

I'm about to go away for a couple of weeks so will have to drop out now, various things I need to finish off before going.

If you can't upload a blank db, all I can suggest is you try one of the templates and see how it is done there.

 

[Jlc614]

I did a new button the prompts don’t give you any options for bringing over the id numbers or things like that from the main form. Just pulling up existing data. The button opens my form okay just can’t get the id to pull

 

[theDBguy]

I did a new button the prompts don’t give you any options for bringing over the id numbers or things like that from the main form. Just pulling up existing data. The button opens my form okay just can’t get the id to pull

So now, when you click on the three dots, what do you get?