Retrieve data from different textbox if a form is loaded

[epardo87]

Hello all,



So I have three forms, let's say that both form1 and from2 have a button that open form 3. form1 is always open, but form2 doesn't.

So I need that:

• if you click the button in form1, a textbox in from 3 pulls the data from a combobox in from1
• if you click the button in form2, a textbox in from 3 pulls the data from a combobox in from2

So I have this code in Properties>Data>Default Value that functions perfectly but it just pulls data from from1

=[Forms]![Frm1]![cbo1] & ""

I have it in Default Value because in Control Source I have the link to the table it is going to populate

I have already tried many ways in both expression builder and VBA without success, If you could please help me build it, I'd really appreciate it.
The logic structure would be something like this, I know this is wrong but, we can start from here:

If [Froms]![Frm2] IsOpen Then
[Forms]![Frm2]![cbo2] & ""
Else
[Forms]![Frm1]![cbo1] & ""
End If

It currently is in expression builder, but I don't mind changing it to VBA

Thanks for your time!

[andy49]

Does it work?


Sent from my iPhone using Tapatalk

[epardo87]

Does it work?

Hello,well it works as the first code, only pulling data from cbo1 in frm1 and it does populate the table as I need. what I haven't been able to make work is that when frm2 is open, instead of pulling data from cbo1 of frm1, it pulls it from cbo2 of frm2...

[Micron]

Use the OpenArgs property of DoCmd.OpenForm method for each button. In the open event of the form, you could code it so that on its open event, you check the open args. If it's YourArgument1, get the value from this form, if it's YourArgument2, get the value from some other form.

But it would be simpler to just pass the control value as the argument, would it not?

[andy49]

http://stackoverflow.com/questions/3...s-in-ms-access

Example here


Sent from my iPhone using Tapatalk

[epardo87]

Thanks for your answers,

I dug up a little about the OpenArgs property but found it too complex, I think I'd have to spend a more than a couple hours experimenting to understand it quite well and since the opened form is actually really simple, I just made an identical copy and assigned one of to each, so each form pulls data from its corresponding...

[Micron]

What's confusing - the property or how you'd implement it? Good enough if you're happy with your solution but multiple copies of objects are a thing to avoid when possible.
For your benefit (?) I see it as something as simple as this:
frm1 button click
DoCmd.OpenForm "frm3",,,,,Me.cbo1

or frm2 button click
DoCmd.OpenForm "frm3",,,,,Me.cbo2

Then in the Load event (not Open event) for frm3
Me.textboxNameHere = Me.OpenArgs

[epardo87]

What's confusing - the property or how you'd implement it? Good enough if you're happy with your solution but multiple copies of objects are a thing to avoid when possible.
For your benefit (?) I see it as something as simple as this:
frm1 button click
DoCmd.OpenForm "frm3",,,,,Me.cbo1

or frm2 button click
DoCmd.OpenForm "frm3",,,,,Me.cbo2

Then in the Load event (not Open event) for frm3
Me.textboxNameHere = Me.OpenArgs

Thank you Micron!, it was confusing to me how to implement it, but with your example, now I get it perfectly, I was also going to ask you how to pass multiple values, but investigated first and managed to solve multiple values as well.

this is the final working code with multiple values being passed:

'Form1
Private Sub btnSchedule_Click()
Dim strWhere As String
strWhere = cmbStation & "|" & lstDesig
DoCmd.OpenForm "frmSchedule20", , , , , , strWhere
End Sub

'Form2
Private Sub btnSchedule_Click()
Dim strWhere As String
strWhere = lstStation & "|" & txtOperator
DoCmd.OpenForm "frmSchedule20", , , , , , strWhere
End Sub

'Frm3

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
Dim intPos As Integer
Dim strOper As String
Dim strStat As String
  If Len(Me.OpenArgs) > 0 Then
    intPos = InStr(Me.OpenArgs, "|")
    If intPos > 0 Then
      strOper = Left$(Me.OpenArgs, intPos - 1)
      strStat = Mid$(Me.OpenArgs, intPos + 1)


      Me.txtOperator = strOper
      Me.txtStation = strStat
    End If
  End If

Thanks for your help!

Regards!

[epardo87]

Hold on,

I've generated a new problem now,

I am receiving a message: "you cannot add or change record because a related record is required in table "...."

see, these textboxes in the third form are linked to a table (which is a relational table and uses values from other two tables). It was previously populating the table correctly, but now that I've use this code, something happened and the table is not recognizing the value entered in the textbox, it is not possible because it is selected at first from the same source, I believe the formula changed something in the property of the value and its causing it to be unrecognizable... I've already checked and all of them are text fields...

no I've read that this is because of the way relationships are configured but they are as I need them to be, I don't want to write a new record in the source table, I just need it to find it and use the one already exists, anyway, as I said, it was working fine before the new code was entered....

would you have any idea of what could have happened?

[Micron]

You're welcome. But I think I see a potential problem. If either of the variables are null or empty strings, you will always have a pipe character surrounded by spaces, thus you'll likely get an error with the Left or Mid functions. If you're sure that's not possible (e.g. your first form button click validates there are values to pass) then you should be ok. As it is, intPos will always be > 0. Now that I see the scope of the task, another way you might want to consider is calling a function from the form button click. If the function is in a standard module, either form 1 or 2 can call it, passing the value of controls to it while ensuring they have values.

IsNullEmpty (my user defined function) receives the actual control and tests for null & empty string, returning true or false. The calling form line is like If IsNullEmpty (Me.txtName) Then. In your case, then I'd call another function (again, standard module) twice, passing the form3 controls names to it since I know I did not get here with those controls on form 1 or 2 being null or empty strings. Thus it's only 2 lines in form 3, plus I'd have 2 new functions I can use anywhere. Alternatively, form3's function call could be in the form module, but you wouldn't be able to set the value of any control from any other place with it.

[Micron]

I see you were posting at the same time as me. Now that you've passed the hurdle you posted about, you're finding you can't do what you wanted (or you have changed something else too). The code does not change the data type or format. The message means you can't add a record in a child table when there is no parent record for it (at least that's what I gather since you didn't post all of the message). What I don't get is what you're saying worked before given the original problem. Perhaps you have done something that prevents the original record from form 1 or 2 to not be saved before going to form 3. I'm also beginning to wonder why you aren't just creating parent/child records with a form/subform design but I guess you have a good reason for that.

[epardo87]

I see you were posting at the same time as me. Now that you've passed the hurdle you posted about, you're finding you can't do what you wanted. The code does not change the data type or format. The message means you can't add a record in a child table when there is no parent record for it (at least that's what I gather since you didn't post all of the message). What I don't get is what you're saying worked before given the original problem. Perhaps you have done something that prevents the original record from form 1 or 2 to not be saved before going to form 3. I'm also beginning to wonder why you aren't just creating parent/child records with a form/subform design but I guess you have a good reason for that.

well, it worked fine when I had two forms instead of just form 3, one for each calling form. The thing is:

table 1: list of operators
table 2: list of stations
relational table 3: schedule to give training to an operator (table 1) in a specific station (table 2), so this one relates operator, station and adds a date

in form 1 you go through areas and you have the option to schedule a training for the selected station/operator

since one operator can have multiple certifications, you have the option as well to see the detail of the operator and his certifications in form 2, where you also have an option to schedule a training with the given selection in form 2, see images below.

now form three will open in a new record mode and pull the operator and the station selected in textbox 1 and 2 so that you can't change them and has a blank space to let you enter a date. this form is supposed to write a new row in the third table.

the full message is: "you cannot add or change record because a related record is required in table "tblStations" (which is the source of the stations column in the schedule table)

now the parent record does exist because in form 1, you first have to select the station desired which is also pulled from "tablStation"...

[epardo87]

Ok, I just detected something:

when I had two forms, since the control source is linked to the schedule table. I wrote the code in Default Value field, which was:

=[Forms]![frm20MainStatus]![cmbStation] & ""

for one of the textboxes called from the first form.

I'm suspecting that the problem might be that the code I wrote is interfering with the control source of the same textbox, how can I have this code function as a default value inside the textbox?

[epardo87]

I just experimented with the simple OpenArgs example that you posted earlier and it also works fine, but just passing one value and I do need both to be transferred... so it is definitely something with the multiple values code that I used

[Micron]

You cannot put code in the default value property. IIRC, a calculated field (a calculation or function in a textbox control source property) cannot be bound to a table or query either. In those cases, you make the control unbound and use code to execute update or append query statements to affect the table(s). Don't understand post #14. The open args suggestion was to pass 2 values, yes? But you make it sound like it was for one. Re: so it is definitely something with the multiple values code that I used at this point I have to say the problem is defined by my first sentence.

I'm hitting the road tomorrow morning, so after this post, my ability to help anyone on any thread is going to be quite limited for a few days.