Help with tables In a Form (1 Viewer)

C

Cubsm22p

Registered User.

Local time

Today, 01:05

Joined

Feb 14, 2018

Messages

37

• Mar 28, 2019

• #1

I would like to be able to click on these sub form records and have it open up a Editing_record form with the record that was clicked showing.

Editing_record form:

 

Last edited: Mar 28, 2019

theDBguy

I’m here to help

Staff member

Local time

Today, 01:05

Joined

Oct 29, 2018

Messages

21,467

• Mar 28, 2019

• #2

Hi. You should be able to use the OpenForm method and simply pass the ID of the clicked record in the WhereCondition argument as a filter criteria. For example:

Code:Copy to clipboard

DoCmd.OpenForm "FormName", , , "[ID]=" & Nz(Me.ID,0)

 

C

Cubsm22p

Registered User.

Local time

Today, 01:05

Joined

Feb 14, 2018

Messages

37

• Mar 28, 2019

• #3

like this:


update got this error with that:


Got it with this:
DoCmd.OpenForm "NewErrorfrm", acNormal, , "[ContentNum] = '" & Me![ContentNum] & "'"

Thanks for the help

 

theDBguy

I’m here to help

Staff member

Local time

Today, 01:05

Joined

Oct 29, 2018

Messages

21,467

• Mar 28, 2019

• #4

[ContentNum] must be a Text field. If so, try it this way:

Code:Copy to clipboard

DoCmd.OpenForm "NewErrorfrm", , , "[ContentNum]='" & Me.[ContentNum] & "'"

 

Last edited: Mar 28, 2019

C

Cubsm22p

Registered User.

Local time

Today, 01:05

Joined

Feb 14, 2018

Messages

37

• Mar 28, 2019

• #5

Now i would like to use this search button to put what ever i search on top of the list in the table.

 

theDBguy

I’m here to help

Staff member

Local time

Today, 01:05

Joined

Oct 29, 2018

Messages

21,467

• Mar 28, 2019

• #6

Hi. Not sure what you mean but assuming you want to search for a ContentNum, then you could try:

Code:Copy to clipboard

Me.Filter="[ContentNum]='" & Me.Searchbox & "'"
Me.FilterOn=False

 

C

Cubsm22p

Registered User.

Local time

Today, 01:05

Joined

Feb 14, 2018

Messages

37

• Mar 29, 2019

• #7

Would I put that in the 'after update event' for the search box,

 

theDBguy

I’m here to help

Staff member

Local time

Today, 01:05

Joined

Oct 29, 2018

Messages

21,467

• Mar 29, 2019

• #8

Cubsm22p said:

Would I put that in the 'after update event' for the search box,
View attachment 74447

Click to expand...

Yes. Try it out and let us know what happens.

 

C

Cubsm22p

Registered User.

Local time

Today, 01:05

Joined

Feb 14, 2018

Messages

37

• Mar 29, 2019

• #9

I put this in the 'after click update' on the search button and it worked:

Me.Trackertbl_subform1.Form.Filter = "[ContentNum]='" & Me.Text2 & "'"
Me.Trackertbl_subform1.Form.FilterOn = True

but how would i do it if i wanted it to make LIKE not just =
this didnt work:
Me.Trackertbl_subform1.Form.Filter = "[ContentNum]LIKE'" & Me.Text2 & "'"
Me.Trackertbl_subform1.Form.FilterOn = True

 

theDBguy

I’m here to help

Staff member

Local time

Today, 01:05

Joined

Oct 29, 2018

Messages

21,467

• Mar 29, 2019

• #10

Try adding a space between the field name and Like operator. Also, if you want to use Like, you probably want to use wildcards as well.

 

C

Cubsm22p

Registered User.

Local time

Today, 01:05

Joined

Feb 14, 2018

Messages

37

• Mar 29, 2019

• #11

syntax error when i do it like this:
Me.Trackertbl_subform1.Form.Filter = "[ContentNum]='" & Me.Text2* & "'"
Me.Trackertbl_subform1.Form.FilterOn = True

 

theDBguy

I’m here to help

Staff member

Local time

Today, 01:05

Joined

Oct 29, 2018

Messages

21,467

• Mar 29, 2019

• #12

Cubsm22p said:

syntax error when i do it like this:
Me.Trackertbl_subform1.Form.Filter = "[ContentNum]='" & Me.Text2* & "'"
Me.Trackertbl_subform1.Form.FilterOn = True

Click to expand...

Try it this way:

Code:Copy to clipboard

Me.Trackertbl_subform1.Form.Filter = "[ContentNum] Like '" & Me.Text2 & "*'"

 

C

Cubsm22p

Registered User.

Local time

Today, 01:05

Joined

Feb 14, 2018

Messages

37

• Mar 29, 2019

• #13

That worked!, Lets say I want the search box to filter other other attributes along with the contentNum

 

theDBguy

I’m here to help

Staff member

Local time

Today, 01:05

Joined

Oct 29, 2018

Messages

21,467

• Mar 29, 2019

• #14

If you want all conditions to match, you'll use AND. If you only care as long as any of them matches, then you'll use OR. For example:

Code:Copy to clipboard

"Field1 Like '" & txtSearch & "*' [b]AND[/b] Field2 Like '" & txtSearch & "*'"

 

C

Cubsm22p

Registered User.

Local time

Today, 01:05

Joined

Feb 14, 2018

Messages

37

• Mar 29, 2019

• #15

Thank You for all of your help

 

Attachments

• 
  cantgetit.PNG

  8.8 KB · Views: 65

Last edited: Mar 29, 2019

theDBguy

I’m here to help

Staff member

Local time

Today, 01:05

Joined

Oct 29, 2018

Messages

21,467

• Mar 29, 2019

• #16

Hi. Format is different from data type. Go back to the table's design and make sure the field's data type is any of the following: Double, Single, or Currency

 

isladogs

MVP / VIP

Local time

Today, 09:05

Joined

Jan 14, 2017

Messages

18,212

• Mar 29, 2019

• #17

This should explain why:

Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number.
For example, the number 123.45 has a precision of 5 and a scale of 2.

Click to expand...

You have set scale=0 so you get whole numbers only
You would probably be much better off using Single or Double number datatype depending on the size of your numbers