Using Command Button to search table and create record

[mikepf]

I have successfully created a command button a form to do a search on a primary table for a key, entered data on the form and saved it into a second table. However the key for that second table does NOT get saved (but does show on the form). What happened?

 

[Remou]

Any chance of a little code?

 

[genomon]

And please tell us that you are not using AutoNumber fields...

Silence is golden.
Duct tape is silver.

 

[mikepf]

The key in the primary table IS autonumbered but it will be put into a field in the secondary table that is NOT.

 

[mikepf]

Actually, there is little/no code because in design mode, using the Command Button self prompts you to select the table and matching key. I am sensing that some VB will have to be added to make the key from the primary table to be put into the secondary table.

 

[Remou]

I do not quite understand how the data is transferred to the second table if there is no code. Are you running a query?

 

[mikepf]

Using Access Forms, in design mode for each text box (or whatever) you identify the NAME and CONTROL SOURCE in the Properties section.

 

[Remou]

Please post the code. If there is none, please explain more fully how the two forms/tables interact.

 

[mikepf]

In the first form as you put the Command Button on that form, it asks you what table you want to read and what key to match to using the data on that form. When you click tat button it takes you to form frmNumb_Cor using this code.

Private Sub Command58_Click()
On Error GoTo Err_Command58_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNumb_Cor"

stLinkCriteria = "[RECIEVED_ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command58_Click:
Exit Sub

Err_Command58_Click:
MsgBox Err.Description
Resume Exit_Command58_Click

End Sub

In the second form is used to both enter new data as well as retrieve data from the primary table. The properties icon at the top of the screen is used to show where it comes from/goes to. There is essentially no real VB coding here. The problem is there NO linkage to pass data from the primary table to the output table.

 

[Remou]

What is the name of the control you wish to hold the ID? When the second form opens, does it show any data?

 

[mikepf]

The data should be stored in field RECIEVED_ID in the output table. And YES the data does show in the second table in RECIEVED_ID but it does NOT show up in the field after SAVE. All other fields DO show up however.

 

[Remou]

I had a small hope that the data was being filled in by the user. However, that is not the case, what you are doing is opening a second table to an existing record, it baffles me that the link works when you say that Received_ID does not contain data relevant to Me!ID, because that is what your code says. You can simply update the field after you open the table, however, I cannot see that this will produce the results you wish for, so make sure that you take a copy before you test.

   stDocName = "frmNumb_Cor"
    
    stLinkCriteria = "[RECIEVED_ID]=" & Me![ID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

'From the code above, Received_ID must ALREADY = Me!ID
'but let us assume that it does not.
    Forms!frmNumb_Cor!Received_Id=Me!ID

 

[mikepf]

Well, it didn't make the database stop but it had no effect. Thanks for the try!!